Oracle Tips by
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
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()
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()
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)
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.