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;

/