How to Use Transactional Event Queues in Oracle

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    TRUE

BOOKING_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

• How to Add Queue Subscribers in Oracle
• How to Dequeue Messages with DBMS_AQ.DEQUEUE

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.