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



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




DBMS_Pipe Package
Oracle Tips by Burleson

The DBMS_Pipe package is used for asynchronous communication between processes. The basic concepts behind the use of pipes are illustrated in Figure 9.3.

Figure 9.3 The concepts behind the use of pipes.

Like alerts, pipes are often implemented through the use of database triggers. The basic processing of a pipe-based implementation is straightforward:

1.  Data is modified or created in a table.

2.  A trigger on the table packs data into the pipe using the DBMS_Pipe.Pack_Message() procedure.

3.  The trigger sends the message using the DBMS_Pipe.Send_Message() procedure.

4.  The listener module waits for a message using the DBMS_Pipe.Receive_Message() procedure. The message is stored in the local buffer.

5.  The listener module unpacks the message using the DBMS_Pipe.Unpack_Message() procedure.

6.  The listener module processes the data contained in the message.

7.  The listener module resets itself to read another message.

This implementation allows multiple messages to be sent to the listener module with no loss of data sent with individual messages. Messages are received by the listener module (or modules) in the same order in which the messages are sent.

The DBMS_Pipe package contains several procedures and functions intended for use by application developers:

  • The Create_Pipe() function

  • The Next_Item_Type() function

  • The Pack_Message() procedure

  • The Purge() procedure

  • The Receive_Message() function

  • The Remove_Pipe() function

  • The Reset_Buffer() procedure

  • The Send_Message() function

  • The Unique_Session_Name() function

  • The Unpack_Message() procedure

Each of these procedures and functions is discussed in the following sections. There is also an example of a simple pipes implementation.

The Create_Pipe() Function

A public pipe is accessible to any user who knows the name of the pipe. A private pipe is accessible only to its creator, to stored PL/SQL objects run by the creator, and to the certain system user IDS accessible to the DBA. Private pipes are often used when the security of data is of paramount importance.

Public pipes are created implicitly through the use of the Send_Message() function. The Create_Pipe() function can be used to create a private pipe. This function accepts three parameters: pipename, maxpipesize, and private.

FUNCTION Create_Pipe (pipename    IN     varchar2,
                      maxpipesize IN     integer := 8192,
                      private     IN     boolean := TRUE)
RETURN integer

The pipename parameter is a character string that identifies the pipe. The value for this parameter should not exceed 30 characters and cannot begin with the string ORA$ (this string is reserved for use by Oracle).

The maxpipesize parameter is the maximum size of the pipe in bytes. The value of the private parameter indicates whether the function creates a public or private pipe—TRUE indicates that the newly created pipe should be private.

The function will return 0 if the new pipe is successfully created. If the specified pipe already exists and you are able to access it, the function returns 0 and the existing pipe is not affected. Otherwise, an error is raised by the function.

Functions created through the use of the Create_Pipe() function must be removed using the Remove_Pipe() function.


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