3.10 DBMS_PIPE

Table of Contents Previous Next


3 Built-In Packages : 3.10 DBMS_PIPE

The DBMS_PIPE package provides the capability to send messages through a pipe within or between sessions connected to the same database cluster.
The procedures and functions available in the DBMS_PIPE package are listed in the following table:
CREATE_PIPE(pipename [, maxpipesize ] [, private ])
Explicitly create a private pipe if private is “true” (the default) or a public pipe if private is “false”.
Place item in the session’s local message buffer.
PURGE(pipename)
RECEIVE_MESSAGE(pipename [, timeout ])
REMOVE_PIPE(pipename)
SEND_MESSAGE(pipename [, timeout ] [, maxpipesize ])
Pipes are categorized as implicit or explicit. An implicit pipe is created if a reference is made to a pipe name that was not previously created by the CREATE_PIPE function. For example, if the SEND_MESSAGE function is executed using a non-existent pipe name, a new implicit pipe is created with that name. An explicit pipe is created using the CREATE_PIPE function whereby the first parameter specifies the pipe name for the new pipe.
Pipes are also categorized as private or public. A private pipe can only be accessed by the user who created the pipe. Even a superuser cannot access a private pipe that was created by another user. A public pipe can be accessed by any user who has access to the DBMS_PIPE package.
A public pipe can only be created by using the CREATE_PIPE function with the third parameter set to FALSE. The CREATE_PIPE function can be used to create a private pipe by setting the third parameter to TRUE or by omitting the third parameter. All implicit pipes are private.
The individual data items or “lines” of a message are first built-in a local message buffer, unique to the current session. The PACK_MESSAGE procedure builds the message in the session’s local message buffer. The SEND_MESSAGE function is then used to send the message through the pipe.
Receipt of a message involves the reverse operation. The RECEIVE_MESSAGE function is used to get a message from the specified pipe. The message is written to the session’s local message buffer. The UNPACK_MESSAGE procedure is then used to transfer the message data items from the message buffer to program variables. If a pipe contains multiple messages, RECEIVE_MESSAGE gets the messages in FIFO (first-in-first-out) order.
Each session maintains separate message buffers for messages created with the PACK_MESSAGE procedure and messages retrieved by the RECEIVE_MESSAGE function. Thus messages can be both built and received in the same session. However, if consecutive RECEIVE_MESSAGE calls are made, only the message from the last RECEIVE_MESSAGE call will be preserved in the local message buffer.
The CREATE_PIPE function creates an explicit public pipe or an explicit private pipe with a specified name.
status INTEGER CREATE_PIPE(pipename VARCHAR2
[, maxpipesize INTEGER ] [, private BOOLEAN ])
Create a public pipe if set to FALSE. Create a private pipe if set to TRUE. This is the default.
The NEXT_ITEM_TYPE function returns an integer code identifying the data type of the next data item in a message that has been retrieved into the session’s local message buffer. As each item is moved off of the local message buffer with the UNPACK_MESSAGE procedure, the NEXT_ITEM_TYPE function will return the data type code for the next available item. A code of 0 is returned when there are no more items left in the message.
typecode INTEGER NEXT_ITEM_TYPE
Note: The type codes list in the table are not compatible with Oracle databases. Oracle assigns a different numbering sequence to the data types.
The following example shows a pipe packed with a NUMBER item, a VARCHAR2 item, a DATE item, and a RAW item. A second anonymous block then uses the NEXT_ITEM_TYPE function to display the type code of each item.
The PACK_MESSAGE procedure places an item of data in the session’s local message buffer. PACK_MESSAGE must be executed at least once before issuing a SEND_MESSAGE call.
PACK_MESSAGE(item { DATE | NUMBER | VARCHAR2 | RAW })
Use the UNPACK_MESSAGE procedure to obtain data items once the message is retrieved using a RECEIVE_MESSAGE call.
3.10.4 PURGE
The PURGE procedure removes the unreceived messages from a specified implicit pipe.
PURGE(pipename VARCHAR2)
Use the REMOVE_PIPE function to delete an explicit pipe.
Try to retrieve the next message. The RECEIVE_MESSAGE call returns status code 1 indicating it timed out because no message was available.
The RECEIVE_MESSAGE function obtains a message from a specified pipe.
status INTEGER RECEIVE_MESSAGE(pipename VARCHAR2
[, timeout INTEGER ])
The REMOVE_PIPE function deletes an explicit private or explicit public pipe.
status INTEGER REMOVE_PIPE(pipename VARCHAR2)
Use the REMOVE_PIPE function to delete explicitly created pipes – i.e., pipes created with the CREATE_PIPE function.
Try to retrieve the next message. The RECEIVE_MESSAGE call returns status code 1 indicating it timed out because the pipe had been deleted.
The RESET_BUFFER procedure resets a “pointer” to the session’s local message buffer back to the beginning of the buffer. This has the effect of causing subsequent PACK_MESSAGE calls to overwrite any data items that existed in the message buffer prior to the RESET_BUFFER call.
The SEND_MESSAGE function sends a message from the session’s local message buffer to the specified pipe.
status SEND_MESSAGE(pipename VARCHAR2 [, timeout INTEGER ]
[, maxpipesize INTEGER ])
The UNIQUE_SESSION_NAME function returns a name, unique to the current session.
name VARCHAR2 UNIQUE_SESSION_NAME
The UNPACK_MESSAGE procedure copies the data items of a message from the local message buffer to a specified program variable. The message must be placed in the local message buffer with the RECEIVE_MESSAGE function before using UNPACK_MESSAGE.
UNPACK_MESSAGE(item OUT { DATE | NUMBER | VARCHAR2 | RAW })
The following demonstrates the execution of the procedures in mailbox. The first procedure creates a public pipe using a name generated by the UNIQUE_SESSION_NAME function.
Using the mailbox name, any user in the same database with access to the mailbox package and DBMS_PIPE package can add messages:

3 Built-In Packages : 3.10 DBMS_PIPE

Table of Contents Previous Next