Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter