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 the REMOVE_PIPE function.

There are two levels of security for pipes:

Public Pipes - These are accessible by any user with 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.
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...

Back to the Top.