Labite’s blog

I was looking for a way to execute os commands from Oracle, all solutions I found were very complex, had to create a C program or had to use the package dbms_scheduler to execute a job, besides to be complex these solutions, have to develop diferents codes for diferents operational systems… so I solved this problem using java without to use the package dbms_scheduler , the Java class executes the command in runtime, this solution works in Oracle 8,9,10 and 11 with all operation systems supported by Oracle. Basicly you need to follow 3 steps:

1. Grant some java privileges to your Oracle database user.
2. Compile java class on Oracle database.
3. Create a Oracle function with the call to java class.

STEP 1 – GRANTS TO YOUR DATABASE USER

Logged as SYS execute the commands bellow:

SQL> EXEC dbms_java.grant_permission('<USERNAME>',
                                     'SYS:java.lang.RuntimePermission',
                                     'writeFileDescriptor', '');

SQL> EXEC dbms_java.grant_permission('<USERNAME>',
                                     'SYS:java.lang.RuntimePermission',
                                     'readFileDescriptor', '');

SQL> EXEC dbms_java.grant_permission('<USERNAME>',
                                     'SYS:java.io.FilePermission',
                                     '/bin/sh', 'execute');

Example:

SQL> EXEC dbms_java.grant_permission('labite',
                                     'SYS:java.lang.RuntimePermission',
                                     'writeFileDescriptor', '');

SQL> EXEC dbms_java.grant_permission('labite',
                                     'SYS:java.lang.RuntimePermission',
                                     'readFileDescriptor', '');

SQL> EXEC dbms_java.grant_permission('labite',
                                     'SYS:java.io.FilePermission',
                                     '/bin/sh', 'execute');

STEP 2 – CREATE AND COMPILE JAVA CLASS

Logged with your database user execute the command bellow:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "OSCommand" AS
  /*
    Created by: Leandro Abite
    email: leandroabite@yahoo.com.br
  */
  import java.io.*;

  public class OSCommand {

    public static String executeCommand(String command) {
      StringBuffer sb = new StringBuffer();
      try {
        String[] finalCommand;
        if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1) {
          finalCommand = new String[4];
          finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";
          finalCommand[1] = "/y";
          finalCommand[2] = "/c";
          finalCommand[3] = command;
        } else { // Linux or Unix System
          finalCommand = new String[3];
          finalCommand[0] = "/bin/sh";
          finalCommand[1] = "-c";
          finalCommand[2] = command;
        }
        // Execute the command...
        final Process pr = Runtime.getRuntime().exec(finalCommand);
        // Capture output from STDOUT
        BufferedReader br_in = null;
        try {
          br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
          String buff = null;
          while ((buff = br_in.readLine()) != null) {
            sb.append(buff);
            sb.append("\n");
            try {Thread.sleep(100); } catch(Exception e) {}
          }
          br_in.close();
        } catch (IOException ioe) {
          System.out.println("Error printing process output.");
          ioe.printStackTrace();
        } finally {
          try {
            br_in.close();
          } catch (Exception ex) {}
        }
        // Capture output from STDERR
        BufferedReader br_err = null;
        try {
          br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
          String buff = null;
          while ((buff = br_err.readLine()) != null) {
            sb.append("stderr:");
            sb.append(buff);
            sb.append("\n");
            try {Thread.sleep(100); } catch(Exception e) {}
          }
          br_err.close();
        } catch (IOException ioe) {
          System.out.println("Error printing execution errors.");
          ioe.printStackTrace();
        } finally {
          try {
            br_err.close();
          } catch (Exception ex) {}
        }
      }
      catch (Exception ex) {
        System.out.println(ex.getLocalizedMessage());
      }
      return sb.toString();
    }
  };
/

STEP 3 – CREATE FUNCTION TO CALL JAVA

CREATE OR REPLACE FUNCTION oscomm (p_command   IN   VARCHAR2) RETURN VARCHAR2
  AS LANGUAGE JAVA NAME
    'OSCommand.executeCommand (java.lang.String) return java.lang.String';
/

EXECUTION TEST

To execute the os command you have to pass the complete path of the command:

it is wrong!!!!!
 SQL> select oscomm('ls -ltr') from dual;
 OSCOMM('LS-LTR')
 --------------------------------------------------------------------------------
 stderr:/bin/sh: ls: No such file or directory

it is right!!!!!
 SQL> select oscomm('/bin/ls -ltr') from dual;
 OSCOMM('/BIN/LS-LTR')
 --------------------------------------------------------------------------------
 total 44
 -rw-r--r-- 1 oracle oinstall  8385 Sep 11  1998 init.ora
 -rw-r--r-- 1 oracle oinstall 12920 May  3  2001 initdw.ora
 -rw-r----- 1 oracle oinstall    24 Nov 19 17:31 lkDB01
 -rw-rw---- 1 oracle oinstall  1544 Nov 19 17:31 hc_db01.dat
 -rw-r----- 1 oracle oinstall  1536 Feb 10 17:08 orapwdb01
 -rw-r----- 1 oracle oinstall  2560 Feb 18 12:42 spfiledb01.ora