Friday, March 20, 2009

Executing Windows Operating System Commands from Oracle

CD293CD1-6624-464B-A676-936E7F020724.jpg

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: