CONNECT aq_user_plsql/aq_user_plsql

SET SERVEROUTPUT ON

DECLARE

  dequeue_options      dbms_aq.dequeue_options_t;
  message_properties   dbms_aq.message_properties_t;
  message_handle       RAW(16);
  message              aq_admin_plsql.message_type;

BEGIN


    /*
     ** +----------------------------------------------------------------------------------+
     ** | DEQUEUE OPTIONS                                                                  |
     ** | -------------------------------------------------------------------------------- |
     ** | -> (CONSUMER_NAME)  - Name of the application, process or user receiving the     |
     ** |                       message. Should be NULL for queues not set up to handle    |
     ** |                       more than one consumer.                                    |
     ** | -> (DEQUEUE_MODE)   - Specifies locks, if any, to be acquired on the message by  |
     ** |                       the dequeue() process. Can be:                             |
     ** |                                                                                  |
     ** |                         BROWSE :  For "read-only" access similar to that used in |
     ** |                                   "select" statements.                           |
     ** |                         LOCKED :  For the ability to write to the message during |
     ** |                                   the transaction, similar to a share lock       |
     ** |                                   acquired in a "select for update" statement.   |
     ** |                         REMOVE :  For the ability to read the message,           |
     ** |                                   updating it or deleting it. The message is     |
     ** |                                   retained according to properties set in queue  |
     ** |                                   table creation.                                |
     ** |                                                                                  |
     ** | -> (NAVIGATION)     - Determines the position of the message to be retrieved,    |
     ** |                       the first step in retrieving messages. The second step is  |
     ** |                       applying search criteria. The navigation variable can have |
     ** |                       one of three values:                                       |
     ** |                                                                                  |
     ** |                         NEXT_MESSAGE     : Is used for retrieving the next       |
     ** |                                            message available that matches search |
     ** |                                            criteria.                             |
     ** |                         NEXT_TRANSACTION : Is used to skip remaining messages in |
     ** |                                            the current transaction group and     |
     ** |                                            retrieve the first message of the     |
     ** |                                            next transaction group.               |
     ** |                         FIRST_MESSAGE    : Used to retrieve the first message    |
     ** |                                            that fits the search criteria,        |
     ** |                                            resetting the position to the         |
     ** |                                            beginning of queue.                   |
     ** | -> (VISIBILITY)     - Defines visibility of the message within the transaction   |
     ** |                       of the application dequeueing it. Values are:              |
     ** |                                                                                  |
     ** |                         ON_COMMIT        : If the message dequeued is part of    |
     ** |                                            the current transaction.              |
     ** |                         IMMEDIATE        : If the message is its own transaction.|
     ** |                                                                                  |
     ** | -> (WAIT)           - Specifies how long to wait if an attempt is made to        |
     ** |                       enqueue() a message and there is no message to retrieve.   |
     ** |                       Values are:                                                |
     ** |                                                                                  |
     ** |                         FOREVER          : Wait forever.                         |
     ** |                         NO_WAIT          : Do not wait for any message.          |
     ** |                         num              : Number that represents the number of  |
     ** |                                            seconds it will wait.                 |
     ** |                                                                                  |
     ** | -> (MSGID)          - The message identifier for the message to be dequeued. If  |
     ** |                       specified, the message will be dequeued even if expired.   |
     ** | -> (CORRELATION)    - The name of the message to be dequeued.                    |
     ** |                                                                                  |
     ** +----------------------------------------------------------------------------------+
    */

    dequeue_options.CONSUMER_NAME           := NULL;
    dequeue_options.DEQUEUE_MODE            := DBMS_AQ.REMOVE;
    dequeue_options.NAVIGATION              := DBMS_AQ.NEXT_MESSAGE;
    dequeue_options.VISIBILITY              := DBMS_AQ.IMMEDIATE;
    dequeue_options.WAIT                    := DBMS_AQ.FOREVER;
    dequeue_options.MSGID                   := null;
    dequeue_options.CORRELATION             := 'TEST MESSAGE';


    dbms_output.put_line('+-----------------+');
    dbms_output.put_line('| DEQUEUE OPTIONS |');
    dbms_output.put_line('+-----------------+');
    dbms_output.put_line(' -> CONSUMER_NAME     := ' || NVL(dequeue_options.CONSUMER_NAME, -999));
    dbms_output.put_line(' -> DEQUEUE_MODE      := ' || NVL(dequeue_options.DEQUEUE_MODE, -999));
    dbms_output.put_line(' -> NAVIGATION        := ' || NVL(dequeue_options.NAVIGATION, -999));
    dbms_output.put_line(' -> VISIBILITY        := ' || NVL(dequeue_options.VISIBILITY, -999));
    dbms_output.put_line(' -> WAIT              := ' || NVL(dequeue_options.WAIT, -999));
    dbms_output.put_line(' -> MSGID             := ' || NVL(dequeue_options.MSGID, '<null>'));
    dbms_output.put_line(' -> CORRELATION       := ' || NVL(dequeue_options.CORRELATION, '<null>'));


    /*
     ** +------------------------------------+
     ** | PRINT MESSAGE PROPERTIES           |
     ** +------------------------------------+
    */
    dbms_output.put_line('+--------------------+');
    dbms_output.put_line('| MESSAGE PROPERTIES |');
    dbms_output.put_line('+--------------------+');
    dbms_output.put_line(' -> PRIORITY         := ' || NVL(message_properties.PRIORITY, -999));
    dbms_output.put_line(' -> DELAY            := ' || NVL(message_properties.DELAY, -999));
    dbms_output.put_line(' -> EXPIRATION       := ' || NVL(message_properties.EXPIRATION, -999));
    dbms_output.put_line(' -> CORRELATION      := ' || NVL(message_properties.CORRELATION, -999));
    dbms_output.put_line(' -> ATTEMPTS         := ' || NVL(message_properties.ATTEMPTS, -999));
    dbms_output.put_line(' -> EXCEPTION_QUEUE  := ' || NVL(message_properties.EXCEPTION_QUEUE, -999));
    dbms_output.put_line(' -> ENQUEUE_TIME     := ' || message_properties.ENQUEUE_TIME);
    dbms_output.put_line(' -> STATE            := ' || NVL(message_properties.STATE, -999));


    /*
     ** +------------------------------------+
     ** | DEQUEUE THE MESSAGE                |
     ** +------------------------------------+
    */
    DBMS_AQ.DEQUEUE (
        queue_name         => 'aq_admin_plsql.msg_queue'
      , dequeue_options    => dequeue_options
      , message_properties => message_properties
      , payload            => message
      , msgid              => message_handle
    );


    /*
     ** +------------------------------------+
     ** | PRINT THE DEQUEUED MESSAGE PAYLOAD |
     ** +------------------------------------+
    */
    dbms_output.put_line('+-----------------+');
    dbms_output.put_line('| MESSAGE PAYLOAD |');
    dbms_output.put_line('+-----------------+');
    dbms_output.put_line('- Message ID   := ' || message.message_id);
    dbms_output.put_line('- Subject      := ' || message.subject);
    dbms_output.put_line('- Message      := ' || message.text);
    dbms_output.put_line('- Dollar Value := ' || message.dollar_value);

  COMMIT;

END;
/