Re: Invoke a UNIX executable from a stored procedure? (SUMMARY)

Recently I posted this question. The best answers (IMHO) are the DBMS_PIPE or DBMS_ALERT packages, since they provides flexibility in what the server process does, and allows the client call to be wrapped nicely in a stored proc. I am used to Sybase's stored procs (which can return result sets), so I will have to get used to calling a stored proc, and then getting results from a table subsequently.

My next question is -- has anybody implemented an example of such a call, either in Pro*C or Oraperl (I am more familiar with Perl, but I will deal with Pro*C)?

Here is the summary of responses:

Carl (catech_at_cloud9.net)

• Begin SUMMARY ***
  1. Use DBMS_PIPE package, as explained in dbmspipe.sql, in $ORACLE_HOME/rdbms/admin. This involves having the "client" stored procedure write requests to one side of the pipe, and having the UNIX "server" process endlessly loop, reading these requests from the other end of the pipe. It can send status messages back on the pipe.

(from George Oliver <geo_at_usa.net>)

2. Use DBMS_ALERT utility. This can be called through a stored procedure or trigger, and a UNIX process needs to be set up which has registered itself for the signal.

(from Raja Jayakumar <raja_at_sdt.com>)

3. Use the package pios*.sql, which allows direct system calls using "os.system('sh ...'); This can be put in a stored proc or a trigger.

(from Edward Groenenberg OEM <egroenen_at_nl.oracle.com>)

4. Oracle product ("about $199 from your sales rep") called PLEX (PL SQL Extender), which allows Pro*C routines to be embedded into the trigger or procedure.

or

5. R-Shell from "The Root Group" which allows remote shell script calls (outside of ORACLE) from PC clients to UNIX servers.

(from Richard A. Wark <warkr_at_vanadium.brooks.af.mil>)

• End SUMMARY ***