Q

Trigger running a shell process on Unix without pipes

This Content Component encountered an error

How can an Oracle trigger run a shell process on Unix, not using pipes?

You would need to create a Java procedure and then call that procedure from within your trigger.

Here's an example of how to create the procedure and execute it (using v8.1.6). You may have to work with it a bit to create it so that it handles properly when called via a trigger but it should work fine as a stand-alone procedure.

First you have to grant some privileges to allow you to execute the program /usr/bin/ps. As SYS or some appropriately priveleged user, execute:

sys@mydb> begin
  2      dbms_java.grant_permission
  3      ('USER33',
  4       'java.io.FilePermission',
  5       '/usr/bin/ps',
  6       'execute');
  7
  8      dbms_java.grant_permission
  9      ('USER33',
 10       'java.lang.RuntimePermission',
 11       '*',
 12       'writeFileDescriptor' );
 13  end;
 14  /

PL/SQL procedure successfully completed.
That allows user USER33 to successfully execute that program. This user could have been allowed to execute any program but for this example we chose just that one program.

Now, USER33 would create in its schema:

user33@mydb> 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
 10    public static int RunThis(String[] args)
 11    {
 12    Runtime rt = Runtime.getRuntime();
 13    int        rc = -1;
 14
 15    try
 16    {
 17       Process p = rt.exec(args[0]);
 18
 19       int bufSize = 4096;
 20       BufferedInputStream bis =
 21        new BufferedInputStream(p.getInputStream(), bufSize);
 22       int len;
 23       byte buffer[] = new byte[bufSize];
 24
 25       // Echo back what the program spit out
 26       while ((len = bis.read(buffer, 0, bufSize)) != -1)
 27          System.out.write(buffer, 0, len);
 28
 29       rc = p.waitFor();
 30    }
 31    catch (Exception e)
 32    {
 33       e.printStackTrace();
 34       rc = -1;
 35    }
 36    finally
 37    {
 38       return rc;
 39    }
 40    }
 41  }
 42  /

Java created.

user33@mydb> 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.
To make this callable as a procedure (ignoring the return code), we'll create a procedure:
user33@mydb>
user33@mydb> 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.
And now to run it:
user33@mydb> set serveroutput on size 1000000
user33@mydb> exec dbms_java.set_output(1000000)
PL/SQL procedure successfully completed.
user33@mydb> exec rc('/usr/bin/ps -ef');
UID   PID  PPID  C    STIME TTY      TIME CMD
root     0     0  0   Aug 17 ?        0:06 sched
root     1     0  0   Aug 17 ?        1:19 /etc/init -
root     2     0  0   Aug 17 ?        0:23 pageout
.....

PL/SQL procedure successfully completed.

For More Information


This was first published in June 2002

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close