AQ Example 1 (PL/SQL): XML Message as a CLOB in an AQ Message

This example shows you how to process an XML message stored in a CLOB in an AQ message. This example is in PL/SQL.

See Also: Oracle8i Application Developer's Guide - Advanced Queuing

Setting Up the AQ Environment

In order to set up the AQ environment, perform the following:

• Grant EXECUTE privileges on the PL/SQL packages DBMS_AQADM and DBMS_AQ

• Create the following tables:
  • Schema

  • Queue tables

  • Queues

AQ Example 1: Tasks Performed

This AQ example performs the following tasks:

1. Creates xml_payload_type to store XML messages in a CLOB

2. Creates AQ Queuetable to hold xml_payload_type messages

3. Creates AQ Queue

4. Adds Subscribers

5. Starts Queue for enqueue and Dequeue

Enqueuing a Message

1. Enqueues an empty CLOB

2. Selects CLOB locator using the enqueued message id

3. Populates the CLOB using DBMS_LOB package

Dequeuing a Message

1. Dequeues the message

2. Uses DBMS_LOB package to read the message

AQ Example 1: The PL/SQL Code

Here is the example:

CONNECT scott/tiger; -- User should have execute privileges on packages DBMS_
AQADM and DBMS_AQ.

-- Create an Oracle Object type to store an XML message

CREATE OR REPLACE TYPE xml_payload_type AS OBJECT (
-- if needed the message can also be parsed to extract some attributes to do 
content-based routing on an AQ queue
	xml_message 	CLOB;
); 

-- CREATE an AQ Queuetable with XML payload
BEGIN
dbms_aqadm.create_queue_table(
queue_table => 'xmlmsg_queuetable',
        sort_list =>'priority,enq_time',
        comment => `demonstrate XML message in an AQ queue',
        multiple_consumers => TRUE,
        queue_payload_type => `xml_payload_type',
        compatible => '8.1');
END;
/

-- CREATE an AQ Queue for XML messages
BEGIN
dbms_aqadm.create_queue (
   queue_name              => `xmlmsg_queue',
   queue_table             => `xmlmsg_queuetable');
END;
/

-- Add an subscriber to XML message queue
BEGIN
dbms_aqadm.add_subscriber(
queue_name => `xmlmsg_queue',
subscriber => `xml_subscriber');
END;
/

-- Start queue for enqueuing and dequeuing
BEGIN
dbms_aqadm.start_queue('xmlmsg_queue');
END;
/

-- Enqueue an XML message
DECLARE
enqopt dbms_aq.enqueue_options_t;
msgprop dbms_aq.message_properties_t;
enq_msgidRAW(40);
sales_order     xml_payload_type;
order_loc       CLOB;
XML_msg         VARCHAR2(200);
msgsize Number;
BEGIN        
sales_order := xml_payload_type (empty_clob());
dbms_aq.enqueue(
   queue_name            => 'xmlmsg_queue', -- IN
   enqueue_options       => enqopt,  -- IN 
   message_properties    => msgprop, -- IN
   payload               => sales_order, -- IN 
   msgid                 => enq_msgid); -- OUT
SELECT t.user_data.xml_message INTO order_loc 
   FROM xmlmsg_queuetable t
   where t.msgid = enq_msgid;

-- Put an XML message in the lob
XML_msg := '<xml> </xml>';
dbms_lob.write(order_loc, 12, 1, XML_msg);

OMMIT;
END;
/


-- Dequeue an XML message
declare
dequeue_options     dbms_aq.dequeue_options_t;
message_properties  dbms_aq.message_properties_t;
message_handle      RAW(16);
message             xml_payload_type;
buffer varchar2(100);
msglen number;
begin
   dequeue_options.consumer_name := 'xml_subscriber';
   dequeue_options.wait          := dbms_aq.NO_WAIT;
   dbms_aq.dequeue(
                    queue_name           => 'xmlmsg_queue',
                    dequeue_options      => dequeue_options,
                    message_properties   => message_properties,
                    payload              => message,
                    msgid                => message_handle);
   commit;
           msglen := dbms_lob.getlength(message.xml_message);
   dbms_lob.read(message.xml_message, msglen, 1,
buffer);
dbms_output.put_line(buffer);
end;
/