Execute OS Commands from PL/SQL

Calling Unix commands from PL/SQL isn't a new idea, but this technique allows the user or program to execute any given OS command, view the printed output, and capture a success/fail argument.

Directions:

1. Compile the java program.
2. Load it to the DB using LOADJAVA.
3. Create a function call spec to call the program.
4. Turn serveroutput and java output ON.

Function Call Spec:
-----------------------------------------
CREATE OR REPLACE FUNCTION shellcmd (v_cmd IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'oscmd.mainrun(java.lang.String[]) return int';
/


Java Source:
-----------------------------------------
import java.io.*;
import java.util.*;

      public class oscmd{
          static public int runCommand(String cmd)
              throws IOException {

              int vSubResult;
              vSubResult = 0;

              // set up list to capture command output lines
              ArrayList list = new ArrayList();

              // start command running
              Process proc = Runtime.getRuntime().exec(cmd);

              // get command's output stream and
              // put a buffered reader input stream on it
              InputStream istr = proc.getInputStream();
              BufferedReader br =
                  new BufferedReader(new InputStreamReader(istr));

              // read output lines from command
              String str;
              while ((str = br.readLine()) != null)
                  list.add(str);

              // wait for command to terminate
              try {
                  proc.waitFor();
              }
              catch (InterruptedException e) {
                  System.err.println("process was interrupted");
                  vSubResult = 1;
              }

              // check its exit value
              if (proc.exitValue() != 0) {
                  System.err.println("exit value was non-zero");
                  vSubResult = 1;
              }

              // close stream
              br.close();

              // read all result strings into variable outlist
              String outlist1[] = (String[])list.toArray(new String[0]);

              // display the output
              for (int i = 0; i < outlist1.length; i++)
                  System.out.println(outlist1[i]);

              return vSubResult;
          }

          public static int mainrun(String args[]) throws IOException {
              try {
                  int vResult;

                  // run the given command
                  vResult = runCommand(args[0]);

                  // return 0 on success, 1 on failure
                  return vResult; 
              }
              catch (IOException e) {
                  System.err.println(e);
                  return 1; 
              }
          }
      }

A similar tip can be found at Calling Java from PL/SQL by Steven Feuerstein (Oracle Magazine, March 2000).