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

9

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

Share.

About Author

9 Comments

  1. We are trying to do similar as explained by you. We want to execute “java -jar “C:\\Documents and Settings\\skulkar\\Desktop\\TestExec.jar\” ” through Runtime.getRuntime().exec(); but always ending error – java doesn’t exist … do i need to provide some permissions for PL/SQL to understand the java command ? Please advice

  2. Hi

    Is there any way to call the AcroRd .exe from this program .since the program uses the Process class i thought is workable .but the pc hangs
    Please advice

    Thanks

  3. This was a great tip! I followed all the steps and was able to invoke a shell script from Stored java on the database. However, the script that gets invoked runs some commands such as touch files and `date`. The script runs and then stops at the first place where it gets to such a command. It does not give me any error messages. I am positive that the script is actually running. I have changed permissions on everything to allow access, so I am sure that is not it either. Any ideas?

  4. GURWINDER SINGH on

    i have done above steps ,all are working fine but on last step
    i received error SQL> exec rc(‘C:WINDOWSsystem32cmd.EXE /c dir C:SCOTT’)
    BEGIN rc(‘C:WINDOWSsystem32cmd.EXE /c dir C:SCOTT’); END;

    *
    ERROR at line 1:
    ORA-06501: PL/SQL: program error
    ORA-06512: at “SCOTT.RC”, line 10
    ORA-06512: at line 1
    steps

    please help.I will be verythankful to you for this

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

    2.)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;
    /

    3.)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;
    /
    4.)
    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;
    }
    }
    /
    5.)BEGIN
    dbms_java.grant_permission( ‘scott’, SYS:java.io.FilePermission’, ‘C:WINDOWSSYSTEM32CMD.EXE’, ‘execute’ );
    dbms_java.grant_permission( ‘scott’, ‘SYS:java.lang.RuntimePermission’, ‘*’, ‘writeFileDescriptor’ );
    END;
    /

    6.)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;
    /
    7.)SQL> set serveroutput on size 1000000
    SQL> exec dbms_java.set_output(1000000)

    PL/SQL procedure successfully completed.

    8.)
    SQL> exec rc(‘C:WINDOWSsystem32cmd.EXE /c dir C:SCOTT’)
    BEGIN rc(‘C:WINDOWSsystem32cmd.EXE /c dir C:SCOTT’); END;

    *
    ERROR at line 1:
    ORA-06501: PL/SQL: program error
    ORA-06512: at “SCOTT.RC”, line 10
    ORA-06512: at line 1

  5. I tried running the above procedure to execute the cmd but it just hangs the session
    and eventually I have to kill it.

    I want to use the above procedure to run the oracle reports (rwcli60.exe) to print
    reports from a procedure.

    Please advice me if you had encounted similar problem and if so is there a solution.

    Thanks
    Karnail

  6. Can i use zip and unzip commands in this ?
    my project requirements, want me to unzip a file from folder and then process it.
    Can i use runcmd for that?

  7. Hello! Congratulations. Your solution has been very helpful because utl_file.fcopy couldn’t copy pdf files correctly (in 9i)
    Thank you!

  8. Hi Gregory,

    UTL_FILE is only capable of reading on and writing to the file system of the database server. How about your functions? I see that you perform I/O on your local C: drive. Can they I/O from any client PC or do you happen to run a local database?
    The 10g version of UTL_FILE has a copy (utl_file.fcopy) procedure that can do a whole or partial copy of a file, but it is restricted only to the server file system.

    *********************************************************************************************************

    Hello Harm,

    Thanks for your comment.
    These (my) functionalities are also restricted to the database server filesystem.
    I know about the Utl_File.Fcopy (since Oracle 9.2.0), but I needed it in 8.1.7.

    Gregory