Note We recommend that you do not use the DBMS_PIPE
package with the cluster endpoints. This may cause long-running transactions.
The following table describes the functions and stored procedures that are available in the
DBMS_PIPE
package.
Table 1. Functions and stored procedures in the DBMS_PIPE packageFunction or stored procedure | Return type | Description |
CREATE_PIPE(pipename [, maxpipesize ] [, private ]) | INTEGER | Explicitly creates a public or private pipe. |
NEXT_ITEM_TYPE | INTEGER | Returns the data type of the next item in a received message. |
PACK_MESSAGE(item) | N/A | Places a data item in the local message buffer of the current session. |
PURGE(pipename) | N/A | Removes unreceived messages from a specified implicitly created pipe. |
RECEIVE_MESSAGE(pipename [, timeout ]) | INTEGER | Obtains a message from a specified pipe. |
REMOVE_PIPE(pipename) | INTEGER | Deletes an explicitly created pipe. |
RESET_BUFFER | N/A | Resets the local message buffer. |
SEND_MESSAGE(pipename [, timeout ] [, maxpipesize ]) | INTEGER | Sends a message from the local message buffer of the current session to a specified pipe. |
UNIQUE_SESSION_NAME | VARCHAR2 | Obtains the unique name of the current session. |
UNPACK_MESSAGE(item OUT) | N/A | Copies the data items of a message from the local message buffer to a program variable. |
- Pipes can be created implicitly or explicitly.
- You can create a pipe implicitly by referencing a name if no pipes that have the same name are created by the
CREATE_PIPE
function. For example, if you use a non-existent pipe name when you call the SEND_MESSAGE
function, a pipe that uses that name will be created implicitly.
Note All implicitly created pipes are private pipes.
- You can create a pipe explicitly by calling the
CREATE_PIPE
function and specifying the name of the new pipe in the first parameter of the function.
- Pipes can be categorized as private pipes and public pipes.
- A private pipe can be accessed by only the creator of the pipe. Even a superuser cannot access private pipes that are created by other users.
- A public pipe can be accessed by all users that can access the
DBMS_PIPE
package.
- Pipes are used to send and receive messages.
- Pipes are used to send messages. The first individual data item or message line that is created in a local message buffer is unique to the current session. You can use the
PACK_MESSAGE
stored procedure to create a message in the local message buffer of the current session, and then use the SEND_MESSAGE
function to send the message through a pipe. - Pipes are used to receive messages. You can use the
RECEIVE_MESSAGE
function to retrieve a message from a specified pipe and write the message to the local message buffer of the current session. Then, you use the UNPACK_MESSAGE
stored procedure to transfer the message data items from the message buffer to a program variable. If a pipe contains multiple messages, RECEIVE_MESSAGE
retrieves the messages in first-in-first-out (FIFO) order.
Each session maintains separate message buffers for messages created by using the PACK_MESSAGE
stored procedure and messages retrieved by the RECEIVE_MESSAGE
function. Messages can be created and received in the same session. However, if RECEIVE_MESSAGE
is called consecutively, only the message from the last RECEIVE_MESSAGE
call is preserved in the local message buffer.
CREATE_PIPE
The
CREATE_PIPE
function is used to explicitly create a public or private pipe that has a specified name. Syntax:
status INTEGER CREATE_PIPE(pipename VARCHAR2
[, maxpipesize INTEGER ] [, private BOOLEAN ])
ParametersParameter | Description |
pipename | The name of the pipe. |
maxpipesize | The maximum capacity of the pipe. Unit: bytes. Default value: 8192. |
private | To create a public pipe, set this parameter to FALSE. To create a private pipe, set this parameter to TRUE. Default value: TRUE. |
status | The status code returned by the operation. 0 indicates successful creation. |
Examples- The following example creates a private pipe named messages:
DECLARE
v_status INTEGER;
BEGIN
v_status := DBMS_PIPE.CREATE_PIPE('messages');
DBMS_OUTPUT.PUT_LINE('CREATE_PIPE status: ' || v_status);
END;
The following output is displayed:CREATE_PIPE status: 0
- The following example creates a public pipe named mailbox:
DECLARE
v_status INTEGER;
BEGIN
v_status := DBMS_PIPE.CREATE_PIPE('mailbox',8192,FALSE);
DBMS_OUTPUT.PUT_LINE('CREATE_PIPE status: ' || v_status);
END;
The following output is displayed:CREATE_PIPE status: 0
NEXT_ITEM_TYPE
The
NEXT_ITEM_TYPE
function returns an integer code. The code is used to identify the data type of the next data item in a message that has been retrieved into the local message buffer of the current session. If an item is removed from the local message buffer by the
UNPACK_MESSAGE
stored procedure, the
NEXT_ITEM_TYPE
function returns the data type code for the next available item. If no more items exist in the message, data type code 0 is returned. Syntax:
typecode INTEGER NEXT_ITEM_TYPE
ParameterTable 2. Possible data type codes returned by the NEXT_ITEM_TYPE functionReturned code | Description |
0 | No more data items. |
9 | NUMBER |
11 | VARCHAR2 |
13 | DATE |
23 | RAW |
Note The data type codes listed in the table are not compatible with Oracle databases. Oracle assigns a different numbering sequence to data types.
ExamplesThe following example shows a pipe packed with a NUMBER item, a VARCHAR2 item, a DATE item, and a RAW item.
DECLARE
v_number NUMBER := 123;
v_varchar VARCHAR2(20) := 'Character data';
v_date DATE := SYSDATE;
v_raw RAW(4) := '21222324';
v_status INTEGER;
BEGIN
DBMS_PIPE.PACK_MESSAGE(v_number);
DBMS_PIPE.PACK_MESSAGE(v_varchar);
DBMS_PIPE.PACK_MESSAGE(v_date);
DBMS_PIPE.PACK_MESSAGE(v_raw);
v_status := DBMS_PIPE.SEND_MESSAGE('datatypes');
DBMS_OUTPUT.PUT_LINE('SEND_MESSAGE status: ' || v_status);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;
The following output is displayed:
SEND_MESSAGE status: 0
The following example shows the type code of each data item in the previous example returned by the NEXT_ITEM_TYPE
function. DECLARE
v_number NUMBER;
v_varchar VARCHAR2(20);
v_date DATE;
v_timestamp TIMESTAMP;
v_raw RAW(4);
v_status INTEGER;
BEGIN
v_status := DBMS_PIPE.RECEIVE_MESSAGE('datatypes');
DBMS_OUTPUT.PUT_LINE('RECEIVE_MESSAGE status: ' || v_status);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
v_status := DBMS_PIPE.NEXT_ITEM_TYPE;
DBMS_OUTPUT.PUT_LINE('NEXT_ITEM_TYPE: ' || v_status);
DBMS_PIPE.UNPACK_MESSAGE(v_number);
DBMS_OUTPUT.PUT_LINE('NUMBER Item : ' || v_number);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
v_status := DBMS_PIPE.NEXT_ITEM_TYPE;
DBMS_OUTPUT.PUT_LINE('NEXT_ITEM_TYPE: ' || v_status);
DBMS_PIPE.UNPACK_MESSAGE(v_varchar);
DBMS_OUTPUT.PUT_LINE('VARCHAR2 Item : ' || v_varchar);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
v_status := DBMS_PIPE.NEXT_ITEM_TYPE;
DBMS_OUTPUT.PUT_LINE('NEXT_ITEM_TYPE: ' || v_status);
DBMS_PIPE.UNPACK_MESSAGE(v_date);
DBMS_OUTPUT.PUT_LINE('DATE Item : ' || v_date);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
v_status := DBMS_PIPE.NEXT_ITEM_TYPE;
DBMS_OUTPUT.PUT_LINE('NEXT_ITEM_TYPE: ' || v_status);
DBMS_PIPE.UNPACK_MESSAGE(v_raw);
DBMS_OUTPUT.PUT_LINE('RAW Item : ' || v_raw);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
v_status := DBMS_PIPE.NEXT_ITEM_TYPE;
DBMS_OUTPUT.PUT_LINE('NEXT_ITEM_TYPE: ' || v_status);
DBMS_OUTPUT.PUT_LINE('---------------------------------');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;
The following output is displayed:RECEIVE_MESSAGE status: 0
----------------------------------
NEXT_ITEM_TYPE: 9
NUMBER Item : 123
----------------------------------
NEXT_ITEM_TYPE: 11
VARCHAR2 Item : Character data
----------------------------------
NEXT_ITEM_TYPE: 13
DATE Item : 02-OCT-07 11:11:43
----------------------------------
NEXT_ITEM_TYPE: 23
RAW Item : 21222324
----------------------------------
NEXT_ITEM_TYPE: 0
PACK_MESSAGE
The
PACK_MESSAGE
function is used to place a data item in the local message buffer of the current session. You must call the
PACK_MESSAGE
function at least once before you call the
SEND_MESSAGE
function. Syntax:
PACK_MESSAGE(item { DATE | NUMBER | VARCHAR2 | RAW })
ParameterParameter | Description |
item | Calculates the parameter values of acceptable data types. The calculated values are added to the local message buffer of the session. |
PURGE
The
PURGE
stored procedure is used to remove unreceived messages from a specified implicitly created pipe. Syntax:
PURGE(pipename VARCHAR2)
ParameterParameter | Description |
pipename | The name of the pipe. |
ExamplesThe following example sends two messages on a pipe:
DECLARE
v_status INTEGER;
BEGIN
DBMS_PIPE.PACK_MESSAGE('Message #1');
v_status := DBMS_PIPE.SEND_MESSAGE('pipe');
DBMS_OUTPUT.PUT_LINE('SEND_MESSAGE status: ' || v_status);
DBMS_PIPE.PACK_MESSAGE('Message #2');
v_status := DBMS_PIPE.SEND_MESSAGE('pipe');
DBMS_OUTPUT.PUT_LINE('SEND_MESSAGE status: ' || v_status);
END;
The following output is displayed:
SEND_MESSAGE status: 0
SEND_MESSAGE status: 0
The following example receives the first message and unpacks it:
DECLARE
v_item VARCHAR2(80);
v_status INTEGER;
BEGIN
v_status := DBMS_PIPE.RECEIVE_MESSAGE('pipe',1);
DBMS_OUTPUT.PUT_LINE('RECEIVE_MESSAGE status: ' || v_status);
DBMS_PIPE.UNPACK_MESSAGE(v_item);
DBMS_OUTPUT.PUT_LINE('Item: ' || v_item);
END;
The following output is displayed:RECEIVE_MESSAGE status: 0
Item: Message #1
The following example purges the pipe:EXEC DBMS_PIPE.PURGE('pipe');
The following example shows an attempt to retrieve the next message. The RECEIVE_MESSAGE
call returns a status code. DECLARE
v_item VARCHAR2(80);
v_status INTEGER;
BEGIN
v_status := DBMS_PIPE.RECEIVE_MESSAGE('pipe',1);
DBMS_OUTPUT.PUT_LINE('RECEIVE_MESSAGE status: ' || v_status);
END;
As displayed in the following output, status code 1 is returned, which indicates that a timeout occurs because no messages are available. RECEIVE_MESSAGE status: 1
RECEIVE_MESSAGE
The
RECEIVE_MESSAGE
function is used to retrieve a message from a specified pipe. Syntax:
status INTEGER RECEIVE_MESSAGE(pipename VARCHAR2
[, timeout INTEGER ])
ParameterParameter | Description |
pipename | The name of the pipe. |
timeout | The time spent waiting for available resources to run the node. Unit: seconds. Default value: 86400000 (1000 days). |
Table 3. Possible status codes returned by the RECEIVE_MESSAGE
functionStatus code | Description |
0 | The message is received successfully. |
1 | The operation times out. |
2 | The message in the pipe is too large for the buffer. |
3 | The operation is interrupted. |
ORA-23322 | You are not authorized to access messages in the pipe. |
REMOVE_PIPE
The
REMOVE_PIPE
function is used to delete a private or public pipe that is created explicitly. Syntax:
status INTEGER REMOVE_PIPE(pipename VARCHAR2)
ParametersParameter | Description |
pipename | The name of the pipe. |
status | The status code returned by the operation. Status code 0 is returned even if the specified pipe does not exist. |
ExamplesThe following example sends two messages on a pipe:
DECLARE
v_status INTEGER;
BEGIN
v_status := DBMS_PIPE.CREATE_PIPE('pipe');
DBMS_OUTPUT.PUT_LINE('CREATE_PIPE status : ' || v_status);
DBMS_PIPE.PACK_MESSAGE('Message #1');
v_status := DBMS_PIPE.SEND_MESSAGE('pipe');
DBMS_OUTPUT.PUT_LINE('SEND_MESSAGE status: ' || v_status);
DBMS_PIPE.PACK_MESSAGE('Message #2');
v_status := DBMS_PIPE.SEND_MESSAGE('pipe');
DBMS_OUTPUT.PUT_LINE('SEND_MESSAGE status: ' || v_status);
END;
The following output is displayed:
CREATE_PIPE status : 0
SEND_MESSAGE status: 0
SEND_MESSAGE status: 0
The following example receives the first message and unpacks it:
DECLARE
v_item VARCHAR2(80);
v_status INTEGER;
BEGIN
v_status := DBMS_PIPE.RECEIVE_MESSAGE('pipe',1);
DBMS_OUTPUT.PUT_LINE('RECEIVE_MESSAGE status: ' || v_status);
DBMS_PIPE.UNPACK_MESSAGE(v_item);
DBMS_OUTPUT.PUT_LINE('Item: ' || v_item);
END;
The following output is displayed:RECEIVE_MESSAGE status: 0
Item: Message #1
The following example removes the pipe:SELECT DBMS_PIPE.REMOVE_PIPE('pipe') FROM DUAL;
The following output is displayed:remove_pipe
-------------
0
(1 row)
The following code example shows an attempt to retrieve the next message. The RECEIVE_MESSAGE
call returns status code 1, which indicates that a timeout occurs because the pipe has been deleted. DECLARE
v_item VARCHAR2(80);
v_status INTEGER;
BEGIN
v_status := DBMS_PIPE.RECEIVE_MESSAGE('pipe',1);
DBMS_OUTPUT.PUT_LINE('RECEIVE_MESSAGE status: ' || v_status);
END;
The following output is displayed:RECEIVE_MESSAGE status: 1
RESET_BUFFER
The
RESET_BUFFER
stored procedure is used to reset a pointer to the local message buffer back to the beginning of the buffer. This allows subsequent
PACK_MESSAGE
calls to overwrite the data items that existed in the message buffer prior to the
RESET_BUFFER
call. Syntax:
RESET_BUFFER
ExamplesThe following example writes a message to
John
to the local message buffer. The
RESET_BUFFER
call replaces this message with a message to
Bob
.
DECLARE
v_status INTEGER;
BEGIN
DBMS_PIPE.PACK_MESSAGE('Hi, John');
DBMS_PIPE.PACK_MESSAGE('Can you attend a meeting at 3:00, today?');
DBMS_PIPE.PACK_MESSAGE('If not, is tomorrow at 8:30 ok with you?');
DBMS_PIPE.RESET_BUFFER;
DBMS_PIPE.PACK_MESSAGE('Hi, Bob');
DBMS_PIPE.PACK_MESSAGE('Can you attend a meeting at 9:30, tomorrow?');
v_status := DBMS_PIPE.SEND_MESSAGE('pipe');
DBMS_OUTPUT.PUT_LINE('SEND_MESSAGE status: ' || v_status);
END;
The following output is displayed:
SEND_MESSAGE status: 0
The message to
Bob
is displayed in the received message.
DECLARE
v_item VARCHAR2(80);
v_status INTEGER;
BEGIN
v_status := DBMS_PIPE.RECEIVE_MESSAGE('pipe',1);
DBMS_OUTPUT.PUT_LINE('RECEIVE_MESSAGE status: ' || v_status);
DBMS_PIPE.UNPACK_MESSAGE(v_item);
DBMS_OUTPUT.PUT_LINE('Item: ' || v_item);
DBMS_PIPE.UNPACK_MESSAGE(v_item);
DBMS_OUTPUT.PUT_LINE('Item: ' || v_item);
END;
The following output is displayed:
RECEIVE_MESSAGE status: 0
Item: Hi, Bob
Item: Can you attend a meeting at 9:30, tomorrow?
SEND_MESSAGE
The
SEND_MESSAGE
function is used to send a message from the local message buffer to a specified pipe. Syntax:
status SEND_MESSAGE(pipename VARCHAR2 [, timeout INTEGER ]
[, maxpipesize INTEGER ])
ParameterParameter | Description |
pipename | The name of the pipe. |
timeout | The timeout period. Unit: seconds. Default value: 86400000 (1000 days). |
maxpipesize | The maximum capacity of the pipe. Unit: bytes. Default value: 8192. |
Table 4. Possible status codes returned by the SEND_MESSAGE
functionStatus code | Description |
0 | The message is sent successfully. |
1 | The operation times out. |
3 | The operation is interrupted. |
ORA-23322 | You are not authorized to perform this operation.
Note If there is a pipe with the same name and the pipe is created by a different user, Oracle issues error code ORA-23322 to indicate a naming conflict. |
UNIQUE_SESSION_NAME
The
UNIQUE_SESSION_NAME
function is used to return a name that is unique to the current session. Syntax:
name VARCHAR2 UNIQUE_SESSION_NAME
ParameterParameter | Description |
name | The unique name of the session. |
ExampleDECLARE
v_session VARCHAR2(30);
BEGIN
v_session := DBMS_PIPE.UNIQUE_SESSION_NAME;
DBMS_OUTPUT.PUT_LINE('Session Name: ' || v_session);
END;
The following output is displayed:
Session Name: PG$PIPE$5$2752
UNPACK_MESSAGE
The
UNPACK_MESSAGE
stored procedure is used to copy the data items of a message from the local message buffer to a program variable. Before you use the
UNPACK_MESSAGE
stored procedure, you must place the message in the local message buffer by using the
RECEIVE_MESSAGE
function. Syntax:
UNPACK_MESSAGE(item OUT { DATE | NUMBER | VARCHAR2 | RAW })
ParameterParameter | Description |
item | A variable that receives a data item from the local message buffer. This variable must be compatible with the type of the data item. |
Comprehensive example
In the following example, a pipe is used as a "mailbox". A series of stored procedures are used to create the mailbox, add a multi-item message to the mailbox (up to three items), and display the full contents of the mailbox. These stored procedures are enclosed in a package named
mailbox
.
CREATE OR REPLACE PACKAGE mailbox
IS
PROCEDURE create_mailbox;
PROCEDURE add_message (
p_mailbox VARCHAR2,
p_item_1 VARCHAR2,
p_item_2 VARCHAR2 DEFAULT 'END',
p_item_3 VARCHAR2 DEFAULT 'END'
);
PROCEDURE empty_mailbox (
p_mailbox VARCHAR2,
p_waittime INTEGER DEFAULT 10
);
END mailbox;
CREATE OR REPLACE PACKAGE BODY mailbox
IS
PROCEDURE create_mailbox
IS
v_mailbox VARCHAR2(30);
v_status INTEGER;
BEGIN
v_mailbox := DBMS_PIPE.UNIQUE_SESSION_NAME;
v_status := DBMS_PIPE.CREATE_PIPE(v_mailbox,1000,FALSE);
IF v_status = 0 THEN
DBMS_OUTPUT.PUT_LINE('Created mailbox: ' || v_mailbox);
ELSE
DBMS_OUTPUT.PUT_LINE('CREATE_PIPE failed - status: ' ||
v_status);
END IF;
END create_mailbox;
PROCEDURE add_message (
p_mailbox VARCHAR2,
p_item_1 VARCHAR2,
p_item_2 VARCHAR2 DEFAULT 'END',
p_item_3 VARCHAR2 DEFAULT 'END'
)
IS
v_item_cnt INTEGER := 0;
v_status INTEGER;
BEGIN
DBMS_PIPE.PACK_MESSAGE(p_item_1);
v_item_cnt := 1;
IF p_item_2 != 'END' THEN
DBMS_PIPE.PACK_MESSAGE(p_item_2);
v_item_cnt := v_item_cnt + 1;
END IF;
IF p_item_3 != 'END' THEN
DBMS_PIPE.PACK_MESSAGE(p_item_3);
v_item_cnt := v_item_cnt + 1;
END IF;
v_status := DBMS_PIPE.SEND_MESSAGE(p_mailbox);
IF v_status = 0 THEN
DBMS_OUTPUT.PUT_LINE('Added message with ' || v_item_cnt ||
' item(s) to mailbox ' || p_mailbox);
ELSE
DBMS_OUTPUT.PUT_LINE('SEND_MESSAGE in add_message failed - ' ||
'status: ' || v_status);
END IF;
END add_message;
PROCEDURE empty_mailbox (
p_mailbox VARCHAR2,
p_waittime INTEGER DEFAULT 10
)
IS
v_msgno INTEGER DEFAULT 0;
v_itemno INTEGER DEFAULT 0;
v_item VARCHAR2(100);
v_status INTEGER;
BEGIN
v_status := DBMS_PIPE.RECEIVE_MESSAGE(p_mailbox,p_waittime);
WHILE v_status = 0 LOOP
v_msgno := v_msgno + 1;
DBMS_OUTPUT.PUT_LINE('****** Start message #' || v_msgno ||
' ******');
BEGIN
LOOP
v_status := DBMS_PIPE.NEXT_ITEM_TYPE;
EXIT WHEN v_status = 0;
DBMS_PIPE.UNPACK_MESSAGE(v_item);
v_itemno := v_itemno + 1;
DBMS_OUTPUT.PUT_LINE('Item #' || v_itemno || ': ' ||
v_item);
END LOOP;
DBMS_OUTPUT.PUT_LINE('******* End message #' || v_msgno ||
' *******');
DBMS_OUTPUT.PUT_LINE('*');
v_itemno := 0;
v_status := DBMS_PIPE.RECEIVE_MESSAGE(p_mailbox,1);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Number of messages received: ' || v_msgno);
v_status := DBMS_PIPE.REMOVE_PIPE(p_mailbox);
IF v_status = 0 THEN
DBMS_OUTPUT.PUT_LINE('Deleted mailbox ' || p_mailbox);
ELSE
DBMS_OUTPUT.PUT_LINE('Could not delete mailbox - status: '
|| v_status);
END IF;
END empty_mailbox;
END mailbox;
The first stored procedure creates a public pipe that has a name generated by the
UNIQUE_SESSION_NAME
function.
EXEC mailbox.create_mailbox;
The following output is displayed:
Created mailbox: PG$PIPE$13$3940
By using the mailbox name, a user in the same database that has access to the
mailbox
and
DBMS_PIPE
packages can add messages.
EXEC mailbox.add_message('PG$PIPE$13$3940','Hi, John','Can you attend a meeting at 3:00, today?','-- Mary');
EXEC mailbox.add_message('PG$PIPE$13$3940','Don''t forget to submit your report','Thanks,','-- Joe');
The following output is displayed:
Added message with 3 item(s) to mailbox PG$PIPE$13$3940Added message with 3 item(s) to mailbox PG$PIPE$13$3940
The contents of the
mailbox
are emptied.
EXEC mailbox.empty_mailbox('PG$PIPE$13$3940');
The following output is displayed:
****** Start message #1 ******
Item #1: Hi, John
Item #2: Can you attend a meeting at 3:00, today?
Item #3: -- Mary
******* End message #1 *******
*
****** Start message #2 ******
Item #1: Don't forget to submit your report
Item #2: Thanks,
Item #3: Joe
******* End message #2 *******
*
Number of messages received: 2
Deleted mailbox PG$PIPE$13$3940