Re: can we call unix scripts from oracle database

  • From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
  • To: "edwin devadanam" <edwin_kodamala@xxxxxxxxx>
  • Date: Fri, 30 Mar 2007 14:36:23 +0200

Edwin

I'm using this concept on 9i. I think it should work on 8i too - although
you'd have to test it. It should work on 10g as well, if you'd prefer to
have the same solution accross all versions. Though in 10g I'd recommend you
use dbms_scheduler, as previously suggested.

Make sure you have JVM installed, and working in the database. You won't
need much of a java pool for this (10M is more than enough).


grant create session, create procedure to jexec identified by jexec
/

alter user jexec default tablespace users quota 1m on users
/


call dbms_java.grant_permission (
      'JEXEC',
      'java.io.FilePermission',
      '/usr/bin/ls',
      'execute'
      );

-- Just replace the above reference to ls with whatever script you want to
call (but DO NOT grant execute on /bin/sh or ksh, for security reasons).

call dbms_java.grant_permission (
      'JEXEC',
      'java.lang.RuntimePermission',
      '*',
      'writeFileDescriptor'
      );


connect jexec/jexec

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

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

create or replace function JEXEC_F( p_cmd  in varchar2)
return number
AS LANGUAGE JAVA
NAME  'JExec.RunThis(java.lang.String) return integer';
/

create or replace procedure JEXEC_P(p_cmd in varchar2)
as
x number;
begin
x := JEXEC.JEXEC_F(p_cmd);
end;
/

If you don't care about the return code, call it with the procedure:

begin
jexec_p('/usr/bin/ls /tmp');
end;
/

To get the return code back, use the function directly.

The output of the command is - per default - written to a tracefile in
user_dump_dest. You can redirect it to sql*plus by set serveroutput  on  and
calling dbms_java.set_output();

Stefan


On 3/30/07, edwin devadanam <edwin_kodamala@xxxxxxxxx> wrote:

Stefan,

We have couple of  8i,9i and 10gR2 instances.
useful updates have come and i would like to more about this.
Could somebody provide be pratically applied scripts which are called from
oracle database.

thanks,
Edwin.K

*Stefan Knecht <knecht.stefan@xxxxxxxxx>* wrote:

Which database version ?

Stefan

On 3/30/07, edwin devadanam <edwin_kodamala@xxxxxxxxx> wrote:
>
>  Hi,
>
> Does anyone know how to call unix scripts from oracle database?
> Is it possible........or am i talking something alien.....
>
>
> regards,
> Edwin.K
>  ------------------------------
> The fish are biting.
> Get more 
visitors<http://us.rd.yahoo.com/evt=49679/*http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php?o=US2140&cmp=Yahoo&ctv=Q107Tagline&s=Y&s2=EM&b=50>on
 your site using Yahoo!
> Search Marketing.
>


------------------------------
Expecting? Get great news right away with email 
Auto-Check.<http://us.rd.yahoo.com/evt=49982/*http://advision.webevents.yahoo.com/mailbeta/newmail_tools.html>
Try the Yahoo! Mail 
Beta.<http://us.rd.yahoo.com/evt=49982/*http://advision.webevents.yahoo.com/mailbeta/newmail_tools.html>


Other related posts: