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
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 ?
TOMASZ is user name 🙂
Dear all,
How do we change the path in unix.
in Unix you can execute many commands in one line cd /;ls;cd /home;ls
Excellent article, really helpful.
Pingback: Oracle database Host command | ivannexus
Really nice article, appreciated.
could you please share the how to store this command output and extract some data.
For example,
execute the host command “who am i”
and capture user,time & IP and store in a table.
Salam, how can I get mac address using oracle PLSQL Code?
Do we need create Java source code is compulsory