Transactional Event Queues, TxEventQ, are the high-throughput queue type of Oracle Database, built for event streaming. They are partitioned into shards internally, can take JSON payloads directly, and work with the same DBMS_AQ enqueue and dequeue calls as classic queues, as well as with Kafka and JMS clients.
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_transactional_event_queue(
queue_name => 'NAME',
queue_payload_type => 'JSON', -- or RAW, an object type, or JMS
multiple_consumers => true);
dbms_aqadm.add_subscriber(queue_name, sys.aq$_agent('NAME', null, null));
dbms_aqadm.start_queue(queue_name);One call creates the queue and its storage; there is no separate queue table to create.
Publish a Booking Event as JSON
Example:
begin
dbms_aqadm.create_transactional_event_queue(
queue_name => 'BOOKING_EVENTS',
queue_payload_type => 'JSON',
multiple_consumers => true);
dbms_aqadm.add_subscriber('BOOKING_EVENTS', sys.aq$_agent('LOYALTY', null, null));
dbms_aqadm.start_queue('BOOKING_EVENTS');
end;
/
declare
v_enq dbms_aq.enqueue_options_t;
v_deq dbms_aq.dequeue_options_t;
v_props dbms_aq.message_properties_t;
v_doc json;
v_msgid raw(16);
begin
dbms_aq.enqueue('BOOKING_EVENTS', v_enq, v_props,
json('{"event":"booked","ref":"NX7Q2P","miles":1250}'), v_msgid);
commit;
v_deq.consumer_name := 'LOYALTY';
v_deq.wait := 5;
dbms_aq.dequeue('BOOKING_EVENTS', v_deq, v_props, v_doc, v_msgid);
commit;
dbms_output.put_line('LOYALTY received ' || json_serialize(v_doc));
end;
/
select name, queue_type, sharded from user_queues where name = 'BOOKING_EVENTS';Output:
PL/SQL procedure successfully completed.
LOYALTY received {"event":"booked","ref":"NX7Q2P","miles":1250}
PL/SQL procedure successfully completed.
NAME QUEUE_TYPE SHARDED
_________________ _______________ __________
BOOKING_EVENTS NORMAL_QUEUE TRUEBOOKING_EVENTS takes JSON payloads, so the event is enqueued as a JSON value without defining a type. The LOYALTY subscriber dequeues it, waiting up to 5 seconds. USER_QUEUES lists the queue with SHARDED set to TRUE, the mark of a transactional event queue.
Things to Know
- Enqueue and dequeue are transactional, as with classic queues: messages appear on commit and return on rollback.
- Drop the queue with DROP_TRANSACTIONAL_EVENT_QUEUE.
- Use TxEventQ for new event-driven designs; classic queues remain for existing applications.
Cleanup:
exec dbms_aqadm.drop_transactional_event_queue('BOOKING_EVENTS', force => true)Related Guides
Conclusion
Transactional Event Queues are sharded, high-throughput queues created in one call, with JSON payloads and subscribers. Enqueue and dequeue them with DBMS_AQ as usual, inside your transactions.
