How to Run an Oracle Advanced Queuing Application

In the previous lesson, you created the core objects for an Oracle Advanced Queuing (AQ) application: an object type for the message payload, a queue table, and one or more queues. This lesson assumes that the design work is done. Your task now is to run the application, observe how messages move through the queue, and clean up the environment when you are finished testing.

If you need a refresher on AQ concepts such as messages, queues, and queue tables, review the overview page “Oracle Advanced Queues (AQ): Concepts, Benefits, and Architecture”. This lesson focuses strictly on execution and validation, not on AQ theory.

1. Prerequisites and environment

Before you run the application, make sure the following prerequisites are met in your Oracle 23c (or cloud-enabled) environment:

• The AQ object type, queue table, and queue have already been created.
• You have a test schema with privileges to enqueue and dequeue messages.
• The helper scripts START.SQL, STOP.SQL, and AQPROCS.SQL are available in your working directory.
• You can connect using SQL*Plus, SQL Developer, or another tool that can execute SQL and PL/SQL scripts.

The examples in this lesson use a queue named QUEUE1 and a queue table created in earlier steps, but you can adapt the names to match your own environment.

2. Runbook overview: how the AQ application behaves

Running an AQ application follows a consistent pattern:

1. Initialize – Start the queue and load any supporting procedures.
2. Enqueue – Use a wrapper procedure to send one or more messages into the queue.
3. Inspect – Query the queue views and underlying message table to confirm that messages are present.
4. Dequeue – Run a wrapper procedure to consume messages and verify that they are removed or marked as processed.
5. Clean up – Stop and drop the queue objects when you are done with the exercise.

3. Initializing the AQ application

The initialization step prepares the queue so that it can accept messages. In this exercise, you will use a script called START.SQL to perform the setup you completed in the design lessons:

1. Log in to your test schema.
2. Run the starter script:

   
   SQL> @START.SQL
       

3. Verify that the queue is available:

   
   SQL> SELECT name, queue_table, enqueue_enabled, dequeue_enabled
          FROM   user_queues
          WHERE  name = 'QUEUE1';
       

The query against USER_QUEUES should show that QUEUE1 exists and that enqueuing and dequeuing are enabled. If the queue does not appear, review the output from START.SQL to identify any compilation or privilege errors.

4. Enqueuing messages using a wrapper procedure

Although you can call DBMS_AQ.ENQUEUE directly, most applications use a wrapper procedure to hide low-level parameters and enforce a consistent message format. The script AQPROCS.SQL typically creates two procedures, one for enqueue and one for dequeue.

A simplified enqueue wrapper might look like this:

CREATE OR REPLACE PROCEDURE enqueue_order (
    p_order_id   IN NUMBER,
    p_customer   IN VARCHAR2,
    p_amount     IN NUMBER
) AS
  l_message   order_message_t;  -- object type defined earlier
  l_enq_opts  DBMS_AQ.enqueue_options_t;
  l_msg_props DBMS_AQ.message_properties_t;
  l_msgid     RAW(16);
BEGIN
  l_message := order_message_t(p_order_id, p_customer, p_amount);

  DBMS_AQ.ENQUEUE(
    queue_name         => 'QUEUE1',
    enqueue_options    => l_enq_opts,
    message_properties => l_msg_props,
    payload            => l_message,
    msgid              => l_msgid
  );

  COMMIT;
END;

To test the application, call the wrapper with sample values:

SQL> EXEC enqueue_order(1001, 'ACME CORPORATION', 2500);
SQL> EXEC enqueue_order(1002, 'GLOBAL SERVICES',   1800);
SQL> EXEC enqueue_order(1003, 'NORTHWIND TRADERS', 3250);

5. Inspecting the queue contents

Once messages are enqueued, you can verify their presence by querying the underlying AQ table. In many AQ examples, the message table has the name AQ$QUEUE1, but your environment may use a slightly different name depending on how the queue table was created.

SQL> SELECT msgid,
            TO_CHAR(enq_time, 'YYYY-MM-DD HH24:MI:SS') AS enq_time,
            state
       FROM aq$queue1
       ORDER BY enq_time;

You should see one row per message you enqueued. The STATE column typically shows whether the message is ready for dequeue or has already been processed. If no rows appear, verify that:

• The queue name in your wrapper procedure matches the actual queue name.
• The queue is started and enabled for enqueue and dequeue.
• No errors occurred in the enqueue wrapper (check the script output or USER_ERRORS).

6. Dequeuing messages and observing behavior

To complete the round-trip, you will dequeue messages and observe how they are removed from the queue. A simple dequeue wrapper might look like this:

CREATE OR REPLACE PROCEDURE dequeue_order AS
  l_message   order_message_t;
  l_deq_opts  DBMS_AQ.dequeue_options_t;
  l_msg_props DBMS_AQ.message_properties_t;
  l_msgid     RAW(16);
BEGIN
  DBMS_AQ.DEQUEUE(
    queue_name         => 'QUEUE1',
    dequeue_options    => l_deq_opts,
    message_properties => l_msg_props,
    payload            => l_message,
    msgid              => l_msgid
  );

  DBMS_OUTPUT.PUT_LINE(
    'Dequeued order ' || l_message.order_id ||
    ' for customer '  || l_message.customer_name ||
    ' amount '        || l_message.amount
  );

  COMMIT;
END;

Call the dequeue wrapper several times:

SQL> SET SERVEROUTPUT ON
SQL> EXEC dequeue_order;
SQL> EXEC dequeue_order;
SQL> EXEC dequeue_order;

After each call, query the AQ table again to confirm that messages are being removed or marked as processed:

SQL> SELECT msgid, state
       FROM aq$queue1;

When no more ready messages remain, the dequeue procedure may block (depending on the options) or raise an exception. In a production application, you would adjust dequeue options such as wait time and navigation strategy to match your processing requirements.

7. Cleaning up the lab environment

For training purposes, it is useful to return the database to a clean state so that you can repeat the exercise. The STOP.SQL script typically stops the queue, drops it, and drops the queue table.

SQL> @STOP.SQL

Verify that the queue has been removed:

SQL> SELECT name
       FROM   user_queues
       WHERE  name = 'QUEUE1';

If no rows are returned, the cleanup was successful. In real-world 23c deployments, AQ queues are usually permanent objects and are stopped only for maintenance, schema changes, or application upgrades.

8. Common issues and troubleshooting tips

• ORA-24010: QUEUE does not exist – Check that you have run START.SQL and that the queue name in your wrapper matches the name shown in USER_QUEUES.
• Messages not visible in AQ table – Confirm that COMMIT is executed at the end of the enqueue wrapper and that you are querying the correct AQ$ table name.
• Dequeue appears to hang – Review the dequeue options (wait time, navigation) and ensure that messages are in the correct state for dequeue.
• Privilege errors – Make sure the schema that runs the wrappers has execute privileges on the AQ packages and access to the queue and queue table.

9. How this lesson fits into the Advanced Queues module

You can think of this lesson as the runbook for your AQ application. The earlier lessons described what Advanced Queues are and how their components are created. This page showed how to:

• Start the queue environment using START.SQL.
• Run wrapper procedures to enqueue and dequeue messages.
• Verify queue activity with simple SQL queries.
• Clean up the environment using STOP.SQL.

Future lessons in the module build on this foundation by exploring more advanced topics such as AQ application patterns, integration scenarios, and queue propagation between databases. For concept refreshers, return to the Advanced Queues overview page; for hands-on execution, revisit this runbook-style lesson.