Advanced Queuing   «Prev  Next»

Lesson 7Developing PL/SQL Applications– Dequeuing
Objective Set up dequeuing from the Advanced Queue.

Developing PL/SQL Applications with Dequeuing

At this point in the example, your message has been sent to the msg_queue queue. The next is to have an agent retrieve the message and remove it from the queue.

Dequeuing Message

The PL/SQL procedure that dequeues a message is essentially a single procedure call, like the PL/SQL procedure for enqueuing a message. The syntax for the call is: 

DECLARE
   dequeue_options dbms_aq.dequeue_options_t;
   message_properties dbms_aq.message_properties_t;
   message_handle RAW(16);
   message PETSTORE.message_typ;

Each of the parameters in this declaration serves the same purpose as the parameters in the previous discussion of enqueuing, with dequeue_options fulfilling the same role as enqueue_options.
In this simplified example, you are once again simply declaring variables that match the type of the parameters needed for the basic function: 
BEGIN
  DBMS_AQ.DEQUEUE(
    queue_name         =>     'queue1',
    dequeue_options    =>    dequeue_options,
    message_properties =>     message_properties,
    payload            =>     message,
    msgid              =>     message_handle);

As with the enqueuing example in the previous lesson, you are executing only the most basic dequeuing operation, so you will not be changing any of the options for enqueuing the message or for the message itself. Different sets of dequeuing options also exist. These options control attributes such as:
  1. How to navigate to a message in the queue
  2. What actions to take on the message in the queue, such as removing it (the default action) or some other type of action, such as simply marking it read
  3. How to lock the retrieved message
At this point, you have retrieved the message, so you would add code to implement logic to handle the payload of the message. For instance, you might use the following line: 

DBMS_OUTPUT.PUT_LINE ('Message: ' || order_msg )

to cause the text of the message to appear on the screen.
After you have implemented the processing actions for the message, you would finish the PL/SQL procedure with the following code: 
COMMIT;
END;

Integrity

One of the great things about Advanced Queuing is that the PL/SQL calls used to implement it can be a part of a larger transaction. In the example used above, the logic needed to process the message could be in the same transaction as the DEQUEUE procedure call. If that logic failed, you could roll back the entire transaction, which would have the effect of canceling the DEQUEUE operation.
In the next lesson, you will learn how to propagate a message to another queue.