Re: Database trigger to start C program???

Tony Ohara (ohara_at_apollo.csu.murdoch.edu.au) wrote:
: Hi Michelle,
 

: We had to do a similar thing here at Murdoch Uni.
 

: The Oracle database package you want is called dbms_alert.
: I had to "grant execute on dbms_alert to public" to make it
: available to the application owners (we have three apps all using
: the package in our database).

We had to do the same thing here. Screwy, since Sys is the owner of the package.

: A trigger generates an alert such as:
 

: dbms_alert.signal('YOUR_ALERT_NAME',to_char(:new.alert_no));
 

: The pro*c program then waits for an alert of that name
: and does whatever processing is required:
Keep in mind that, unless your C program is already running, your signal will go nowhere. That package will not give you a mechanism for starting a host executable from inside a database trigger or stored procedure.

In my last conversation with someone at Oracle, about three weeks ago, I asked about a host command that could be used from inside a stored procedure. The tech support rep informed me that my request was a common one and that no upcoming release has that feature implemented. Irritating, isn't it?

: dbms_alert.register('YOUR_ALERT_NAME');
: dbms_alert.waitone ('YOUR_ALERT_NAME',
: p_message,
: p_status,
: 3000000);

Instead of doing this, have you C program sleep for about 5 seconds, call waitone wiht a timeout of 0, then sleep again. Make sure that your process can be told to end execution normally too :) Doing a waitone for 3 million seconds is going to be a long wait.

: These parameters are described in the Oracle 7 Server
: Application Developers Guide.
 

: The Pro*C program I wrote is run as a UNIX daemon.
 

: Hope this helps.
Ditto.

:
: - Tony O'Hara

Jonathan W. Ingram
jwingram_at_whale.st.usm.edu Received on Thu Mar 30 1995 - 00:00:00 CEST