CONNECT aq_user_plsql/aq_user_plsql

SET SERVEROUTPUT ON

DECLARE

  enqueue_options      dbms_aq.enqueue_options_t;
  message_properties   dbms_aq.message_properties_t;
  -- subscriber           dbms_aq.aq$_recipient_list_t;
  message_handle       RAW(16);
  message              aq_admin_plsql.message_type;
  message_id           NUMBER;

BEGIN

    /*
     ** +----------------------------------------------------------------------------+
     ** | GET NEXT MESSAGE ID                                                        |
     ** +----------------------------------------------------------------------------+
    */
    SELECT aq_admin_plsql.message_seq.nextval INTO message_id FROM dual;


    /*
     ** +----------------------------------------------------------------------------+
     ** | ASSIGN MESSAGE                                                             |
     ** +----------------------------------------------------------------------------+
    */
    message := aq_admin_plsql.message_type(message_id, 'EXAMPLE MESSAGE', 'This is a sample message.', 10.2);


    /*
     ** +----------------------------------------------------------------------------------+
     ** | ENQUEUE OPTIONS                                                                  |
     ** | -------------------------------------------------------------------------------- |
     ** | -> (VISIBILITY)         - Defines transactional behaviour of the queued          |
     ** |                           request. Can be set to:                                |
     ** |                                                                                  |
     ** |                             ON_COMMIT    : The enqueued message is part of the   |
     ** |                                            current transaction and that the      |
     ** |                                            operation will be complete when the   |
     ** |                                            transaction commits. The default      |
     ** |                                            value is "ON_COMMIT".                 |
     ** |                             IMMEDIATE    : the enqueued message is its own       |
     ** |                                            transaction, not part of the current  |
     ** |                                            transaction.                          |
     ** |                                                                                  |
     ** | -> (RELATIVE_MSGID)     - Only relevant when "BEFORE" is used in                 |
     ** |                           "sequence_deviation" (below). This variable            |
     ** |                           defines the message identifier referenced in           |
     ** |                           "sequence_deviation".                                  |
     ** |                                                                                  |
     ** | -> (SEQUENCE_DEVIATION) - Identifies whether the message enqueued should be      |
     ** |                           dequeued before other messages in the queue. Values    |
     ** |                           permitted:                                             |
     ** |                                                                                  |
     ** |                             BEFORE       : This message should be dequeued       |
     ** |                                            before the message defined by         |
     ** |                                            "RELATIVE_MSGID" (above).             |
     ** |                             TOP          : This message is dequeued before any   |
     ** |                                            other messages.                       |
     ** |                             null         : Says this message is dequeued in      |
     ** |                                            regular order. NULL is the default    |
     ** |                                            value.                                |
     ** |                                                                                  |
     ** +----------------------------------------------------------------------------------+
    */
    enqueue_options.VISIBILITY           := DBMS_AQ.ON_COMMIT;
    -- enqueue_options.RELATIVE_MSGID    := '02AB9AD2F4859C5';
    enqueue_options.SEQUENCE_DEVIATION   := null;

    dbms_output.put_line('+-----------------+');
    dbms_output.put_line('| ENQUEUE OPTIONS |');
    dbms_output.put_line('+-----------------+');

    dbms_output.put_line(' -> VISIBILITY          := ' || NVL(enqueue_options.VISIBILITY, -999));
    -- dbms_output.put_line(' -> RELATIVE_MSGID   := ' || NVL(enqueue_options.RELATIVE_MSGID, '<null>'));
    dbms_output.put_line(' -> SEQUENCE_DEVIATION  := ' || NVL(enqueue_options.SEQUENCE_DEVIATION, -999));


    /*
     ** +----------------------------------------------------------------------------------+
     ** | MESSAGE PROPERTIES                                                               |
     ** | -------------------------------------------------------------------------------- |
     ** | -> (PRIORITY)         - Specifies the prioriy of the message numerically. Both   |
     ** |                         negatives and positives are allowed; the lower the       |
     ** |                         number, the higher the priority.                         |
     ** |                                                                                  |
     ** | -> (DELAY)            - Identifies a delay, in seconds, during which time the    |
     ** |                         message may not be dequeued. Alternately, "NO_DELAY"     |
     ** |                         may be specified for this variable. It relies on the     |
     ** |                         setting of the time manager.                             |
     ** |                                                                                  |
     ** | -> (EXPIRATION)       - Defines how long the message is available for            |
     ** |                         dequeueing, in seconds, after which time the message     |
     ** |                         expires. Alternately, "NEVER" may be specified for       |
     ** |                         this variable.                                           |
     ** |                                                                                  |
     ** | -> (CORRELATION)      - Identifies the message with a name.                      |
     ** |                                                                                  |
     ** | -> (ATTEMPTS)         - Number of times other consumers attempted to             |
     ** |                         dequeue() the message. This is not set at time of        |
     ** |                         enqueue().                                               |
     ** |                                                                                  |
     ** | -> (RECIPIENT_LIST)   - Can be used only for queues allowing multiple consumers. |
     ** |                         Default recipients are the subscribers to the queue.     |
     ** |                         Values for this variable cannot be returned in a         |
     ** |                         dequeue(). The recipient list can be defined with        |
     ** |                         variable, of type SYS.AQ$_AGENT, which takes three       |
     ** |                         variables: name, address and protocol, of datatypes      |
     ** |                         VARCHAR2, VARCHAR2 and NUMBER, respectively.             |
     ** |                                                                                  |
     ** | -> (EXCEPTION_QUEUE)  - Messages moved to the exception queue after value for    |
     ** |                         "expire" has passed, or if "attempts" exceeded the       |
     ** |                         maximum number of attempts allowed for the queue.        |
     ** |                                                                                  |
     ** | -> (ENQUEUE_TIME)     - Set internally by the system as the time "enqueue()"     |
     ** |                         deposited the message.                                   |
     ** |                                                                                  |
     ** | -> (STATE)            - The current state of the message. This has four          |
     ** |                         possible values:                                         |
     ** |                                                                                  |
     ** |                           WAITING    - If the message is still in delay.         |
     ** |                           READY      - If the message can be obtained via        |
     ** |                                        dequeue().                                |
     ** |                           PROCESSED  - If the message is processed and retained. |
     ** |                           EXPIRED    - If the message moved to the location      |
     ** |                                        defined by exception queue.               |
     ** |                                                                                  |
     ** +----------------------------------------------------------------------------------+
    */
    message_properties.PRIORITY            := -5;
    message_properties.DELAY               := DBMS_AQ.NO_DELAY;
    message_properties.EXPIRATION          := DBMS_AQ.NEVER;
    message_properties.CORRELATION         := 'TEST MESSAGE';
    -- message_properties.ATTEMPTS         := (Not set at time of enqueue);
    --   subscriber(1)                       := SYS.AQ$_AGENT('JEFF', null, null);
    --   subscriber(2)                       := SYS.AQ$_AGENT('MELODY', null, null);
    --   subscriber(3)                       := SYS.AQ$_AGENT('ALEX', null, null);
    -- message_properties.RECIPIENT_LIST   := subscriber;
    -- message_properties.EXCEPTION_QUEUE  := 'AQ$MSG_QT_E';
    -- message_properties.ENQUEUE_TIME     := (Not set by user);
    -- message_properties.STATE            := (Not set by user);


    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         := ' || '(Not set at time of enqueue)' );
    dbms_output.put_line(' -> EXCEPTION_QUEUE  := ' || NVL(message_properties.EXCEPTION_QUEUE, -999));
    dbms_output.put_line(' -> ENQUEUE_TIME     := ' || '(Not set by user)' );
    dbms_output.put_line(' -> STATE            := ' || '(Not set by user)' );

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

  COMMIT;

END;
/