DBMS_PIPE

This package provides a way to send messages asynchronously between two sessions. Unlike alerts, messages sent with DBMS_PIPE are not transaction dependent. Oracle allows you to create pipes within the database that can hold a maximum of 8,192 bytes of information at any given time. Pipes can be public or private, and access to them can be restricted. Unlike alerts, messages sent via pipes are not readable by multiple sessions because the receiving session removes the message from the pipe.

The basic steps to send and receive data with DBMS_PIPE are as follows. A pipe is created. A data set consisting of various datatypes is packed into a message and sent to the pipe. From this point the receiving session detects the presence of the message, retrieves it from the pipe, determines the datatype of each item, and unpacks it into a variable of the proper type.

PROCEDURE PACK_MESSAGE(

     item IN VARCHAR2);

PROCEDURE PACK_MESSAGE(

     item IN NUMBER);

PROCEDURE PACK_MESSAGE(

     item IN DATE);

PROCEDURE PACK_MESSAGE(

     item IN RAW);

PROCEDURE PACK_MESSAGE(

     item IN ROWID);

This procedure is overloaded to handle the listed datatypes. The total size limit of a message is 4,096 bytes. Of this, three bytes are used per item to specify the type, length, and size, and one byte is used to terminate the message.

FUNCTION CREATE_PIPE(

     pipename IN VARCHAR2,

     maxpipesize IN INTEGER default 8192,

     private IN BOOLEAN default TRUE)

RETURN INTEGER;

Like SEND_MESSAGE, this function creates a pipe, but it also allows the pipe to be designated as private. Private pipes are only available to their creator or SYSDBA. Normally, there is no reason to use this function to create a public pipe because SEND_MESSAGE creates one automatically. The function returns a 0 for success. If there is a like-named pipe with a different owner, or a public pipe with the same name, ORA-23322 is returned.

FUNCTION UNIQUE_SESSION_NAME

RETURN VARCHAR2;

This function returns a string that uniquely identifies the session it is issued from. This string can be used to name a pipe and then be packed into a message that is sent through an existing pipe whose name is known to the receiving session. The receiver unpacks the message and gets the name of the uniquely identified pipe that the sender has prepared, and replies with a message through this uniquely identified pipe. This method allows sessions to set up a private communication on a pipe whose name is known only to them. There is no chance of an outside session getting messages before the intended session can.

FUNCTION SEND_MESSAGE(

     pipename IN VARCHAR2,

     timeout IN INTEGER default MAXWAIT,

     maxpipesize IN INTEGER default 8192)

RETURN INTEGER;

This function returns a 0 for success, 1 if it times out, or 3 if an internal error interrupts the message.

FUNCTION RECEIVE_MESSAGE(

     pipename IN VARCHAR2,

     timeout IN INTEGER default MAXWAIT)

RETURN INTEGER;

The parameters provided to RECEIVE_MESSAGE are identical to those provided for SEND_MESSAGE. This function returns a 0 for success, 1 if it timed out, 2 if the message is too large for the buffer, or 3 if there is an internal error that interrupts the call.

FUNCTION NEXT_ITEM_TYPE

RETURN INTEGER;



0 = no more items in message

6 = NUMBER

9 = VARCHAR2

11 = ROWID

12 = DATE

23 = RAW

This function is used to determine the datatype of the next item in the message. Unless the message follows a known format, this function is a necessary step before unpacking the next item for storage in the variable provided with item.

UNPACK_MESSAGE(

     item OUT VARCHAR2);

UNPACK_MESSAGE(

     item OUT NUMBER);

UNPACK_MESSAGE(

     item OUT DATE);

UNPACK_MESSAGE(

     item OUT RAW);
UNPACK_MESSAGE(

     item OUT ROWID);

The item value is the variable in which to store the next value of the message. The data type of the next item is determined either by using the NEXT_ITEM_TYPE function or by prior knowledge of the message format.

FUNCTION REMOVE_PIPE(

     pipename IN VARCHAR)

RETURN INTEGER;

This function returns a 0 if the pipe is removed or did not exist in the first place, and raises ORA-23322 if the user does not have privileges for the pipe.

PROCEDURE PURGE(

     pipename IN VARCHAR2);