RESET_BUFFER procedure - Reset the local message buffer

The RESET_BUFFER procedure resets a pointer to the session's local message buffer back to the beginning of the buffer. Resetting the buffer causes subsequent PACK_MESSAGE calls to overwrite any data items that existed in the message buffer prior to the RESET_BUFFER call.

Syntax

Authorization

EXECUTE privilege on the DBMS_PIPE module.

Examples

In proc1, use the PACK_MESSAGE function to put a message for an employee named Sujata in the local message buffer. Call the RESET_BUFFER procedure to replace the message with a message for Bing, and then send the message on a pipe. In proc2, receive and unpack the message for Bing.

SET SERVEROUTPUT ON@ 

CREATE PROCEDURE proc1()
BEGIN
  DECLARE    v_status        INTEGER;
  DECLARE    status          INTEGER;
  SET status = DBMS_PIPE.PACK_MESSAGE('Hi, Sujata');
  SET status = DBMS_PIPE.PACK_MESSAGE('Can you attend a meeting at 3:00, today?');
  SET status = DBMS_PIPE.PACK_MESSAGE('If not, is tomorrow at 8:30 ok with you?');
  CALL DBMS_PIPE.RESET_BUFFER;
  SET status = DBMS_PIPE.PACK_MESSAGE('Hi, Bing');
  SET status = DBMS_PIPE.PACK_MESSAGE('Can you attend a meeting at 9:30, tomorrow?');
  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.UNPACK_MESSAGE(v_item);
  CALL DBMS_OUTPUT.PUT_LINE('Item: ' || v_item);
END@

CALL proc1@

CALL proc2@

This example results in the following output:

From proc1:

SEND_MESSAGE status: 0

From proc2:

RECEIVE_MESSAGE status: 0
Item: Hi, Bing
Item: Can you attend a meeting at 9:30, tomorrow?