Oracle8i Application Developer's Guide - XML Release 3 (8.1.7) Part Number A86030-01 |
Library | Service | Contents | Index

Using Oracle Advanced Queuing (AQ) in XML Data Exchange, 4 of 6

## 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;
    /

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index