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.
