DB2 Version 9.7 for Linux, UNIX, and Windows

UNPACK_MESSAGE procedures - Get a data item from the local message buffer

The UNPACK_MESSAGE procedures retrieve the next data item from a message and assign it to a variable.

Before calling one of the UNPACK_MESSAGE procedures, use the RECEIVE_MESSAGE procedure to place the message in the local message buffer.

Syntax


>>-DBMS_PIPE.UNPACK_MESSAGE_NUMBER--(--item--)-----------------><


>>-DBMS_PIPE.UNPACK_MESSAGE_CHAR--(--item--)-------------------><


>>-DBMS_PIPE.UNPACK_MESSAGE_DATE--(--item--)-------------------><


>>-DBMS_PIPE.UNPACK_MESSAGE_RAW--(--item--)--------------------><

Procedure parameters

item

An output argument of one of the following types that specifies a variable to receive data items from the local message buffer.

Routine	Data type
UNPACK_MESSAGE_NUMBER	DECFLOAT
UNPACK_MESSAGE_CHAR	VARCHAR(4096)
UNPACK_MESSAGE_DATE	DATE
UNPACK_MESSAGE_RAW	BLOB(4096)

Authorization

EXECUTE privilege on the DBMS_PIPE module.

Example

In proc1, pack and send a message. In proc2, receive the message, unpack it using the appropriate procedure based on the item's type, and display the message to standard output.

SET SERVEROUTPUT ON@ 

CREATE PROCEDURE proc1()
BEGIN
  DECLARE status INT;
  SET status = DBMS_PIPE.CREATE_PIPE( 'pipe1' );
  SET status = DBMS_PIPE.PACK_MESSAGE('message1');
  SET status = DBMS_PIPE.SEND_MESSAGE( 'pipe1' );
END@

CREATE PROCEDURE proc2()
BEGIN
  DECLARE status   INT;
  DECLARE num1     DECFLOAT;
  DECLARE date1    DATE;
  DECLARE raw1     BLOB(100);
  DECLARE varchar1 VARCHAR(100);
  DECLARE itemType INTEGER;
  
  SET status = DBMS_PIPE.RECEIVE_MESSAGE( 'pipe1' );
  IF( status = 0 ) THEN
    SET itemType = DBMS_PIPE.NEXT_ITEM_TYPE();
    CASE itemType
      WHEN 6 THEN
        CALL DBMS_PIPE.UNPACK_MESSAGE_NUMBER( num1 );
        CALL DBMS_OUTPUT.PUT_LINE( 'num1: ' || num1 );
      WHEN 9 THEN
        CALL DBMS_PIPE.UNPACK_MESSAGE_CHAR( varchar1 );
        CALL DBMS_OUTPUT.PUT_LINE( 'varchar1: ' || varchar1 );
      WHEN 12 THEN
        CALL DBMS_PIPE.UNPACK_MESSAGE_DATE( date1 );
        CALL DBMS_OUTPUT.PUT_LINE( 'date1:' || date1 );
      WHEN 23 THEN
        CALL DBMS_PIPE.UNPACK_MESSAGE_RAW( raw1 );
        CALL DBMS_OUTPUT.PUT_LINE( 'raw1: ' || VARCHAR(raw1) );
      ELSE
        CALL DBMS_OUTPUT.PUT_LINE( 'Unexpected value' );
    END CASE;
  END IF;
  SET status = DBMS_PIPE.REMOVE_PIPE( 'pipe1' );
END@

CALL proc1@

CALL proc2@

This example results in the following output:

varchar1: message1