Host Command From Oracle PL/SQL Using Java
During data loads, ETL and other data management processes, OS commands have to be executed to move/rename files etc. The below shows use of Java and PL/SQL to call OS commands. This is based on code discussed on the Oracle's Ask Tom site. The code shown below is to execute the Unix/Linux command "ps -ef".

Grants Setup 
DECLARE
  v_usr  VARCHAR2(30) := UPPER('&i_user');
BEGIN
   dbms_java.grant_permission
     (v_user,
      'java.io.FilePermission',
      '/usr/bin/ps',    -- replace ps with * for all commands
      'execute');
    
   dbms_java.grant_permission
     (v_user,
      'java.lang.RuntimePermission',
      '*',
      'writeFileDescriptor' );
END;
/

The Java Class (Source) 
CREATE OR REPLACE and compile java source named "Util"
as
import java.io.*;
import java.lang.*;

public class Util extends Object  {

  public static int RunThis(String[] args)  {

    Runtime rt = Runtime.getRuntime();
    int     rc = -1;
  
    try  {
      Process p = rt.exec(args[0]);

      int bufSize = 4096;
      BufferedInputStream bis =
        new BufferedInputStream(p.getInputStream(), bufSize);
      int len;
      byte buffer[] = new byte[bufSize];

      // Display the program output
      while ((len = bis.read(buffer, 0, bufSize)) != -1)
          System.out.write(buffer, 0, len);
          rc = p.waitFor();
    }

    catch (Exception e)   {
       e.printStackTrace();
       rc = -1;
    }
    finally {
        return rc;
    }
  }
}
/

PL/SQL Function and Procedure To Call Java Class 
CREATE OR REPLACE function exec_cmd(p_cmd  in varchar2)
 return number
as
language java 
name 'Util.RunThis(java.lang.String) return integer';
/

CREATE OR REPLACE procedure p_exec_cmd(p_cmd in varchar2) 
as
 x number;
begin
 x := exec_cmd(p_cmd);
end p_exec_cmd;
/

Execution of the (Unix/linux) OS command from PL/SQL 
set serveroutput on size 1000000
exec dbms_java.set_output(1000000)
exec p_exec_cmd('/usr/bin/ps -ef');

Oracle registered trademark of Oracle Corporation.
Last Revised On: December 08, 2013

  73904