Stored Java to run an OS command, copy a file and get a directory listing in Oracle

Oracle 8.1.5 introduced the ability to choose Java as a language to implement a stored procedure.

One of our customers required the following functionalities from within an Oracle database:

• Running an operating system command
• The ability to copy a file
• Listing all of the files in a given directory

These things are missing in UTL_FILE.
PL/SQL cannot do this – Java can quite easily.
Here is how (I have tested this on an Oracle 8.1.7 and 10.1.0.2.0):

I created a collection type and a package for these functionalities:

create or replace type STRARRAY as table of varchar2(255)
/

This type is used for holding a Java object (of oracle.sql.ARRAY[]).

CREATE OR REPLACE PACKAGE UTIL AS
 FUNCTION RUN_CMD(p_cmd IN VARCHAR2) RETURN NUMBER;
 PROCEDURE COPY_FILE(p_srcFile IN VARCHAR2, p_destDir IN VARCHAR2);
 PROCEDURE GET_DIR_LIST(p_dir IN VARCHAR2, p_dirList OUT STRARRAY);
END UTIL;
/

CREATE OR REPLACE PACKAGE BODY UTIL AS
 FUNCTION RUN_CMD(p_cmd IN VARCHAR2) RETURN NUMBER
  AS LANGUAGE JAVA NAME 'nl.amis.util.RunCmd.runThis(java.lang.String) return int';
 PROCEDURE COPY_FILE(p_srcFile IN VARCHAR2, p_destDir IN VARCHAR2)
  AS LANGUAGE JAVA NAME 'nl.amis.util.FileCopy.copy(java.lang.String, java.lang.String)';
 PROCEDURE GET_DIR_LIST(p_dir IN VARCHAR2, p_dirList OUT STRARRAY)
  AS LANGUAGE JAVA NAME 'nl.amis.util.DirList.getList(java.lang.String, oracle.sql.ARRAY[])';
END UTIL;
/

As you can see, Java methods (see the implementation below) are wrapped into stored procedures or functions.

We can just use SQL*PLUS to load Java code straight into the database, have it compiled into byte code and stored for us.
There is no need for an external compiler, no JDK installs – just a SQL CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED… statement:

Running an operating system command:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "RunCmd" AS
package nl.amis.util;

import java.io.*;

public class RunCmd
{
  private static void pass( java.lang.String p_in,
                         java.lang.String[] p_out )
  {
    if ( p_in != null ){
        p_out[ 0 ] = p_in.toUpperCase();
    }
  }

