Previous Table of Contents Next


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.

The Next_Item_Type() Function

The Next_Item_Type() function is used when unpacking a pipe that can contain different types of values in the same positions. For instance, a message might contain either a company name or a company ID number in the same position. Depending on the value returned from this function, the next portion of the message will be placed into a character or number variable.

This function returns an integer value that indicates the datatype of the next section of the message. These values are listed in Table 9.2.

Table 9.2 Return values for the Next_Item_Type() function.

Return Value Datatype Of Next Message Section
0 There are no more values in the message
6 number
9 varchar2
12 date
23 raw

This function does not have any parameters.

The Pack_Message() Procedure

The Pack_Message() procedure is used to add information to a message. The procedure is overloaded. The different implementations of the procedure are as follows:

PROCEDURE Pack_Message (item IN     varchar2)
PROCEDURE Pack_Message (item IN     date)
PROCEDURE Pack_Message (item IN     number)
PROCEDURE Pack_Message (item IN     raw)
PROCEDURE Pack_Message (item IN     ROWID)

Each implementation of this procedure is designed to add data of a specific type to the message that will be sent over the pipe.

The Purge() Procedure

The Purge() procedure is used to completely empty the pipe, thus freeing the memory used by the pipe when it is removed from the SGA. Pipes are not removed from the SGA if they contain messages. Therefore, it is critical that unnecessary pipes are emptied using this procedure.

The procedure accepts a single parameter:

PROCEDURE Purge (pipename IN     varchar2)

The pipename parameter holds the name of an existing pipe.

This procedure empties the pipe by calling the Receive_Message() function repeatedly. Therefore, the values in the local buffer will probably change if you use the Purge() procedure.

Previous Table of Contents Next