My manager wanted to be able to call his SAS program from an Oracle procedure to process some data. To allow Oracle to execute a Windows command I had to create a type, function and java source on the server. I found this solution on the great Experts Exchange site which was posted by sdstuber here.
First, execute the following in SQLPLUS (or save it as a file on the system and call it from SQLPlus using @c:\dir\file.sql)
CREATE OR REPLACE TYPE VCARRAY AS TABLE OF VARCHAR2(4000) / CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED Run_Cmd as import java.io.*; import java.sql.*; import java.util.*; import oracle.sql.*; import oracle.jdbc.driver.*; public class Run_Cmd{ public static oracle.sql.ARRAY run_it(java.lang.String p_cmd) throws java.sql.SQLException,IOException { Runtime v_rt = Runtime.getRuntime(); ArrayList v_output = new ArrayList(); try { Process v_proc = v_rt.exec(p_cmd); BufferedReader v_stdout = new BufferedReader( new InputStreamReader( v_proc.getInputStream() ) ); String v_line; while ((v_line = v_stdout.readLine()) != null) v_output.add(v_line); BufferedReader v_stderr = new BufferedReader( new InputStreamReader( v_proc.getErrorStream() ) ); while ((v_line = v_stderr.readLine()) != null) v_output.add(v_line); v_proc.waitFor(); } catch (Exception e) { e.printStackTrace(); } Connection v_conn = new OracleDriver().defaultConnection(); ArrayDescriptor v_descriptor = ArrayDescriptor.createDescriptor( "VCARRAY", v_conn ); return new ARRAY( v_descriptor, v_conn, v_output.toArray() ); } } / create or replace FUNCTION run_cmd(p_cmd IN VARCHAR2) RETURN vcarray AS LANGUAGE JAVA NAME 'Run_Cmd.run_it( java.lang.String ) return oracle.sql.ARRAY';
/
On my server I have the 7zip command-line tool installed. I then created a c:\temp directory, copied miscellaneous files into it, including a few with .sql for their extensions. Now if I execute the following PL/SQL statement it will create an archive named "oracle.zip" and add only files with the .sql extension to the archive.
select * from table(run_cmd('c:\progra~1\7za\7za.exe a c:\temp\oracle.zip *.sql'));
To use the OS command, you need to call the command interpreter with a switch that indicates that it needs to close that instance of the interpreter after the command finishes. To delete the file created above, you would execute the following PL/SQL statement:
select * from table(run_cmd('c:\windows\system32\cmd.exe /c del c:\temp\oracle.zip'));
Hope this helps someone else out like it did me.
No comments:
Post a Comment