Running dos command in PL/SQL

[Thameem]

Hi,
I need help to run the dos command through PL/SQL program unit.

 

[vivekm]

Yes, you could do so using a call to Java stored procedure.

import java.lang.Runtime;
import java.lang.Process;
import java.io.IOException;
import java.lang.InterruptedException;

class ExecuteCmd {

public static void main(String args[] {

System.out.println("Start executing"

try {

/* Execute the command using the Runtime object and get the
Process which controls this command */
Process p = Runtime.getRuntime().exec(args[0];

/* Use the following code to wait for the process to finish
and check the return code from the process */
try {

p.waitFor();

/* Handle exceptions for waitFor() */
} catch (InterruptedException intexc) {
System.out.println("Interrupted Exception on waitFor: " + intexc.getMessage());
}

System.out.println("Return code from process: "+ p.exitValue());
System.out.println("Done executing"

/* Handle the exceptions for exec() */
} catch (IOException e) {

System.out.println("IO Exception from exec: " + e.getMessage());
e.printStackTrace();

}
}
}

Compile the ExecuteCmd.java file
and load the ExecuteCmd.class file into the database by

$ loadjava -user system/manager ExecuteCmd.class

Then create the the stored procedure

CREATE OR REPLACE PROCEDURE executecmd (S1 VARCHAR2)
AS LANGUAGE JAVA
name 'ExecuteCmd.main(java.lang.String[])';
/

And now, time to test your program

SQL> set serveroutput on
SQL> call dbms_java.set_output(2000);
SQL> EXEC executecmd('/bin/touch /vis/a.txt');
Start executing
Return code from process: 0
Done executing
PL/SQL procedure successfully completed.

This time, we can use the host command of sqlplus to return to the OS to check the result

SQL> host
$ ls /vis
a.txt

ah...... one thing to remember, you have to specfy the full path of the OS command, or the stored procedure will not work

Alternatively, you could call an external C function.