The PURGE procedure removes unreceived messages in the specified implicit pipe.
EXECUTE privilege on the DBMS_PIPE module.
In proc1 send two messages on a pipe: Message #1 and Message #2. In proc2, receive the first message, unpack it, and then purge the pipe. When proc3 runs, the call to the RECEIVE_MESSAGE function times out and returns the status code 1 because no message is available.
SET SERVEROUTPUT ON@
CREATE PROCEDURE proc1()
BEGIN
DECLARE v_status INTEGER;
DECLARE status INTEGER;
SET status = DBMS_PIPE.PACK_MESSAGE('Message #1');
SET v_status = DBMS_PIPE.SEND_MESSAGE('pipe');
CALL DBMS_OUTPUT.PUT_LINE('SEND_MESSAGE status: ' || v_status);
SET status = DBMS_PIPE.PACK_MESSAGE('Message #2');
SET v_status = DBMS_PIPE.SEND_MESSAGE('pipe');
CALL DBMS_OUTPUT.PUT_LINE('SEND_MESSAGE status: ' || v_status);
END@
CREATE PROCEDURE proc2()
BEGIN
DECLARE v_item VARCHAR(80);
DECLARE v_status INTEGER;
SET v_status = DBMS_PIPE.RECEIVE_MESSAGE('pipe',1);
CALL DBMS_OUTPUT.PUT_LINE('RECEIVE_MESSAGE status: ' || v_status);
CALL DBMS_PIPE.UNPACK_MESSAGE_CHAR(v_item);
CALL DBMS_OUTPUT.PUT_LINE('Item: ' || v_item);
CALL DBMS_PIPE.PURGE('pipe');
END@
CREATE PROCEDURE proc3()
BEGIN
DECLARE v_item VARCHAR(80);
DECLARE v_status INTEGER;
SET v_status = DBMS_PIPE.RECEIVE_MESSAGE('pipe',1);
CALL DBMS_OUTPUT.PUT_LINE('RECEIVE_MESSAGE status: ' || v_status);
END@
CALL proc1@
CALL proc2@
CALL proc3@
This example results in the following output.
From proc1:
SEND_MESSAGE status: 0
SEND_MESSAGE status: 0
From proc2:
RECEIVE_MESSAGE status: 0
Item: Hi, Sujata
From proc3:
RECEIVE_MESSAGE status: 1