Oracle Tips by
Let’s look at an example of some code that uses signals for
interprocess communication. Presume we have an order entry system that
must be tied to a legacy system. Data is input into the legacy system
via a Pro*C program. The trigger in Listing 9.1 is implemented on the
Listing 9.1 Using a trigger to send a signal.
TRIGGER ORDERS ARIU
AFTER INSERT OR UPDATE
FOR EACH ROW
CHANGED_ORDER_SIGNAL CONSTANT varchar2 (10) := 'Changed order';
NEW_ORDER_SIGNAL CONSTANT varchar2 (10) := 'New order';
IF INSERTING THEN
'A new order has been submitted.');
ELSIF UPDATING THEN
'An order has been changed.');
The Pro*C program is initiated by the system
whenever the order entry form is run. This code makes a call to the
DBMS_Alert.Register() procedure, as follows:
DBMS_ALERT.Register ('New order')
After registering for the signal, the Pro*C program
goes into a loop. During each loop cycle, the program calls the
DBMS_Alert.WaitOne (name => 'New order',
message => alert_message,
status => alert_return_value,
timeout => 1);
This instructs the WaitOne() procedure to wait for one
second. If no alert has occurred before the end of that second, the
procedure returns a value of 1 for the status parameter.
Figure 9.2 illustrates how this implementation works.
Figure 9.2 Using the DBMS_Alert package.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.