DBMS_PIPE - For Inter-Session Communication
DBMS_PIPE package provides a non-secure mechanism for inter-session messaging. It is considered non-secure because messages can be lost from the pipe if the instance crashes or is shutdown before they are processed. Advanced Queues are arguably a better mechanism when secure messaging and greater flexibility are required.
There are two types of pipes:
- Implicit Pipes - These are created automatically when a message is sent with an unknown pipename using the
SEND_MESSAGEfunction. Implicit pipes disappear when they are empty.
- Explicit Pipes - These are created using the
CREATE_PIPEfunction. Explicitly created pipes must be removed using the
There are two levels of security for pipes:
- Public Pipes - These are accessible by any user with
EXECUTEpermission on the
DBMS_PIPEpackage. Implicit pipes are always public, but they can also be created explicitly by calling the
CREATE_PIPEfunction with the
privateparameter set to FALSE.
- Private Pipes - These are only accessible by sessions with the same ownerid as the pipe creator, stored programs owned by the pipe creator or by users connected as SYSDBA.
The following example used the
CREATE_PIPE function to create explicit public and private pipes. The
V$DB_PIPES view displays information about the pipes.
DECLARE l_result INTEGER; BEGIN -- Explicit public pipe. l_result := DBMS_PIPE.create_pipe(pipename => 'explicit_public_pipe', private => FALSE); -- Explicit private pipe. l_result := DBMS_PIPE.create_pipe(pipename => 'explicit_private_pipe'); END; / PL/SQL procedure successfully completed. COLUMN name FORMAT A30 SELECT * FROM v$db_pipes; OWNERID NAME TYPE PIPE_SIZE ---------- ------------------------------ ------- ---------- 55 EXPLICIT_PRIVATE_PIPE PRIVATE 358 EXPLICIT_PUBLIC_PIPE PUBLIC 357 2 rows selected. SQL>
The following example uses the
REMOVE_PIPE function to remove the pipes created previously.
DECLARE l_result INTEGER; BEGIN -- Explicit public pipe. l_result := DBMS_PIPE.remove_pipe(pipename => 'explicit_public_pipe'); -- Explicit private pipe. l_result := DBMS_PIPE.remove_pipe(pipename => 'explicit_private_pipe'); END; / PL/SQL procedure successfully completed. COLUMN name FORMAT A30 SELECT * FROM v$db_pipes; no rows selected SQL>
Messages are packed, one variable at a time, into a buffer using the
PACK_MESSAGE procedure. Once the message is complete it is sent using the
SEND_MESSAGE function. Messages are recieved using the
RECEIVE_MESSAGE function and unpacked, one variable at a time, using the
UNPACK_MESSAGE procedure. The following code creates a package (message_api) that contains two procedures (send and receive) that allow messages to be sent an received on an implicit public pipe.
CREATE OR REPLACE PACKAGE message_api AS PROCEDURE send (p_number IN NUMBER, p_text IN VARCHAR2, p_date IN DATE DEFAULT SYSDATE); PROCEDURE receive; END message_api; / SHOW ERRORS CREATE OR REPLACE PACKAGE BODY message_api AS PROCEDURE send (p_number IN NUMBER, p_text IN VARCHAR2, p_date IN DATE DEFAULT SYSDATE) AS l_status NUMBER; BEGIN DBMS_PIPE.pack_message(p_number); DBMS_PIPE.pack_message(p_text); DBMS_PIPE.pack_message(p_date); l_status := DBMS_PIPE.send_message('message_pipe'); IF l_status != 0 THEN RAISE_APPLICATION_ERROR(-20001, 'message_pipe error'); END IF; END; PROCEDURE receive AS l_result INTEGER; l_number NUMBER; l_text VARCHAR2(32767); l_date DATE; BEGIN l_result := DBMS_PIPE.receive_message ( pipename => 'message_pipe', timeout => DBMS_PIPE.maxwait); IF l_result = 0 THEN -- Message received successfully. DBMS_PIPE.unpack_message(l_number); DBMS_PIPE.unpack_message(l_text); DBMS_PIPE.unpack_message(l_date); DBMS_OUTPUT.put_line('l_number: ' || l_number); DBMS_OUTPUT.put_line('l_text : ' || l_text); DBMS_OUTPUT.put_line('l_date : ' || l_date); ELSE RAISE_APPLICATION_ERROR(-20002, 'message_api.receive was unsuccessful. Return result: ' || l_result); END IF; END receive; END message_api; / SHOW ERRORS
To test the package, run the following code in one session. The session will appear to hang, waiting for a message to be read off the pipe.
CONN test/test SET SERVEROUTPUT ON EXEC message_api.receive;
In another session, run the following code to send a message.
CONN test/test BEGIN message_api.send(p_number => 12345, p_text => 'This is a test.', p_date => SYSDATE); END; /
The procedure call in the first session immediately returns, printing out the contents of the message, as seen below.
SQL> CONN test/test Connected. SQL> SET SERVEROUTPUT ON SQL> EXEC message_api.receive; l_number: 12345 l_text : This is a test. l_date : 20-NOV-2005 13:35:57 PL/SQL procedure successfully completed. SQL>
For further information see:
Hope this helps. Regards Tim...