Using PL/SQL to Create an Oracle Advanced Queuing Application

In earlier lessons you learned what Oracle Advanced Queues (AQ) are and how they fit into an Oracle 23c–based architecture. This lesson assumes that background knowledge and focuses on a single, practical objective:

Use PL/SQL to create the core objects for an Advanced Queuing application.

You will build the foundation of a small order-processing application. Later lessons in the module will show how to enqueue and dequeue messages and how to run and observe the application. Here, we concentrate on the setup phase: defining the message payload type, creating the queue table, creating the queue, and starting it so that it can accept messages.

Imagine a pet store that receives orders from several channels: in-store, web, and remote sales representatives. The business wants to:

• Capture orders immediately when they are placed.
• Process billing and fulfillment asynchronously in the background.
• Optionally send order notifications to a supplier database.

Advanced Queues are a good fit for this scenario. Each new order is turned into a message and placed on a queue. Separate processes can subscribe to the queue to perform billing, inventory checks, or downstream integration. In this lesson, you will build the basic queue infrastructure in a schema called PETSTORE.

The first step is to decide what information each message needs to carry. For this introductory example, we will keep the payload simple—a short text description of the order. In a real system, you might use a more complex object type or a JSON structure.

Create an object type in the PETSTORE schema to represent the message payload:

CREATE TYPE petstore_message_t AS OBJECT (
  order_msg  VARCHAR2(100)
);

This type defines a single attribute, order_msg, that will store a brief description of the order. The type will be referenced by the queue table definition in the next step.

A queue table is the physical storage location for messages. You create it with the DBMS_AQADM.CREATE_QUEUE_TABLE procedure and associate it with the payload type you just defined.

The following PL/SQL call creates a queue table named QUEUE1_TAB in the PETSTORE schema:

BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    queue_table        => 'PETSTORE.QUEUE1_TAB',
    queue_payload_type => 'PETSTORE.PETSTORE_MESSAGE_T'
  );
END;
/

Key points about this step:

• All queues that share this table must use the same payload type.
• The queue table is a regular Oracle table under the covers, but its structure is managed by AQ.
• In production, you would also consider tablespace placement, partitioning options, and retention policies for the queue table, which are beyond the scope of this introductory lesson.

With the queue table in place, you can define a logical queue that will hold the messages for your pet store orders. Use the DBMS_AQADM.CREATE_QUEUE procedure:

BEGIN
  DBMS_AQADM.CREATE_QUEUE(
    queue_name  => 'PETSTORE.QUEUE1',
    queue_table => 'PETSTORE.QUEUE1_TAB'
  );
END;
/

This statement creates a single queue named PETSTORE.QUEUE1 backed by the QUEUE1_TAB queue table. Later, additional queues could be created on the same table if they share the same payload type.

A newly created queue is not ready to accept messages until it is started. To start the queue, call DBMS_AQADM.START_QUEUE:

BEGIN
  DBMS_AQADM.START_QUEUE(
    queue_name => 'PETSTORE.QUEUE1'
  );
END;
/

After this call completes successfully, PETSTORE.QUEUE1 is available for enqueuing and dequeuing messages. In a cloud-enabled Oracle 23c environment, this step is typically performed as part of a deployment script or automated provisioning process.

Although the details of monitoring AQ are covered in a separate lesson, it is useful to perform a quick verification step as soon as the objects are created. Use the AQ-related dictionary views to confirm that the queue table and queue exist and are enabled:

-- Verify the queue table
SELECT queue_table, object_type
  FROM   user_queue_tables
 WHERE  queue_table = 'QUEUE1_TAB';

-- Verify the queue
SELECT name, queue_table, enqueue_enabled, dequeue_enabled
  FROM   user_queues
 WHERE  name = 'QUEUE1';

Successful queries confirm that your PL/SQL calls created the infrastructure correctly. The lessons dedicated to data dictionary views will explore these catalog tables in more depth; here they are used only for a quick sanity check.

Advanced Queues in Oracle 23c support many capabilities beyond the simple single-queue example used here. To keep this lesson focused on the creation of an AQ application, the following topics are mentioned only briefly and are treated in more detail elsewhere in the course or in Oracle documentation:

• Navigating through messages with different dequeue options.
• Prioritizing messages and grouping them into related sets.
• Multi-consumer queues and subscriber lists.
• Rule-based subscriptions that route messages based on message content.
• Expiration times and exception queues for messages that cannot be processed.
• Security and privilege management on queues and queue tables.

These features become important as your AQ applications grow more sophisticated, but they are not required to complete the introductory order-processing scenario.

Many learners find it helpful to walk through the setup in an interactive tool such as SQL Developer or SQL*Plus. The following outline summarizes the actions you would take in such a simulation:

1. Create the message type.
   Enter the CREATE TYPE statement for PETSTORE_MESSAGE_T and execute it. Confirm that the command completes successfully.
2. Create the queue table.
   Execute the DBMS_AQADM.CREATE_QUEUE_TABLE block, supplying the queue table name PETSTORE.QUEUE1_TAB and the payload type PETSTORE.PETSTORE_MESSAGE_T.
3. Create the queue.
   Run the DBMS_AQADM.CREATE_QUEUE block to define PETSTORE.QUEUE1 on the queue table you created in the previous step.
4. Start the queue.
   Execute DBMS_AQADM.START_QUEUE for PETSTORE.QUEUE1 so that it can accept messages.
5. Confirm the setup.
   Query USER_QUEUE_TABLES and USER_QUEUES to verify that the objects exist and are enabled. At this point, the queue is ready for the enqueue and dequeue code that will be developed in the next lessons.

In a real-world project, you would package these steps into deployment scripts so that new environments can be provisioned consistently. The essential idea is the same: define the payload type, create the queue table, create the queue, and start it.

In this lesson, you used PL/SQL and AQ administration packages to create the core objects for an Advanced Queuing application:

• A user-defined object type for the message payload.
• A queue table to store messages of that type.
• A queue associated with the queue table.
• A start operation that enables the queue to accept messages.

You now have a working foundation for the pet store order-processing application. The queue is defined and ready; the next lessons in Module 6 will show how to:

• Write PL/SQL code to enqueue messages into the queue.
• Write PL/SQL code to dequeue and process those messages.
• Run the completed application and observe how messages flow through the system.

By keeping this page focused on creation and setup, and deferring execution, propagation, and high-level summaries to other lessons, we avoid content overlap and keep each module page aligned with a single, clearly defined objective.