OracleBlog

You would like to communicate with a 3rd-party application from your PL/SQL program, for example you want to run a UNIX command. Or perhaps you'd like to communicate directly with another Oracle session. Whatever your specific communication needs are, DBMS_PIPE is your solution.

What is DBMS_PIPE?

DBMS_PIPE is a package provided by Oracle that allows two or more sessions in the same instance to communicate.

If you know anything about UNIX pipes, its a similar concept in Oracle. It is called a pipe because it connects two (or more) sessions, and messages are queued up inside, just like a pipe. Each session can take the next received item out of a pipe, or insert the next item to send. Anybody (with access) can insert or remove something from the pipe, in any order. Messages can only be removed and read once - two people can't remove the same message from a pipe.

In more real terms, these pipes are buffers in the system global area (the SGA). Messages are prepared for loading using PACK_MESSAGE, loaded into a pipe using SEND_MESSAGE, and read similarly (RECEIVE_MESSAGE then UNPACK_MESSAGE).

There are basically 3 different types of pipes. Briefly:
Implicit Public Pipe: Automatically created when first accessed, disappears when it is emptym, available to entire schema
Explicit Public Pipe: Created with CREATE_PIPE, freed with REMOVE_PIPE, available to entire schema
Explicit Private Pipe: Created with CREATE_PIPE and private=true, freed with REMOVE_PIPE, available only to that userid privileges, or SYSDBA.

Check chapter 45 of the PL/SQL Supplied Packages and Types Reference.
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612.pdf

Example:

For this test, open up two sessions as the SAME user on the SAME instance. Make sure your user has access to the DBMS_PIPE package.

We are going to have the first instance create the pipe, send in an SQL command, and have the second instance retrieve that message and execute it. That should accomplish two things: show the basic usage of DBMS_PIPE, and give Pete Finnigan a heart attack.

I have largely left out error-checking and handling, for brevity's sake. At many points throughout the "ORA-23322" error is possible, which is for insufficient privileges to access that pipe.

Session #1:

DECLARE
 l_status NUMBER(2);
BEGIN

-- Create the pipe.  0 is success.  
-- Possible error: name in use.
l_status := DBMS_PIPE.CREATE_PIPE (pipename => 'TEST_PIPE', 
 maxpipesize => 8192, private => TRUE);
 
-- Let's pack an instruction for another session.  
-- Possible error: buffer overflow (4093 bytes of data)
DBMS_PIPE.PACK_MESSAGE(item => 'SELECT SYSDATE FROM DUAL;');
 
-- Let's stuff it into the pipe 
-- We'll use defaults for maxpipesize and timeout
-- Returns 0 on success, 1 for timeout, 3 for interrupt
l_status := DBMS_PIPE.SEND_MESSAGE(pipename => 'TEST_PIPE');
 
-- Ok we're done, we should get 0 for success
l_status := DBMS_PIPE.REMOVE_PIPE (pipename => 'TEST_PIPE');

EXCEPTION
 WHEN OTHERS
 THEN
  DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
  DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;

Session #2:

DECLARE
 l_received_message VARCHAR2(128);
 l_message_type NUMBER(2);
 l_status NUMBER(2);
BEGIN

-- Receive the message, use default timeout
-- If the pipe doesn't exist, Oracle will create it, 
-- and wait for a message.
-- Returns 0 on success, 1 on a timeout, 3 for an interrupt
l_status := DBMS_PIPE.RECEIVE_MESSAGE(pipename => 'TEST_PIPE');

-- Ok, so what type are we extracting?
-- 0 nothing, 6 number, 9 varchar2, 11 ROWID, 12 DATE, 23 RAW
l_message_type := DBMS_PIPE.NEXT_ITEM_TYPE;
 
-- Open up the message, we can get ORA-06556 or ORA-06559 
-- if its the wrong time, or nothing is left.
IF (l_message_type = 9) THEN
 DBMS_PIPE.UNPACK_MESSAGE(item => l_received_message);
 EXECUTE IMMEDIATE(l_received_message);
END IF;

EXCEPTION
 WHEN OTHERS
 THEN
  DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
  DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;

By the way, I realise that I have removed the pipe in the first session before accessing that message in the second session. But because there is still a message in there, the pipe will stick around. To destroy it immediately I would have to purge it first.

More Examples:

The aforementioned guide has an example on how to use DBMS_PIPE for debugging. There is also a great example on communication with the shell to execute UNIX commands (like listing the contents of a directory). These are complete, excellent examples.

I'll close with some other little notes about DBMS_PIPES:
- There is a constant called maxwait which determines how long the pipe will wait for a message to be picked up.
- The two major errors with DBMS_PIPE are ORA-23321 (bad pipename) or ORA-23322 (insufficient privileges).
- You can use PURGE to empty out a pipe, and RESET_BUFFER for the local packing buffer
- You can use UNIQUE_SESSION_NAME to help distinguish between sessions if there are several accessing the pipe.