What is Advanced Queuing in Oracle?

There are a number of terms related to "advanced queuing" that you will have to understand to use its functionality:

1. Message: A message is the basic unit of information that is transmitted. You define the information in a message as an object type.
2. Queue: A queue is a repository for messages. You can define any number of individual queues. Messages can be enqueued, or placed, into a queue, and dequeued, or removed, from a queue.
3. Queue table: Queues are stored in queue tables, which are database tables that can contain one or more queues. You can use standard SQL statements to query information in a queue table.
4. Agent: An agent is a queue user. Agents interact with queues. There are two types of agents: a producer, who creates messages for a queue, and a consumer, who takes messages from queues.
5. Recipient and subscription lists: A message can be directed toward more than one recipient, or to a list of subscribers associated with a queue. A queue can have a subscriber list, but you can still direct a message to a particular recipient or recipients explicitly.
6. Rule: A rule is a Boolean expression that acts as a filter for subscribers to a queue. For instance, a message could be directed to different subscribers based on priority or some quality of its message payload.
7. Rule-based subscriber: A rule-based subscriber is a subscriber to a queue who gets messages only based on the evaluation of a rule.
8. Queue monitor: The queue monitor is a background process that performs administrative tasks for queues, such as collecting statistics on a queue or limiting the window of time when a message can be retrieved. The queue monitor is optional.

It is important to note the following 1) updates and 2) shifts in emphasis for Oracle 23c:
✅ Still Supported in Oracle 23c
All of the following remain part of the Oracle AQ infrastructure:

• Message: Still the core unit of communication.
• Queue / Queue Table: Still central to AQ structure.
• Agent (Producer/Consumer): Still relevant in enqueue/dequeue logic.
• Recipient/Subscription lists: Still used in multi-consumer queues.
• Rule / Rule-Based Subscriber: These are still valid and used for content-based routing.
• Queue Monitor (QMNn): Still exists as a background process, though its visibility is minimal in many cloud-managed environments.

🔁 What's Changed in Oracle 23c?
Oracle 23c shifts emphasis toward newer messaging and eventing frameworks, such as:

• Oracle Transactional Event Queues (TxEventQ): a more modern replacement for some AQ use cases, offering Kafka-style semantics and native JSON support.
• JSON payload support and RESTful eventing: enabling event-driven microservices.
• Autonomous Database and cloud environments abstract some of the AQ internals, such as manual queue monitor configurations.

🛑 Clarification No specific AQ term from your list is marked as *deprecated* in Oracle 23c documentation as of this writing. But Oracle has stated that some older, synchronous AQ interfaces and DBMS\_AQ packages may be considered for future deprecation in favor of TxEventQ and REST-based event streaming.

The concept behind Advanced Queuing (also known as AQ) is fairly simple. In a normal interaction with an Oracle database, the communication between a user and the database server is synchronous^[1] when a user sends a message to the Oracle server and waits for a response from the server. Advanced Queuing uses the capabilities of the Oracle server to implement asynchronous communication. A user sends a message to a queue, where it waits until another user or process takes the message out of the queue. The sender is not blocked while waiting for a response from the server, as it is with synchronous communication. The process of communication between the sender and recipient of a message is still guaranteed. The main difference in implementation is that the delivery time is not guaranteed, because it is dependent on when a recipient retrieves a message as well as when the message is sent.

• Environment Setup
  Administration and access privileges for advanced queuing are controled using two roles:
  1. AQ_ADMINISTRATOR_ROLE - Allows creation and administration of queuing infrastructure.
  2. AQ_USER_ROLE - Allows access to queues for enqueue and dequeue operations.
  In the following examples I have used two schemas, one owning the queuing infrastructure and another with access to it.
  

  CONNECT / AS SYSDBA
  CREATE USER aq_admin IDENTIFIED BY aq_admin DEFAULT TABLESPACE users;
  GRANT connect TO aq_admin;
  GRANT create type TO aq_admin;
  GRANT aq_administrator_role TO aq_admin;
  ALTER USER aq_admin QUOTA UNLIMITED ON users;
  
  CREATE USER aq_user IDENTIFIED BY aq_user DEFAULT TABLESPACE users;
  GRANT connect TO aq_user;
  GRANT aq_user_role TO aq_user;
  

Each statement reflects current and valid practices for setting up Oracle Advanced Queuing (AQ) with a separation of responsibilities between an administrative schema (`aq_admin`) and a consuming/producing user (`aq_user`). Here is a breakdown confirming their continued validity:
✅ Analysis of Each SQL Statement

1. CREATE USER ...
   • ✅ Still supported. No changes to user creation syntax in 23c.
2. GRANT connect TO aq_admin;
   • ✅ Valid. While the CONNECT role has changed over versions (it’s now a basic privilege set), it’s still functional and not deprecated.
3. GRANT create type TO aq_admin;
   • ✅ Valid. Required to define object types used in AQ payloads. Still current.
4. GRANT aq_administrator_role TO aq_admin;
   • ✅ Valid and recommended. This role is still the correct way to grant full administrative control over queuing infrastructure.
