Runube from Oracle

jwhite

Member
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 protected] 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.

---------------------------- Snip ----------------------------------
Overview
--------

This article contains a small working example of a PL/SQL external C
callout on UNIX platforms. The C code itself simply returns the square
of an integer parameter.

This code has been tested on 8.0.4 and 8.0.5 on Solaris, but should be
generic to all UNIX platforms.

A SQL script is supplied as well as a makefile to build and run the code.

In order for callouts to work, configure your tnsnames.ora and
listener.ora as follows:

Note: Remember to restart the listener.

Tnsnames.ora
------------

extproc_connection_data=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(host=my_hostname_or_IP_address)
(port=1521)
)
(CONNECT_DATA=
(SID=extproc)
)
)

Listener.ora
------------

SID_LIST_LISTENER=
(SID_LIST=
...
(SID_DESC= (SID_NAME=extproc)
(ORACLE_HOME=my/oraclehome/directory)
(PROGRAM=extproc)

)
...


The the source code is as follows:

square.c
--------

#include <stdio.h>
#include <stdlib.h>
int square(int n)
{
return(n*n);
}


square.mk (remember the tabs at the beginning of the appropriate lines.)
---------

all: build run

build:
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 \
SHARED_LIBNAME=square.so OBJS=square.o

run:
sqlplus scott/tiger @square.sql

clean:
rm square.o
rm square.so


square.sql
----------

CREATE OR REPLACE LIBRARY squarelib IS '/usr1/mydir/square.so';
/
show errors
CREATE OR REPLACE FUNCTION squarefunc(n IN BINARY_INTEGER)
RETURN BINARY_INTEGER AS EXTERNAL
LIBRARY squarelib
NAME "square"
LANGUAGE C
PARAMETERS
(n int, RETURN int);
/
show errors
set serverout on
BEGIN
DBMS_OUTPUT.PUT_LINE('squarelib.square ret code is...'||squarefunc(10));
END;
/
show errors

------------------------------- Snip ----------------------------

The example above calculates square roots for you. Here is my code for a more useful command, "orashell":

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

void orashell(char *);

void orashell(char *cmd)
{
int num;
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.

Regards,

Justin Miller
Idea Integration
[email protected]
 
Back
Top