OS command /Shell Script from PL/SQL

Running OS command /Shell Script from PL/SQL



Step 1 : Check Java installation

SQL>
Declare
v_check varchar2(20);
begin
select count(1) into v_check from ALL_REGISTRY_BANNERS where BANNER like '%JAVA%';
if v_check<>'1' then
raise_application_error(-20000,'ERROR: Jave is not installed !!!');
-- dbms_output.put_line('ERROR: Jave is not installed !!!');
return;
end if;
end;
/

Step 2: Run OS Command Setup
create or replace and compile java source named "DebaShellUtil"
as
import java.io.*;
import java.lang.*;
public class DebaShellUtil extends Object
{
public static int RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;
try
{
Process p = rt.exec(args);
int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];
// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);
rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/
SQL > show errors

Step 4: Intrgrated Function Setup Started
SQL>
create or replace function RUN_CMD(p_cmd in varchar2) return number
as
language java
name 'DebaShellUtil.RunThis(java.lang.String) return integer';
/
SQL>show errors

Step 5:  User Function Setup Started

create or replace procedure HOST(p_cmd in varchar2)
as
x number;
begin
x:= run_cmd(p_cmd);
end;
/

SQL> show errors


Example : How to run


SQL> exec dbms_java.set_output(100000);

SQL>set serveroutput on

SQL> exec sys.host('ls -lrt');

-rwxr-xr-x 1 oracle oinstall 1461 Jan 21 13:13 fri_traitsync.sql*

-rw-r--r-- 1 oracle oinstall 85 Jan 21 13:13 doc_stats_SUM.txt
-rw-r--r-- 1 oracle oinstall 86 Jan 21 13:13 doc_stats_CARE.txt
-rw-r--r-- 1 oracle oinstall 85 Jan 21 13:13 doc_stats_BCK.txt
-rw------- 1 oracle oinstall 637402338 Jan 21 13:13 dead.letter