Running O/S command from PL/SQL 22 June, 2007
This tip has been completely extracted from http://www.oracle-base.com/articles/8i/ShellCommandsFromPLSQL.php
It will allow you to execute O/S command from PL/SQL.
It uses Java to interact with the O/S then the java class is wrapped in an ORACLE Function so that the function can be executed within standard PL/SQL
there can be numerous application for such utility in many integration solution
After you create the following script, you need to expose it to Oracle PL/SQL as shown in step (2)
Then grant the appropriate privileges as shown in Step (3)
STEP (1)
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
public static void executeCommand(String command) {
try {
String[] finalCommand;
if (isWindows()) {
finalCommand = new String[4];
// Use the appropriate path for your
windows version.
finalCommand[0] =
"C:\\windows\\system32\\cmd.exe"; // Windows XP/2003
//finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";
// Windows NT/2000
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
}
else {
finalCommand = new String[3];
finalCommand[0] = "/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}
final Process pr =
Runtime.getRuntime().exec(finalCommand);
pr.waitFor();
new Thread(new Runnable(){
public void run() {
BufferedReader br_in =
null;
try {
br_in = new
BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff =
null;
while ((buff
= br_in.readLine()) != null) {
System.out.println("Process out :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process output.");
ioe.printStackTrace();
}
finally {
try {
br_in.close();
} catch
(Exception ex) {}
}
}
}).start();
new Thread(new Runnable(){
public void run() {
BufferedReader br_err =
null;
try {
br_err = new
BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff =
null;
while ((buff
= br_err.readLine()) != null) {
System.out.println("Process err :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process error.");
ioe.printStackTrace();
}
finally {
try {
br_err.close();
} catch
(Exception ex) {}
}
}
}).start();
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}
public static boolean isWindows() {
if (System.getProperty("os.name").toLowerCase().indexOf("windows")
!= -1)
return true;
else
return false;
}
};
/
show errors java source "Host"
STEP(2)
SQL>CREATE OR REPLACE PROCEDURE host_command (p_command IN
VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';
STEP(3)
SQL>EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME', 'java.io.FilePermission',
'<<ALL FILES>>', 'read ,write, execute, delete');
SQL>EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission',
'writeFileDescriptor', '');
SQL>EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission',
'readFileDescriptor', '');
Interesting Example
|