Oracle 9i - Advanced Queue handling of collections/XML

From: Alex Vilner <avilner_at_gmail.com>
Date: 8 Oct 2004 07:18:09 -0700
Message-ID: <3a9a58b4.0410080618.31781cca@posting.google.com>

We have a requirement where a Web Logic Server (WLS) needs to send a collection of rows to Oracle 9i database server. The collection should then be enqueued using Advanced Queue mechanism.

Seems that Oracle 9i has a problem handling collection types as payload for the queue:

CREATE TYPE atomic_t AS OBJECT (
  a VARCHAR2(100)
,b NUMBER
,c DATE

);

CREATE TYPE collection_t AS TABLE OF atomic_t;

I can then create a queue table:

DBMS_AQADM.create_queue_table

  (queue_table             => 'MY_QT',
   queue_payload_type      => 'collection_t',
   storage_clause          => 'nested table USER_DATA store as
nt_payload'
              );

and the Queue (Say, MY_Q)

... but ENQUEUE fails with an error:
PLS-00306: wrong number or types of arguments in call to 'ENQUEUE'

DECLARE

   l_msgid		RAW(16);
   l_Message		collection_t;

BEGIN
    ... bulk insert into l_message                 

    DBMS_AQ.ENQUEUE (

        queue_name           	   => 'MY_Q'
       ,payload             	   => l_Message
       ,msgid               	   => l_msgid

    );
END;
/

I think I saw somewhere that collections are not supported for queues in 9i... If they are, please let me know what I am doing wrong.

Alternatively, I can define the queue as transactional (so that messages that are passed in as part of the collection are enqueued together, and are treated as a group). This is NOT preferred, because of the enqueing/dequeuing overhead -- a collection will have up to 250 rows.

I also heard that Oracle has an XML parser of sort. This is the second part of my question -- if WLS, instead of passing a collection, passed in XML, I can enqueue the entire XML string... But on the dequeing side, I will need to parse it out to individual elements. How does Oracle handle XML parsing -- is there a built-in package to do that? What are the performance implications -- will it be faster/slower than using SUBSTR processing (strings are up to 10000 characters in length in theory, up to about 1500 in practice).

Thank you in advance!

--Alex Received on Fri Oct 08 2004 - 09:18:09 CDT