How to run Window Command from PL/SQL

How to run Window Command from PL/SQL
mawinghoAsked:
Who is Participating?
 
sdstuberCommented:
if your database is running on windows you can do it with external procedure calls, with java stored procedure or with dbms_scheduler, or with .net plugin stored procedures
note, these will run on the server only.

no code on the database server will run a windows command on your client.

here's an example of a java stored procedure to run commands
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_command IN VARCHAR2)
        RETURN vcarray
    AS
        LANGUAGE JAVA
        NAME 'Run_Cmd.run_it( java.lang.String ) return oracle.sql.ARRAY';

Open in new window

0
 
mawinghoAuthor Commented:
Can you tell me how to use it?
Use SQL*Plus to load the entire code to Database?
0
 
sdstuberCommented:
yes, exactly

then to run something...

select * from table(run_cmd('your command here'))


you run it as a select because the comand may return multiple lines of output or errors
0
 
mawinghoAuthor Commented:
Thanks!
0
 
sdstuberCommented:
glad I could help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.