Executing Linux command from Oracle PLSQL using Java Stored Procedure

In this blog I am going to use a JAVA stored procedure to execute Linux commands from PLSQL. In my earlier post we I have used a C program to achieve the same solution. But there are various methods to utilize it.

To execute any OS command from Oracle PLSQL we have to create a JAVA store procedure. Execute the below SQL from your schema from which you want to fire the operating system command.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "BASH_OS" AS
   /******************************************************************************
           NAME:       BASH_OS
           PURPOSE:    To perform the shell command using Java class 
                               
           REVISIONS:
           Ver        Date        Author    Description
           ---------  ----------  ------   ---------------------------------------------
           0.1        <<>>        S.Ray      Initial Version
                   
        ******************************************************************************/
        import java.io.*;
public class Host {
  public static void executeCommand(String command) {
try {
      String[] finalCommand;
       {
        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());
    }
  }
  
};

Once the above step is completed then we have to publish the Java call specification by executing the below SQL file:

CREATE OR REPLACE PROCEDURE unix_command (p_command  IN  VARCHAR2)
AS LANGUAGE JAVA 
NAME 'BASH_OS.executeCommand (java.lang.String)';

After that login with SYS DBA user and grant the privileges to the Schema by executing the below SQL file :

 

---Grant Privileges
--CONN / AS SYSDBA

DECLARE
  l_schema VARCHAR2(30) := 'Your Schema Name'; -- Pass the correct Schema name
BEGIN
  DBMS_JAVA.grant_permission(l_schema, 'java.io.FilePermission', '<<ALL FILES>>', '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;
/

Now Finally test it by running :

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as pgedev

SQL> 
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> CALL DBMS_JAVA.SET_OUTPUT(1000000);

Method called

SQL> BEGIN
  2          unix_command('/bin/ls -lrt /home/oracle/app/EXP_DIR');
  3  END;
  4  /

Process out :total 8
Process out :drwxrwxrwx 2 oracle oinstall 4096 Aug 12 12:21 attachments
Process out :-rw-r--r-- 1 oracle oinstall    5 Aug 12 18:21 test.txt

PL/SQL procedure successfully completed

Thank you for reading my blog , the reference is taken from OTN . Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Advertisements

9 thoughts on “Executing Linux command from Oracle PLSQL using Java Stored Procedure

  1. Great example! Thanks!
    I attempt to convert this Java class to return a message back to calling function so I can intercept it withing PLSQL code but facing multiple errors. I wonder if you could provide an example of this class to return 0 if all is good but anything else if there is an error or an issue.

  2. This is very useful. Thanks. Just a clarification. Does this support a unix job in background. I tried calling a shell script using this with & at the end (to start it in background). The calling pl/sql kicked off the unix process but it look like it is waiting for it to finish and not returning control back to the calling pl/sql.

  3. Thanks for this article and this is very useful. Does this work with background jobs in unix? I am trying to execute a shell script from the pl/sql using this and one of the command inside the shell script is starting a job in background. (& at the end). I can see this background job initiated. However instead of moving on to the next command in the script, it seem to be waiting for the background job to complete. Appreciate your help.

  4. When I tried above scripts it gives following error, is stored procedure need to be loaded using loadjava?
    BEGIN
    unix_command(‘/bin/ls -lrt /home/oracle’);
    END;
    / 2 3 4
    BEGIN
    *
    ERROR at line 1:
    ORA-29540: class BASH_OS does not exist
    ORA-06512: at “APPS.UNIX_COMMAND”, line 1
    ORA-06512: at line 2

  5. Thanks for the reply, i found the issue due to wrong class reference in the procedure unix_command, modified code as below replaced bash_os with host worked very well:

    CREATE OR REPLACE PROCEDURE unix_command (p_command IN VARCHAR2)
    AS LANGUAGE JAVA
    NAME ‘Host.executeCommand (java.lang.String)’;

    Thanks

  6. Hi Team,
    Whenever i am trying call this process .i am getting below error. could please some one help me on this .
    BEGIN
    unix_command(‘/bin/sh /u100/app/MKTUAT/ormktuat/oracle/SEG_TOOL/generate_preview_list.sh 1101957 RP_PREVIEW_EMAIL_VW XXXXX@oracle.com CXD’);
    END;
    /
    Error :-

    Process err :/u100/app/MKTUAT/ormktuat/oracle/SEG_TOOL/generate_preview_list.sh: line 5: java: No such file or directory

    PL/SQL procedure successfully completed.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s