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;