Does anybody know how to execute a runube command from an Oracle trigger? We currently execute an interface UBE every 5 minutes with the Unix scheduler CRON. Our goal is to have the Runube run when activity occurs. Thank You. email@example.com B733 SP3, HP 9000, Oracle 8.05.
I have accomplished something similar -- pushing data from a trigger via lp and ftp to another device. I have used this technique with my current client to allow the Payment UBE's to send information to a fax server and a BACS box (U.K. bank interface device).
You can create a simple C program that takes an arbitrary command line and executes it as the unix user that owns the Oracle processes. This C program is compiled into a shared library and then cataloged within Oracle. A special external procedure network listener must also be configured.
This process is easiest if you are running Oracle 8.1.6. Oracle 8.1.6 comes configured out of the box for the extproc listener. Any version of 8i can be configured to work this way.
Since you are using 8.05 I have enclosed a snippet from the Oracle support site that works on Solaris 8.05 but should also work with HPUX.
int square(int n)
square.mk (remember the tabs at the beginning of the appropriate lines.)
all: build run
cc -K PIC -Xa -c square.c
# The above options work for the SPARCWorks C compiler on Solaris.
# You may wish to customize these or alternately comment out the above
# cc command and let the demo_rdbms.mk makefile perform the compilation
# as well as the link. The cc command is included above to demonstrate
# in a simple manner what is going on.
make -f $(ORACLE_HOME)/rdbms/demo/demo_rdbms.mk extproc_callback \
sqlplus scott/tiger @square.sql
CREATE OR REPLACE LIBRARY squarelib IS '/usr1/mydir/square.so';
CREATE OR REPLACE FUNCTION squarefunc(n IN BINARY_INTEGER)
RETURN BINARY_INTEGER AS EXTERNAL
(n int, RETURN int);
set serverout on
DBMS_OUTPUT.PUT_LINE('squarelib.square ret code is...'||squarefunc(10));
void orashell(char *cmd)
num = system(cmd);
You can use the orashell to issue a runube command. Since you need to issue this command as the jde user I would suggest you use the free sudo package (http://www.courtesan.com/sudo/sudo.html). This will allow you to give the oracle user the ability to execute the runube program.