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:
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);
CREATE OR REPLACE PACKAGE BODY message_api AS
PROCEDURE send (p_text varchar2)
l_status := DBMS_PIPE.send_message(‘message_pipe’);
IF l_status != 0 THEN
RAISE_APPLICATION_ERROR(-20001, ‘message_pipe error’);
l_result := DBMS_PIPE.receive_message (pipename => ‘message_pipe’);
IF l_result = 0 THEN– Message received successfully.
DBMS_OUTPUT.put_line(‘l_text : ‘ || l_text);
RAISE_APPLICATION_ERROR(-20002, ‘message_api.receive was unsuccessful. Return result: ‘ || l_result);
In the current session : Exec message_api.send(‘abc’);
In the another session : Exec message_api.receive;