5. ALTER USER aq_admin QUOTA UNLIMITED ON users;
   • ✅ Valid. Required for persistent queue storage. No change in 23c.
6. GRANT connect TO aq_user;
   • ✅ Valid. Same notes as above; not deprecated.
7. GRANT aq_user_role TO aq_user;
   • ✅ Valid. Still required for enqueuing/dequeuing messages by non-administrators.

While none of these are deprecated, Oracle 23c does encourage the use of:

• JSON-based queuing
• Transactional Event Queues (TxEventQ)
• Multitenant-aware AQ setup using local and common users (if in CDB/PDB)

If you are working in a multitenant or cloud-native environment, you might also consider configuring Application Containers or Pluggable Databases to isolate AQ infrastructure further.
✅ Conclusion: Your SQL is fully valid and not deprecated in Oracle 23c. It aligns with best practices for a traditional AQ configuration using separate schemas for administration and usage.
The following series of images illustrates Advanced Queuing at work:

So, when would you use Advanced Queuing, with its asynchronous communication? First of all, Advanced Queuing would not be appropriate if you are depending on an interaction between the client and the server. If a response is required before the client can move on, there is generally no advantage to asynchronous communication. If you do not need a response from the server before the client can move on, you might want to consider Advanced Queuing. It can be especially useful if an application is requesting a service from another application, and the application receiving the requests may have limited resources available to handle requests. For instance, you might use Advanced Queuing to make a credit-check call as part of an order entry process. The credit-check application may be swamped with requests at any given time, so simply placing the requests into a queue will avoid you having to wait for a response. Of course, this means that the application will either have to check back for the results of the credit check later, or make it clear to the user that any order placed is subject to a credit check before the order can actually be fulfilled.

Modern ava Stored Procedure rewritten using Oracle 23c best practices, based on:

• ✅ Transactional Event Queues (TxEventQ)
• ✅ JSON payloads (object-free, lightweight)
• ✅ JDBC Thin Driver (oracle.jdbc.OracleDriver)
• ✅ Secure, maintainable queue interaction

🔧 Prerequisites (once per schema)
Before running the stored procedure, ensure the following setup has been completed in Oracle 23c:

-- 1. Create a JSON-enabled event queue
BEGIN
  DBMS_AQADM.CREATE_EVENT_QUEUE(
    queue_name => 'credit_alert_queue',
    queue_table => 'credit_alert_qtab',
    payload_type => 'JSON');
END;
/

-- 2. Start the queue
BEGIN
  DBMS_AQADM.START_QUEUE(queue_name => 'credit_alert_queue');
END;
/

☕️ Java Stored Procedure Using TxEventQ and JSON

import java.sql.*;
import oracle.jdbc.*;
import oracle.jms.*;
import javax.jms.*;

public class CreditLimitChecker {

  public static void checkCreditLimit(int customerId, double creditNeeded) throws Exception {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    OracleConnection oconn = null;
    AQjmsSession aqSession = null;
    AQjmsQueue queue = null;
    AQjmsProducer producer = null;

    try {
      // Establish JDBC connection
      conn = DriverManager.getConnection("jdbc:default:connection:");
      oconn = conn.unwrap(OracleConnection.class);

      // Get credit limit from customer table
      ps = conn.prepareStatement(
        "SELECT credit_limit FROM customers WHERE customer_id = ?");
      ps.setInt(1, customerId);
      rs = ps.executeQuery();

      if (!rs.next()) {
        throw new Exception("Customer not found.");
      }

      double creditLimit = rs.getDouble(1);
      double creditGap = creditNeeded - creditLimit;

      if (creditGap > 0) {
        // Create JSON payload
        String jsonPayload = String.format(
          "{\"customerId\": %d, \"creditLimit\": %.2f, \"creditNeeded\": %.2f, \"creditGap\": %.2f}",
          customerId, creditLimit, creditNeeded, creditGap
        );

        // Connect to Transactional Event Queue (TxEventQ)
        AQjmsFactory factory = AQjmsFactory.getInstance();
        aqSession = factory.createQueueConnection(oconn).createQueueSession(true, Session.AUTO_ACKNOWLEDGE);

        queue = aqSession.getQueue(oconn.getSchema(), "credit_alert_queue");
        producer = aqSession.createProducer(queue);

        // Create and send the message
        TextMessage message = aqSession.createTextMessage();
        message.setText(jsonPayload);
        producer.send(message);

        conn.commit(); // Ensure enqueue is committed
      }
    } finally {
      if (rs != null) rs.close();
      if (ps != null) ps.close();
      if (producer != null) producer.close();
      if (aqSession != null) aqSession.close();
    }
  }
}

📌 Key Highlights

Feature	Description
JSON payload	Avoids need for object types or type mapping.
AQ JMS API	Clean interface for TxEventQ via AQjmsFactory.
Transactional commit	Ensures queueing is part of database transaction.
Multitenant-ready	Works in PDBs; queue name is schema-scoped.

In the next lesson, you will learn about the data dictionary views used for Advanced Queuing.

[1] Asynchronous: A process where a command is submitted and the submitter does not wait for a response from the recipient of the command.