Getting started with Oracle Advanced Queuing - Hands on example

In this article we will see how to create queues and to enqueue and dequeue messages. In this article I will not discuss what Advanced Queuing is about and why it is used. This is just a step-by-step guide to generate a message and to read it. The agenda of this article is:

 

• Create a queue table
• Create a queue
• Start the queue
• Enqueue the message
• Dequeue the message

 

Create a queue table

--Type to hold the item object

create or replace type aqt_item as object(item_id number, item_description number) ;

--Type to hold order object
create or replace type aqt_order as object(order_id number, ord_name varchar2(60)) ;

exec DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => 'aqt_queue_table', queue_payload_type => 'aqt_order');

 

Now we will see what are the objects created.

 

SELECT queue_table,type,object_type,recipients FROM USER_QUEUE_TABLES;

 

QUEUE_TABLE       TYPE     OBJECT_TYPE        RECIPIENTS

---------------   ----     ------------       -------------------------

AQT_QUEUE_TABLE           OBJECT             SYMSLSP.AQT_ORDER  SINGLE

 

Select object_name from user_objects where object_name like ‘AQ$%’;

 

OBJECT_NAME                OBJECT_TYPE   INDEX ON

--------------------       ------------  -----------

AQ$AQT_QUEUE_TABLE         TABLE

AQ$_AQT_QUEUE_TABLE_E             QUEUE

AQ$_AQT_QUEUE_TABLE_F *    VIEW

AQ$_AQT_QUEUE_TABLE_I             INDEX         AQT_QUEUE_TABLE. TIME_MANAGER_INFO

AQ$_AQT_QUEUE_TABLE_T             INDEX         AQT_QUEUE_TABLE.( Q_NAME

   STATE

   ENQ_TIME

   STEP_NO

   CHAIN_NO

   LOCAL_ORDER_NO)

 

* The view will be created as a JOIN between AQT_QUEUE_TABLE and ALL_DEQUEUE_QUEUES view.

 

Creating Queues

 

exec DBMS_AQADM.CREATE_QUEUE (queue_name => 'aqt_queue', queue_table => 'aqt_queue_table');

 

select name, queue_table, queue_type from user_queues;

 

NAME                 QUEUE_TABLE          QUEUE_TYPE

--------------------- --------------------  ----------------

AQ$_AQT_QUEUE_TABLE_E      AQT_QUEUE_TABLE      EXCEPTION_QUEUE

AQT_QUEUE            AQT_QUEUE_TABLE      NORMAL_QUEUE

 

 

Start the queue

Sql> exec DBMS_AQADM.START_QUEUE('aqt_queue');

Enqueue of message

DECLARE
  enqueue_options    dbms_aq.enqueue_options_t;
  message_properties       dbms_aq.message_properties_t;
  message_handle     RAW(16);
  message            aqt_order;
  message_id         NUMBER;
BEGIN

  Message := aqt_order(10, ‘Pencil’);

 

  enqueue_options.VISIBILITY := DBMS_AQ.ON_COMMIT;
  enqueue_options.SEQUENCE_DEVIATION := null;

 

  message_properties.EXPIRATION := DBMS_AQ.NEVER;

 

  DBMS_AQ.ENQUEUE (
    queue_name => ' aqt_queue ',

    enqueue_options => enqueue_options,

    message_properties => message_properties,

    payload => message,

    msgid => message_handle);

 

  COMMIT;

END;

/

Dequeue of message

 

DECLARE

  dequeue_options        dbms_aq.dequeue_options_t;

  message_properties     dbms_aq.message_properties_t;

  message_handle         RAW(16);

  message                aqt_order;

BEGIN

  dequeue_options.DEQUEUE_MODE := DBMS_AQ.BROWSE;

  DBMS_AQ.DEQUEUE ('aqt_queue', dequeue_options, message_properties, message, message_handle);

 

  Dbms_output.put_line(message. ord_name);

END;

/

 

Output

-------

Pencil

 

 

This concludes our hands-on session on how to create queue tables and queues and getting started with Oracle Advanced Queuing. Await for more articles on this topic.