Re: can we call unix scripts from oracle database

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_at_yahoo.com> 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_at_gmail.com>* wrote:
>
> Which database version ?
>
> Stefan
>
> On 3/30/07, edwin devadanam <edwin_kodamala_at_yahoo.com> 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>
>
>

--
http://www.freelists.org/webpage/oracle-l