Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | 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:

There are two levels of security for pipes:

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.