 |
|
PL/SQL Using Pipes
Oracle Tips by
Burleson
|
Let’s go back to the order entry system example that we discussed
for the DBMS_Alert package. When an order is created or
modified, data in a legacy system must be created or modified as well.
A trigger is implemented on the ORDERS table that packs a
message and sends the message over a pipe. A Pro*C program is
implemented to interface with the legacy system and to receive
messages over the pipe. Listing 9.2 illustrates how a message can be
sent over a pipe.
Listing 9.2 Using a trigger to send a message over a pipe.
TRIGGER ORDERS_ARIU
AFTER INSERT OR UPDATE ON ORDERS
FOR EACH ROW
ORDERS_PIPE CONSTANT varchar2 (30) := 'ORDER';
iMessageStatus integer;
BEGIN
DBMS_Pipe.Pack_Message (:new.order_number);
DBMS_Pipe.Pack_Message (:new.customer_number);
DBMS_Pipe.Pack_Message (:new.order_cost);
iMessageStatus := DBMS_Pipe.Send_Message (ORDERS_PIPE);
END ORDERS_ARIU;
The Pro*C program is still started by the system when the order
entry form is run. This program makes calls to the
DBMS_Pipe.Receive_Message() function:
iMessageReceived := DBMS_Pipe.Receive_Message (ORDERS_PIPE);
When iMessageReceived holds a 0 after this call, the program
begins calling the DBMS_Pipe.Unpack_Message() procedures to
extract individual pieces of data from the message:
IF (iMessageReceived = 0) THEN
DBMS_Pipe.Unpack_Message (item => iOrderNumber);
DBMS_Pipe.Unpack_Message (item => iCustomerNumber);
DBMS_Pipe.Unpack_Message (item => nOrderValue);
END IF;
In this implementation, the Pro*C program does not have to make a
reference back to the ORDERS table to retrieve data. The
ORDERS_ARIU trigger can send several messages, and the listener
program will process each order in succession, with no loss of data.
To implement this type of functionality using signals, the Pro*C
program has to refer back to the ORDERS table to query
information.
Figure 9.4 illustrates the high-level processing of this
implementation.
Figure 9.4 High-level processing of a pipe-based
implementation.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
