DBMS_AQ.ENQUEUE puts a message on a queue. Message properties travel with it: a priority that decides the order, a correlation ID that consumers can filter on, an expiration, and a delay. Like any DML, the message becomes visible to consumers only when the transaction commits.
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_aq.enqueue(queue_name, enqueue_options, message_properties, payload, msgid_out); v_props.priority := n; -- lower numbers first v_props.correlation := 'text'; v_props.expiration := seconds; -- then moved to the exception queue v_props.delay := seconds; -- hidden until then
The example uses the GATE_Q queue and GATE_CHANGE_T type from How to Create Queues with DBMS_AQADM.
Enqueue Two Gate Changes
Example:
declare
v_options dbms_aq.enqueue_options_t;
v_props dbms_aq.message_properties_t;
v_msgid raw(16);
begin
v_props.correlation := 'NM150';
v_props.priority := 1; -- lower numbers are dequeued first
dbms_aq.enqueue('GATE_Q', v_options, v_props, gate_change_t('NM150', 'A1', 'B12'),
v_msgid);
v_props.correlation := 'NM101';
v_props.priority := 5;
v_props.expiration := 3600; -- seconds, then to the exception queue
dbms_aq.enqueue('GATE_Q', v_options, v_props, gate_change_t('NM101', 'C3', 'C5'),
v_msgid);
commit; -- visible to consumers from now on
end;
/
select corr_id, msg_priority, msg_state, q.user_data.flight_no as flight,
q.user_data.new_gate as gate
from aq$gate_qt q
order by msg_priority;Output:
PL/SQL procedure successfully completed. CORR_ID MSG_PRIORITY MSG_STATE FLIGHT GATE __________ _______________ ____________ _________ _______ NM150 1 READY NM150 B12 NM101 5 READY NM101 C5
NM150 is enqueued with priority 1 and NM101 with priority 5 and a one-hour expiration. After the COMMIT both are READY. The AQ$GATE_QT view shows the messages with their properties and payload attributes.
Delay a Message
Example:
declare
v_options dbms_aq.enqueue_options_t;
v_props dbms_aq.message_properties_t;
v_msgid raw(16);
begin
v_props.delay := 600; -- seconds before consumers can see it
dbms_aq.enqueue('NOTICE_Q', v_options, v_props,
notice_t('NM150', 'Final call in 10 minutes'), v_msgid);
commit;
end;
/
select msg_state, q.user_data.text as text,
round((cast(delay as date) - cast(enq_time as date)) * 1440) as minutes_delay
from aq$notice_qt q;Output:
PL/SQL procedure successfully completed. MSG_STATE TEXT MINUTES_DELAY ____________ ___________________________ ________________ WAIT Final call in 10 minutes 10
With a 600-second delay, the message waits in state WAIT and no consumer can dequeue it for ten minutes. The example drops its queue table and type afterward.
Things to Know
- A rollback removes messages enqueued in the transaction, so business data and messages stay consistent.
- Set the visibility option to DBMS_AQ.IMMEDIATE to enqueue in its own transaction instead.
- The message ID returned in MSGID identifies the message for later lookups.
Related Guides
Conclusion
DBMS_AQ.ENQUEUE adds a message with priority, correlation, expiration, and delay properties, visible to consumers once you commit. Enqueue in the same transaction as the data change it describes, so both happen or neither does.
