SHELL FROM DATABASE PROCEDURE

EXECUTE SHELL SCRIPT FROM DATABASE PROCEDURE


Why do we need to execute shell script commands from database?

Advantages and Disadvantages

Implementation Code



Why do we need to execute shell script commands from database?

To explain this better i will give you one example. Suppose you have a 2 tier(applications reside on one server
(XX) and database on another server(YY))oracle apps system. Now you have to write a concurrent program to check if the file erps.txt exists on the server or not. if file exists the concurrent program should exit successfully if not it should return warning and for any other problems it should error out.

Here we have two choices to check if file exists or not. one is write a shell script to check the file on server and second option is write a PL/SQL procedure to check the file exists or not.

If you pick the first choice that is writing the shell script to check if file exists or not this program is going to check files on only appsserver which is XX and in the second choice it is going to check the file only on database server which is YY. what in case if you need to do both in one program? thats where you need to call shell script commands from database.


Advantages and Disadvantages

When you call use database procedure instead shell script you have the flexibility to pass what ever the return code you want to the concurrent manager thus making the concurrent program to exit with warning/success/error while with shell script you can not make the concurrent request as warning.

Main disadvantage would be security problem. If you say rm * as a shell command in the database procedure you write it will delete all the files on your server. you can avoid this by restricting the permissions only to few commands like ls, cp, mv e.t.c

Implementation Code:

Execute all the below code connecgted to your oracle apps database as APPS user.

-- Block to give the permission to the APPS user
begin
dbms_java.grant_permission
('APPS',
'java.io.FilePermission',
'/usr/bin/*',
'execute');

dbms_java.grant_permission
('APPS',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
end;
/

--Connect as APPS user and then compile the java code

create or replace and compile
java source named "Util"
as
import java.io.*;
import java.lang.*;

public class Util 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;
}
}
}
/

-- PL/SQL function to execute the host commands and return the code

create or replace
function RUN_CMD(p_cmd in varchar2) return number
as
language java
name 'Util.RunThis(java.lang.String) return integer';
/

-- If running from SQL*PLUS set these before you execute
variable x number;
set serveroutput on;
exec dbms_java.set_output(100000);

--PL/SQL Block to execute the commands

declare
x number;
begin
x := RUN_CMD('/usr/bin/ls /home/oracle; echo $?');
dbms_output.put_line('shell Returned with status:'x);
end;
/