Oracle Advanced Queuing lets programs send messages to each other through the database, with the same transactions, security, and backup as the data. A queue lives in a queue table, holds messages of one payload type, and is created and started with DBMS_AQADM.
Code for This Guide
The main examples are in the examples/pkg-messaging folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_aqadm.create_queue_table(queue_table => 'NAME_QT', queue_payload_type => 'TYPE',
multiple_consumers => false);
dbms_aqadm.create_queue(queue_name => 'NAME_Q', queue_table => 'NAME_QT',
max_retries => n);
dbms_aqadm.start_queue('NAME_Q');
dbms_aqadm.stop_queue('NAME_Q', enqueue => true, dequeue => true);The payload type is an object type, RAW, or JSON. Creating queues needs the AQ_ADMINISTRATOR_ROLE or EXECUTE on DBMS_AQADM.
Create a Queue for Gate Changes
Example:
create type gate_change_t as object (
flight_no varchar2(6),
old_gate varchar2(4),
new_gate varchar2(4)
);
/
begin
dbms_aqadm.create_queue_table(queue_table => 'GATE_QT',
queue_payload_type => 'GATE_CHANGE_T');
dbms_aqadm.create_queue(queue_name => 'GATE_Q', queue_table => 'GATE_QT',
max_retries => 3);
dbms_aqadm.start_queue('GATE_Q');
end;
/
select name, queue_type, max_retries,
trim(enqueue_enabled) as enq, trim(dequeue_enabled) as deq
from user_queues
where queue_table = 'GATE_QT';Output:
Type GATE_CHANGE_T compiled PL/SQL procedure successfully completed. NAME QUEUE_TYPE MAX_RETRIES ENQ DEQ ________________ __________________ ______________ ______ ______ AQ$_GATE_QT_E EXCEPTION_QUEUE 0 NO NO GATE_Q NORMAL_QUEUE 3 YES YES
GATE_CHANGE_T is the message type. The queue table GATE_QT gets its exception queue automatically, AQ$_GATE_QT_E, where messages go when they expire or fail too often. GATE_Q allows three dequeue attempts per message and is started for both enqueue and dequeue.
Stop Enqueuing
STOP_QUEUE can close a queue in one direction only, for example to let consumers empty it before maintenance.
Example:
-- stop only enqueuing: consumers can still empty the queue
exec dbms_aqadm.stop_queue('NOTICE_Q', enqueue => true, dequeue => false)
select name, trim(enqueue_enabled) as enq, trim(dequeue_enabled) as deq
from user_queues where name = 'NOTICE_Q';
declare
v_options dbms_aq.enqueue_options_t;
v_props dbms_aq.message_properties_t;
v_msgid raw(16);
begin
dbms_aq.enqueue('NOTICE_Q', v_options, v_props, notice_t('NM150', 'Boarding'), v_msgid);
end;
/Output:
PL/SQL procedure successfully completed. NAME ENQ DEQ ___________ ______ ______ NOTICE_Q NO YES declare * ERROR at line 1: ORA-25207: enqueue failed, queue NIMBUS.NOTICE_Q is disabled from enqueueing ORA-06512: at "SYS.DBMS_AQ", line 249 ORA-06512: at line 6
With enqueue stopped and dequeue open, a new message fails with ORA-25207. The example drops its queue table and type afterward.
Things to Know
- Drop a queue table with DROP_QUEUE_TABLE and FORCE to remove its queues too; then drop the payload type.
- Set MULTIPLE_CONSUMERS TRUE on the queue table when several subscribers should each get every message.
- For high-throughput event streaming, Transactional Event Queues are the newer queue type.
Related Guides
Conclusion
DBMS_AQADM creates the queue table, the queue, and its exception queue, and starts and stops queues in each direction. Choose the payload type and consumer model first, since both are fixed when the queue table is created.
