Oracle Advanced Queuing - Define Queues and Queue Tables

I am just getting my feet wet working with using single consumer queues in Oracle Advanced Queuing.  This is the BASIC definition for a single consumer queue.

First, you must build an Oracle type to hold the message payload.

create or replace type schema.typ_Order_Message
as object(
           Order_Key       Number(11),
           Customer_Number Number(8)
         );

Second, you need to define a queue table to hold the queue entries.

Begin
    DBMS_AQADM.Create_Queue_Table (
               queue_table        => 'schema.Order_Queue_Table',
               queue_payload_type => 'schema.typ_Order_Message'
                                  );
End;

This will create a queue table called "Order_Queue_Table" in the specified schema.  It will also create some more objects such as:

• An error queue "schema.AQ$_Order_Queue_Table_E" - This is the exception queue where all the messages that get rolled back more than the defined number of times will be moved to.  It is strange that Oracle builds the exception queue at this point and not when you define the actual queue - but it does.
• A view that you can use to query the contents of the queue table, "schema.aq$Order_Queue_Table"

Third, you create the queue itself.

Begin
    DBMS_AQADM.Create_Queue(
             Queue_Name     =>  'schema.Order_Queue',
             Queue_Table    =>  'schema.Order_Queue_Table',
             Queue_Type     =>  SYS.DBMS_AQADM.NORMAL_QUEUE,
             Max_Retries    =>  10,
             Retry_Delay    =>  60,
             Retention_Time =>  0, -- when dequeuing delete it
             Comment        =>  'Comment goes here'
                              );
End;

In this particular example a new queue "Order_Queue" will be created in "schema".

• It will use the queue table "schema.Order_Queue_Table" that was defined in the previous step.
• It is a normal queue table.
• Each message has 10 tries to be consumed.  If the transaction is rolled back more than 10 times the message will be moved to the exception queue.
• If the transaction is rolled back the message will not be available for consumption for 60 seconds.  This lets the application process other messages and then come to try this one again later.
• The message is not kept after it is consumed from the queue.  You can have messages stay for any number of seconds after they are consumed or you can set this to -1 and the messages will never be deleted.
• A comment has been added to the queue

Finally, you need to start the queues.  You start the normal queue for enqueuing and dequeuing but you can only start the exception queue for decueuing.

Begin
    DBMS_AQADM.START_QUEUE(
                           Queue_Name   =>  'schema.Order_Queue',
                           Enqueue      =>  True,
                           Dequeue      =>  True
                              );
    
    DBMS_AQADM.START_QUEUE( 
                           Queue_Name => 'schema.AQ$_Order_Queue_Table_E',
                           Dequeue => True,
                           Enqueue => False 
                           );
End;

Now you are ready to start using the queues.