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:
SEND_MESSAGE
function. Implicit pipes disappear when they are empty.CREATE_PIPE
function. Explicitly created pipes must be removed using the REMOVE_PIPE
function.There are two levels of security for pipes:
EXECUTE
permission on the DBMS_PIPE
package. Implicit pipes are always public, but they can also be created explicitly by calling the CREATE_PIPE
function with the private
parameter set to FALSE.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...
Back to normal view: https://oracle-base.com/articles/misc/dbms_pipe