Execute os command on sql prompt

sys@> select comp_id, version from dba_registry;


COMP_ID                        VERSION

------------------------------ --------------------------

CATALOG                        9.2.0.6.0

CATPROC                        9.2.0.6.0

JAVAVM                         9.2.0.6.0  (<< If not Available Then) 


If you do not have java installed, your DBA can install it via initjvm.sql found in 

$ORACLE_HOME/javavm/install 



begin

 dbms_java.grant_permission

    ('DILIP',

     'java.io.FilePermission',

      '/usr/bin/ps',

      'execute');


dbms_java.grant_permission

       ('DILIP',

        'java.lang.RuntimePermission',

        '*',

        'writeFileDescriptor' );

 end;



begin

 dbms_java.grant_permission

    ('DILIP',

     'java.io.FilePermission',

      'dir',

      'execute');


dbms_java.grant_permission

       ('DILIP',

        'java.lang.RuntimePermission',

        '*',

        'writeFileDescriptor' );

 end;



@$ORACLE_HOME/javavm/install/initjvm



We need to start by granting some privs.  I'm going to grant as little as I have to get 

allow us to execute the program /usr/bin/ps.  As SYS or some appropriately priveleged 

user, we will execute:


begin

   dbms_java.grant_permission

   ('DILIP',

    'java.io.FilePermission',

    '/bin/ls',

    'execute');



   dbms_java.grant_permission

   ('DILIP',

     'java.lang.RuntimePermission',

     '*',

     'writeFileDescriptor' );

  end;

  /


PL/SQL procedure successfully completed.


ops$tkyte@ORA9I.WORLD> connect rt_test/rt_test

Connected.

ops$tkyte@ORA9I.WORLD> @login


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;

    }

    }

  }

  /


Java created.


 create or replace

  function RUN_CMD(p_cmd in varchar2) return number

  as

  language java

  name 'Util.RunThis(java.lang.String) return integer';

  /


Function created.


create or replace procedure RC(p_cmd in varchar2)

  as

    x number;

  begin

    x := run_cmd(p_cmd);

  end;

  /




Elapsed: 00:00:00.00

dilip@live >  exec rc('/bin/ps -ef');

java.lang.ArrayIndexOutOfBoundsException

at Util.RunThis(Util.java:14)


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.00

dilip@live > create or replace and compile

  2    java source named "Util"

  3    as

  4    import java.io.*;

  5    import java.lang.*;

  6    

  7    public class Util extends Object

  8    {

  9      public static int RunThis(String args)

 10      {

 11      Runtime rt = Runtime.getRuntime();

 12      int        rc = -1;

 13    

 14      try

 15      {

 16         Process p = rt.exec(args);

 17    

 18         int bufSize = 4096;

 19         BufferedInputStream bis =

 20          new BufferedInputStream(p.getInputStream(), bufSize);

 21         int len;

 22         byte buffer[] = new byte[bufSize];

 23    

 24         // Echo back what the program spit out

 25         while ((len = bis.read(buffer, 0, bufSize)) != -1)

 26            System.out.write(buffer, 0, len);

 27    

 28         rc = p.waitFor();

 29      }

 30      catch (Exception e)

 31      {

 32         e.printStackTrace();

 33         rc = -1;

 34      }

 35      finally

 36      {

 37         return rc;

 38      }

 39      }

 40    }

 41    /


Java created.


Elapsed: 00:00:00.01

dilip@live >  create or replace

  2     function RUN_CMD(p_cmd in varchar2) return number

  3     as

  4     language java

  5     name 'Util.RunThis(java.lang.String) return integer';

  6     /


Function created.


Elapsed: 00:00:00.00

dilip@live > 

dilip@live > Fnction created.

SP2-0734: unknown command beginning "Fnction cr..." - rest of line ignored.

dilip@live > 

dilip@live >  create or replace procedure RC(p_cmd in varchar2)

  2     as

  3       x number;

  4     begin

  5       x := run_cmd(p_cmd);

  6     end;

  7     /


Procedure created.


Elapsed: 00:00:00.00

dilip@live > variable x number;

dilip@live > set serveroutput on

dilip@live > exec dbms_java.set_output(100000);


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.00

dilip@live > exec :x := RUN_CMD('/bin/ls');

BEGIN :x := RUN_CMD('/bin/ls'); END;


*

ERROR at line 1:

ORA-29549: class DILIP.Util has changed, Java session state cleared

ORA-06512: at "DILIP.RUN_CMD", line 0

ORA-06512: at line 1



Elapsed: 00:00:00.00

dilip@live >  exec :x := RUN_CMD('/bin/ls');


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.03

dilip@live > print x


         X

----------

         0


dilip@live > variable x number;

dilip@live > set serveroutput on

dilip@live > exec dbms_java.set_output(100000);


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.00

dilip@live >  exec :x := RUN_CMD('/bin/ls');

initdw.ora

init.ora

initorcl.ora

initORCL.ora.bak

lkORCL

orapw

spORCLinit.ora


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.00

dilip@live > exec :x := RUN_CMD('/bin/ls -lrt')

total 92

-rw-r--r--  1 oralocal oinstall 12920 Mar  8  2002 initdw.ora

-rw-r--r--  1 oralocal oinstall  8385 Mar  9  2002 init.ora

-rw-r--r--  1 oralocal oinstall  8910 Jan 23 11:03 initORCL.ora.bak

-rw-r-----  1 oralocal oinstall  2560 Jan 23 11:56 spORCLinit.ora

-rwSr-----  1 oralocal oinstall  2560 Mar 29 10:00 orapw

-rw-r--r--  1 oralocal oinstall  8998 Apr 12 17:15 initorcl.ora

-rw-rw----  1 oralocal oinstall    24 Apr 26 15:58 lkORCL


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.01

dilip@live > exec :x := RUN_CMD('/bin/ps')

PID TTY          TIME CMD

14010 ?        00:00:00 sh <defunct>

14019 ?        00:00:00 sh

14025 ?        00:00:00 sendmail <defunct>

14566 ?        00:07:17 tnslsnr

29639 ?        00:00:00 sshd

29799 ?        00:00:00 sshd

32625 ?        00:00:00 oracle

32627 ?        00:00:01 oracle

32629 ?        00:00:01 oracle

32631 ?        00:00:00 oracle

32633 ?        00:00:00 oracle

32635 ?        00:00:00 oracle

32637 ?        00:00:00 oracle

32639 ?        00:00:00 oracle

32641 ?        00:00:00 oracle

32643 ?        00:00:00 oracle

32732 ?        00:00:00 oracle

32740 ?        00:00:01 oracle

309 ?        00:00:00 oracle

347 ?        00:00:00 ps

348 ?        00:00:00 oracle

349 ?        00:00:00 oracle


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.01

dilip@live >