  public static int runThis(String cmd)
  {
    Runtime rt = Runtime.getRuntime();
    int        rc = -1;

    String[] args = new String[ 4096 ];
    pass(cmd, args);

    try
    {
       Process p = rt.exec(args[ 0 ]);

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

       // Echo back what the program spit out
       while ((len = bis.read(buffer, 0, bufSize)) != -1)
          System.out.write(buffer, 0, len);

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

We have to grant as SYS the appropriate Java permissions to the schema storing this java source (here GREGORY), since the database will take care of the security:

BEGIN
  dbms_java.grant_permission( 'GREGORY', SYS:java.io.FilePermission', 'C:WINDOWSSYSTEM32CMD.EXE', 'execute' );
  dbms_java.grant_permission( 'GREGORY', 'SYS:java.lang.RuntimePermission', '*', 'writeFileDescriptor' );
END;
/

The first privilege allows us to run a very specific program. If we put * in place of the program name, we can run anything.
The second allows us run-time to produce output.
Here we must use a *, as we do not know what output might be created (stdout for example).

Next, a procedure to call UTIL.RUN_CMD:

CREATE OR REPLACE PROCEDURE RC(
   P_CMD IN VARCHAR2)
AS
   X                             NUMBER;
BEGIN
   X := UTIL.RUN_CMD (P_CMD);

   IF (X <> 0)
   THEN
      RAISE PROGRAM_ERROR;
   END IF;
END RC;
/

Now, we will see how this works:

SQL> set serveroutput on size 1000000
SQL> exec dbms_java.set_output(1000000)

PL/SQL procedure successfully completed.

SQL> exec rc('C:WINDOWSsystem32cmd.EXE /c dir C:Gregory')
Volume in drive C has no label.
Volume Serial Number is 5C82-7765
Directory of C:GREGORY
02/07/2005  04:19 PM    <dir>          .
02/07/2005  04:19 PM    <dir>          ..
11/20/2003  11:51 AM               411 call_process_trillium
11/13/2003  09:49 AM           485,758 Docs.zip
12/09/2003  02:07 PM             1,380 GG-data-sources.xml
12/09/2003  05:12 PM        12,599,247 GG-oc4j-app.log
12/09/2003  02:07 PM               422 GG-oc4j-app.xml
11/04/2003  02:00 PM               634 GG.jws
11/10/2003  05:08 PM            13,473 process_trillium.sql
11/07/2003  05:08 PM            93,512 rdc.zip
11/04/2003  02:06 PM            35,851 rdplog.zip
11/14/2003  03:01 PM         3,369,686 RDP_DEV.zip
11/17/2003  10:22 AM             1,126 test.html
11/17/2003  10:21 AM             1,242 test.html.bak
11/04/2003  01:59 PM            11,008 TestAU.java
11/13/2003  09:49 AM                48 vssver.scc
14 File(s)     16,613,798 bytes
2 Dir(s)   8,372,465,664 bytes free

PL/SQL procedure successfully completed.

Copying a file:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "FileCopy" AS
package nl.amis.util;

import java.io.*;

public class FileCopy{

  public static void copy(String srcFile, String destDir) {
    File source = new File(srcFile);
    File dest = new File(destDir);
    File cpDestFile = null;
    try {
      if (dest.isDirectory()) {
          if(source.isFile()) {
            System.out.println("File name = " + source.getName());
            System.out.println("File name dest = " + dest.getPath() );
            System.out.println("File name cpDestFile = " + dest.getPath() + File.separator + source.getName());
            cpDestFile = new File(dest.getPath() + File.separator + source.getName());
          }
          else {
            System.out.println("Directory or File Not Found");
            return;
          }
      }
      BufferedInputStream  br = new BufferedInputStream (new FileInputStream (source));
      BufferedOutputStream  bw = new BufferedOutputStream (new FileOutputStream (cpDestFile));
      int read = 0;
      while((read = br.read()) != -1) {
        //System.out.println((char)read);
        bw.write(read);
      }
      br.close();
      bw.close();
    }
    catch (FileNotFoundException e) {
    e.printStackTrace();
    }
    catch (IOException e) {
    e.printStackTrace();
    }
  }
}
/

We also have to grant as SYS the appropriate Java permissions to GREGORY when copying ‘C:\temp\xmltool javadoc.zip’
to ‘C:\Gregory’

BEGIN
  dbms_java.grant_permission( 'GREGORY', 'SYS:java.io.FilePermission', 'C:Gregory', 'read' );
  dbms_java.grant_permission( 'GREGORY', 'SYS:java.io.FilePermission', 'C:tempxmltool javadoc.zip', 'read' );
  dbms_java.grant_permission( 'GREGORY', 'SYS:java.io.FilePermission', 'C:Gregoryxmltool javadoc.zip', 'write' );
END;
/

Ready to test it:

SQL> set serveroutput on size 1000000
SQL> exec dbms_java.set_output(1000000)

PL/SQL procedure successfully completed.

SQL> exec UTIL.COPY_FILE('C:tempxmltool javadoc.zip', 'C:Gregory')
File name = xmltool javadoc.zip
File name dest = C:Gregory
File name cpDestFile = C:Gregoryxmltool javadoc.zip

PL/SQL procedure successfully completed.

Getting a directory listing:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "DirList" AS
package nl.amis.util;

import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class DirList {

  public static void getList(String directory, oracle.sql.ARRAY[] listArray) throws java.sql.SQLException,IOException{
    File path = new File(directory);
    String[] list = path.list();

    Connection conn = new OracleDriver().defaultConnection();
    ArrayDescriptor descr =
       ArrayDescriptor.createDescriptor( "STRARRAY", conn ); //"STRARRAY" is SQL type name (table of varchar2(255))
    listArray[ 0 ] = new ARRAY( descr, conn, list );
  }
}
/

Here also the grant (as SYS) on a specific directory we want a listing of:

BEGIN
  dbms_java.grant_permission( 'GREGORY', 'SYS:java.io.FilePermission', 'C:temp', 'read' );
END;
/

A test program:

SQL> set serveroutput on size 1000000
SQL> exec dbms_java.set_output(1000000)

PL/SQL procedure successfully completed.

SQL> DECLARE
  2  vArray STRARRAY := STRARRAY ();
  3  BEGIN
  4    UTIL.GET_DIR_LIST('C:temp', vArray);
  5    DBMS_OUTPUT.PUT_LINE(CHR(09));
  6    DBMS_OUTPUT.PUT_LINE('Directory listing of C:temp:');
  7    DBMS_OUTPUT.PUT_LINE(CHR(09));
  8    FOR i IN 1..vArray.COUNT LOOP
  9      DBMS_OUTPUT.PUT_LINE(vArray(i));
 10    END LOOP;
 11  END;
 12  /

Directory listing of C:temp:

274326.pdf
275195.pdf
DEPT.csv
edvpln502f.fmb
EMP.csv
emp_dept.dmp
f90jdapi.zip
f90upgrade.zip
fma
forms9icstowebmigration.pdf
forms_upgrade_reference.pdf
jdapi javadoc.zip
jdapi.properties
jdapiTools.zip
jdev9052.zip
log
sqlnet.log
test.log
xmltool javadoc.zip

PL/SQL procedure successfully completed.

Resource:
Chapter 19 of ‘Expert One-on-One Oracle’ by Thomas Kyte

Download:
sources