Shell Commands From PL/SQL

Using a Java stored procedure it is possible to perform shell commands from PL/SQL.

◉ Create the Java Stored Procedure

First we need to create the Java class to perform the shell command.

CONN test/test

CREATE OR REPLACE AND COMPILE 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;

  }

};

/

show errors java source "Host"

◉ Publish the Java call specification

Next we publish the call specification using a PL/SQL "wrapper" PL/SQL procedure.

CREATE OR REPLACE PROCEDURE host_command (p_command  IN  VARCHAR2)

AS LANGUAGE JAVA 

NAME 'Host.executeCommand (java.lang.String)';

/

◉ Grant Privileges

In this example we are granting access to all directories on the server. That is really dangerous. You need to be more specific about these grants and/or be very careful about who you grant access to this functionality.

The relevant permissions must be granted from SYS for JServer to access the file system. In this case we grant access to all files accessible to the Oracle software owner, but in reality that is a very dangerous thing to do.

CONN / AS SYSDBA

DECLARE

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

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;

/

The affects of the grant will not be noticed until the grantee reconnects. In addition to this, the owner of the Oracle software must have permission to access the file system being referenced.

◉ Test It.

Finally we call the PL/SQL procedure with our command text.

CONN test/test

SET SERVEROUTPUT ON SIZE 1000000

CALL DBMS_JAVA.SET_OUTPUT(1000000);

BEGIN

  host_command (p_command => 'move C:\test1.txt C:\test2.txt');

  --host_command (p_command => '/bin/mv /home/oracle/test1.txt /home/oracle/test2.txt');

END;

/

The output from the host command can be captured using the DBMS_OUTPUT.get_lines procedure.

CONN test/test

SET SERVEROUTPUT ON SIZE 1000000

CALL DBMS_JAVA.SET_OUTPUT(1000000);

DECLARE

  l_output DBMS_OUTPUT.chararr;

  l_lines  INTEGER := 1000;

BEGIN

  DBMS_OUTPUT.enable(1000000);

  DBMS_JAVA.set_output(1000000);

  host_command('dir C:\');

  --host_command('/bin/ls /home/oracle');

  DBMS_OUTPUT.get_lines(l_output, l_lines);

  FOR i IN 1 .. l_lines LOOP

    -- Do something with the line.

    -- Data in the collection - l_output(i)

    DBMS_OUTPUT.put_line(l_output(i));

  END LOOP;

END;

/

◉ Known Issues.

- Depending on the environment, the process may continue running as a zombie after the command has been executed, even if the destroy() method is called manually. If this happens the process is only cleaned up when the session ends. Under normal circumstances this doesn't represent a problem, but when called as part of a job the zombie processes will only die when the Job Queue Coordinator is stopped.

- No profile is run for the OS callout, so no environment variables will be set. As a result you will need to use full paths to any executables ("ls" becomes "/bin/ls") or scripts. Alternatively, write all operations as scripts and set the relevant environment variables inside the scripts.

Source: oracle-base.com