Calling unix command or windows command from PL/SQL

It’s useful to call UNIX/Windows command directly from PL/SQL. One of method is to use Java language embedded in database.

Java code

First we need to create java code inside database. Java is responsible for calling hosts programs and returns output from the called program back to pl/sql

create or replace and compile java source named "Host" as
 import java.io.*;
 public class Host {
 public static void runCmd(String command) {
 try {
 String[] strCmd;
 if (isWindows()) {
 strCmd = new String[4];
 strCmd[0] = "C:\\windows\\system32\\cmd.exe";
 strCmd[1] = "/y";
 strCmd[2] = "/c";
 strCmd[3] = command;
 }
 else {
 strCmd = new String[3];
 strCmd[0] = "/bin/sh";
 strCmd[1] = "-c";
 strCmd[2] = command;
 }
 
final Process pr = Runtime.getRuntime().exec(strCmd);
 new Thread(new Runnable() {
 public void run() {
 try {
 BufferedReader br_in = new BufferedReader(new
 InputStreamReader(pr.getInputStream()));
 String buff = null;
 while ((buff = br_in.readLine()) != null) {
 System.out.println(buff);
 try {Thread.sleep(100); } catch(Exception e) {}
 }
 br_in.close();
 }
 catch (IOException ioe) {
 System.out.println("Exception caught printing process output.");
 ioe.printStackTrace();
 }
 }
 }).start();
 
new Thread(new Runnable() {
 public void run() {
 try {
 BufferedReader br_err = new BufferedReader(new
 InputStreamReader(pr.getErrorStream()));
 String buff = null;
 while ((buff = br_err.readLine()) != null) {
 System.out.println(buff);
 try {Thread.sleep(100); } catch(Exception e) {}
 }
 br_err.close();
 }
 catch (IOException ioe) {
 System.out.println("Exception caught printing process error.");
 ioe.printStackTrace();
 }
 }
 }).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;
 }
};
/

PL/SQL wrapper

Then we need to create PL/SQL wrapper to call java code

CREATE OR REPLACE PROCEDURE host_command (p_command IN VARCHAR2)
AS language JAVA
name 'Host.runCmd(java.lang.String)';
/

Privileges

Unix

BEGIN
  dbms_java.grant_permission
  ( 'TOMASZ',
    'SYS:java.io.FilePermission', 
    '/bin/sh', 
    'execute' );
END;
/

Windows

BEGIN
  dbms_java.grant_permission
  ( 'TOMASZ', 
    'SYS:java.io.FilePermission', 
    'C:\windows\system32\cmd.exe', 
    'execute' );
END;
/

Test

Unix

SET serveroutput ON
call dbms_java.set_output(1000000);
 
BEGIN
  host_command('ls');
END;
/
 
Call completed.
/ora01/app/oracle/product/11.2.0/db_1/dbs
PL/SQL PROCEDURE successfully completed.

Windows

SET serveroutput ON
call dbms_java.set_output(1000000);
 
BEGIN
  host_command('dir');
END;
/
 
anonymous block completed
 Volume IN drive D IS Disk D
 Volume Serial NUMBER IS 6696-94B3
 
 Directory OF d:\app\oracle\product\11.2.0\dbhome_1\DATABASE
 
   2013-03-22  23:07
 . 2013-03-22  23:07
.. 2013-03-22  22:55
   archive        2013-03-22  23:01      2 048 
   hc_ora11g.dat  2013-03-22  23:07         40 
   initORA11G.ora 2005-12-22  05:07     31 744 
   oradba.exe     2013-03-23  12:30         241 
   oradim.LOG     2013-03-22  23:08       1 536 
   PWDORA11G.ora                
 5 File(s)         35 609 bytes                
 3 Dir(s)  133 292 769 280 bytes free

Have a fun 🙂
  Tomasz

5 thoughts on “Calling unix command or windows command from PL/SQL

  1. Thanks Tomasz. Can you please tell me more about giving the grant. WHat exactly is ‘TOMASZ’ in the grant permission procedure. Is it a database user or OS user ?

Leave a Reply

Your email address will not be published. Required fields are marked *