8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
DBMS_PIPE - For Inter-Session Communication
The 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_MESSAGE
function. Implicit pipes disappear when they are empty. - Explicit Pipes - These are created using the
CREATE_PIPE
function. Explicitly created pipes must be removed using theREMOVE_PIPE
function.
There are two levels of security for pipes:
- Public Pipes - These are accessible by any user with
EXECUTE
permission on theDBMS_PIPE
package. Implicit pipes are always public, but they can also be created explicitly by calling theCREATE_PIPE
function with theprivate
parameter 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...