Calling/Executing OS Commands from PL/SQL

You may never know when the need arrives to make an Operating System Command call from a PL/SQL Procedure...

Recently came across such requirement and found the below code and thought of updating my blog.

I used this procedure along with the Extract methods as mentioned in earlier post to extract about 300 million records from about 200+ tables by compressing and ftping to another server. All in 2:30 hours with one session.

Making an OS call from PL/SQL is of two folds:
1. Create JAVA Source Object
2. Create a wrapper procedure to call the JAVA Class created above.

JAVA Procedure Code:
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
  public static void executeCommand(String command) {
    try {
      String[] finalCommand;
      if (isWindows()) {
        finalCommand = new String[4];
        // Use the appropriate path for your windows version.
        //finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";    // Windows NT/2000
        finalCommand[0] = "C:\\windows\\system32\\cmd.exe";    // Windows XP/2003
        //finalCommand[0] = "C:\\windows\\syswow64\\cmd.exe";  // Windows 64-bit
        finalCommand[1] = "/y";
        finalCommand[2] = "/c";
        finalCommand[3] = command;
      }
      else {
        finalCommand = new String[3];
        finalCommand[0] = "/bin/sh";
        finalCommand[1] = "-c";
        finalCommand[2] = command;
      }

      final Process pr = Runtime.getRuntime().exec(finalCommand);
      pr.waitFor();

      new Thread(new Runnable(){
        public void run() {
          BufferedReader br_in = null;
          try {
            br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
            String buff = null;
            while ((buff = br_in.readLine()) != null) {
              System.out.println("Process out :" + buff);
              try {Thread.sleep(100); } catch(Exception e) {}
            }
            br_in.close();
          }
          catch (IOException ioe) {
            System.out.println("Exception caught printing process output.");
            ioe.printStackTrace();
          }
          finally {
            try {
              br_in.close();
            } catch (Exception ex) {}
          }
        }
      }).start();

      new Thread(new Runnable(){
        public void run() {
          BufferedReader br_err = null;
          try {
            br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
            String buff = null;
            while ((buff = br_err.readLine()) != null) {
              System.out.println("Process err :" + buff);
              try {Thread.sleep(100); } catch(Exception e) {}
            }
            br_err.close();
          }
          catch (IOException ioe) {
            System.out.println("Exception caught printing process error.");
            ioe.printStackTrace();
          }
          finally {
            try {
              br_err.close();
            } catch (Exception ex) {}
          }
        }
      }).start();
    }
    catch (Exception ex) {
      System.out.println(ex.getLocalizedMessage());
    }
  }

  public static boolean isWindows() {
    if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
      return true;
    else
      return false;
  }

};
/

Wrapper to call Java Source:
CREATE OR REPLACE procedure host_command (p_command  in  varchar2)
as language java
name 'Host.executeCommand (java.lang.String)';
/

Grants:

Now, grant DBMS_JAVA permissions to a specific user who should be allowed to make OS calls. Please be very very careful in giving this grant.

CONN / AS SYSDBA

DECLARE

  l_schema VARCHAR2(30) := 'USER_TO_MAKE_OS_CALL'; -- Adjust as required.

BEGIN

  DBMS_JAVA.grant_permission(l_schema, 'java.io.FilePermission', '<>', 'read ,write, execute, delete');

  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');

  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');

END;

/

Example:

Now, use this wrapper proc in your pl/sql to make any OS command.

t_os_call := '/bin/gzip --force '||t_os_path||'/*.dat'; --using -force option to overwrite  

host_command(t_os_call);

The above example shows that t_os_call variable is set to execute gzip with options built dynamically and host_command procedure uses this variable to execute this command in the OS.

Note:

Use dbms_java.set_output along with dbms_output.enable to get the actual command output from the OS call.

eg., sample showing dbms_java.set_output and storing the results

declare

t_output dbms_output.chararr;

begin

  dbms_output.disable; --flushing the pool

  dbms_output.enable(1000000);

  dbms_java.set_output(1000000);

  

  t_os_call := '/bin/gzip --force '||t_os_path||'/*.dat'; --using -force option to overwrite

  

  host_command(t_os_call);

  

  dbms_output.get_lines(t_output, t_lines);

  

  for i in 1.. t_lines loop

    dbms_output.put_line(t_output(i));

    p_message := p_message||t_output(i);

  end loop;

end;