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
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
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
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?
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
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
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?
Hello! Congratulations. Your solution has been very helpful because utl_file.fcopy couldn’t copy pdf files correctly (in 9i)
Thank you!
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
Can you make the sources available as zip-file or perhaps even a JDev project, please?