CONNECT aq_admin_plsql/aq_admin_plsql

SET SERVEROUTPUT ON


/*
 ** +-----------------------------------+
 ** | DROP ALL OBJECTS                  |
 ** +-----------------------------------+
*/

DROP SEQUENCE message_seq;

EXECUTE dbms_aqadm.stop_queue(queue_name => 'msg_queue');
EXECUTE dbms_aqadm.drop_queue(queue_name => 'msg_queue');
EXECUTE dbms_aqadm.drop_queue_table (queue_table => 'aq_admin_plsql.msg_qt');

DROP TYPE message_type;



/*
 ** +-------------------------------------+
 ** | CREATE SEQUENCE / OBJECT TYPE       |
 ** +-------------------------------------+
*/

CREATE TYPE message_type AS OBJECT (
    message_id     NUMBER(15)
  , subject        VARCHAR2(100)
  , text           VARCHAR2(100)
  , dollar_value   NUMBER(4,2)
)
/

GRANT EXECUTE ON message_type TO aq_user_plsql;

CREATE SEQUENCE message_seq
       INCREMENT BY 1
       START WITH 1000
       NOMAXVALUE
       NOCYCLE;

GRANT select ON message_seq TO aq_user_plsql;


BEGIN

    /*
     ** +----------------------------------------------------------------------------+
     ** | CREATE QUEUE TABLE                                                         |
     ** | -------------------------------------------------------------------------- |
     ** | -> (QUEUE_TABLE)        - Name of the queue table to create.               |
     ** | -> (QUEUE_PAYLOAD_TYPE) - Name of either the object type or RAW.           |
     ** |                                                                            |
     ** | NOTE: The "create_queue_table" procedure will also create the following    |
     ** |       items:                                                               |
     ** |                                                                            |
     ** |    AQ$QUEUE_TABLE_NAME     - A read-only view for information on the queue |
     ** |                              table.                                        |
     ** |    AQ$QUEUE_TABLE_NAME_E   - A default exception queue.                    |
     ** |    AQ$QUEUE_TABLE_NAME_T   - An index for time manager operations.         |
     ** |    AQ$QUEUE_TABLE_NAME_I   - An index or index-organized table to handle   |
     ** |                              dequeueing on queues with multiple consumers. |
     ** |                                                                            |
     ** | ADDITIONAL NOTES:                                                          |
     ** |   - Payload type can be either RAW or as a custom object type.             |
     ** |   - Maximum payload size is 32 KB.                                         |
     ** |   - When a user-defined object type is being used as a payload, the        |
     ** |     maximum number of attributes allowed for the object type is 900.       |
     ** |   - Messages must be in READY state to be dequeued unless a MSGID value is |
     ** |     specified. Messages can be sorted for dequeue based on msgid or        |
     ** |     correlation values.                                                    |
     ** |                                                                            |
     ** +----------------------------------------------------------------------------+
    */

    DBMS_AQADM.CREATE_QUEUE_TABLE (
        queue_table         => 'aq_admin_plsql.msg_qt'
      , queue_payload_type  => 'aq_admin_plsql.message_type'
    );


    /*
     ** +----------------------------------------------------------------------------+
     ** | CREATE QUEUE                                                               |
     ** | -------------------------------------------------------------------------- |
     ** | -> (QUEUE_NAME)     - Name of the queue to create and place in the         |
     ** |                       queue table (below).                                 |
     ** | -> (QUEUE_TABLE)    - Name of the queue table to store queue the (above)   |
     ** |                       named queue in.                                      |
     ** | -> (QUEUE_TYPE)     - Type of queue to create. The types of queues to      |
     ** |                       are NORMAL queues and EXCEPTION queues.              |
     ** | -> (MAX_RETRIES)    - Used to limit the number of times a dequeue with the |
     ** |                       REMOVE mode can be attempted on the message. The     |
     ** |                       default is 0 which allows no retries. When the       |
     ** |                       maximum number of retries is reached, the message is |
     ** |                       moved to the exception queue. The value is           |
     ** |                       incremented when the application issues a rollback   |
     ** |                       after executing the dequeue.                         |
     ** | -> (RETRY_DELAY)    - Specifies the delay time, in seconds, before the     |
     ** |                       message is scheduled for processing again after an   |
     ** |                       application rollback.  The default value is 0, which |
     ** |                       allows a message to be retried as soon as possible.  |
     ** |                       If MAX_RETRIES is set to 0, the RETRY_DELAY argument |
     ** |                       will have no effect.                                 |
     ** | -> (RETENTION_TIME) -                                                      |
     ** | -> (DEPENDENCY_TRACKING) -                                                 |
     ** | -> (COMMENT)        - Assign a comment to the queue.                       |
     ** | -> (AUTO_COMMIT)    - If you set the AUTO_COMMIT argument to 'TRUE', the   |
     ** |                       current transaction, if any, will be committed       |
     ** |                       before the operation is carried out.  This is the    |
     ** |                       default action.  If you set the AUTO_COMMIT argument |
     ** |                       to 'FALSE', the operation will be part of the        |
     ** |                       current transaction and will become persistent only  |
     ** |                       when the user issues a COMMIT.                       |
     ** +----------------------------------------------------------------------------+
    */

    DBMS_AQADM.CREATE_QUEUE (
          queue_name          => 'msg_queue'
        , queue_table         => 'aq_admin_plsql.msg_qt'
        , queue_type          => DBMS_AQADM.NORMAL_QUEUE
        , max_retries         => 0
        , retry_delay         => 0
        , retention_time      => 1209600
        , dependency_tracking => FALSE
        , comment             => 'Test Object Type Queue'
        , auto_commit         => FALSE
    );


    /*
     ** +-------------------------------------+
     ** | START QUEUE                         |
     ** +-------------------------------------+
    */

    DBMS_AQADM.START_QUEUE('msg_queue');


    /*
     ** +-------------------------------------------+
     ** | GRANT ACCESS TO THE QUEUE TO THE AQ USER  |
     ** +-------------------------------------------+
    */

    DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
        privilege     =>     'ALL'
      , queue_name    =>     'aq_admin_plsql.msg_queue'
      , grantee       =>     'aq_user_plsql'
      , grant_option  =>      FALSE
    );

END;
/