DBMS_PIPE Package

DBMS_PIPE is a package supplied to allow two sessions to communicate with each other. It is an inter-process communication device.

One session can write a message on a pipe, and another session can read this message.

Members of DBMS_PIPE:
Sender session
Pack_message procedure
Send_message procedure

      Receiver Session
Unpack_message procedure
Receive_message procedure

Only SYS user has the privilege to use the DBMS_PIPE package.

SYS has to grant the privilege to scott:

Grant All on DBMS_PIPE to SCOTT;

CREATE OR REPLACE PACKAGE message_api  AS

procedure send(p_text varchar2);

procedure receive;

END message_api;

CREATE OR REPLACE PACKAGE BODY message_api AS

  PROCEDURE send (p_text varchar2)

   is

       l_status  NUMBER;

      begin

                     DBMS_PIPE.pack_message(p_text);

                      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

is

       l_result  INTEGER;

       l_text    VARCHAR2(32767);

     BEGIN

        l_result := DBMS_PIPE.receive_message (pipename => ‘message_pipe’);

         IF l_result = 0 THEN– Message received successfully.

               DBMS_PIPE.unpack_message(l_text);

                DBMS_OUTPUT.put_line(‘l_text  : ‘ || l_text);

           ELSE

RAISE_APPLICATION_ERROR(-20002, ‘message_api.receive was   unsuccessful.   Return result: ‘ || l_result);

         END IF;            

  END receive;

 

END message_api;

In the current session : Exec message_api.send(‘abc’);
In the another session : Exec message_api.receive;