Frequently Asked Questions (FAQs): XML and Advanced Queuing

Multiple Format Messages: Create an Object Type and Store as Single Message

Question

We are exchanging XML documents from one business area to another using Oracle Advanced Queuing. Each message received or sent includes an XML header, XML attachment (XML data stream), DTDs, and PDF files. We need to store all this information, including some imagery files, in the database table, in this case, the queuetable.

Can we enqueue this message into an Oracle queue table as one record or one piece? Or do we have to enqueue this message as multiple records, such as one record for XML data streams as CLOB type, one record for PDF files as RAW type, ...

Then somehow specify these set of records are correlated? Also we want to ensure that we dequeue this.

Answer

You can achieve this in the following ways:

• You can either define an object type with (CLOB, RAW,...) attributes, and store it as a single message

• You can use the AQ message grouping feature and store it in multiple messages. But the message properties will be associated with a group. To use message grouping feature, all messages must be the same payload type.

Question 2

Does this mean that we specify the payload type as CLOB first, then enqueue and store all the pieces, XML message data stream, DTDs, and PDF,... as a single message payload in the Queue table? If so, how can we separate this single message into individual pieces when we dequeue this message?

Answer 2

No. You create an object type, for example:

CREATE TYPE mypayload_type as OBJECT (xmlDataStream CLOB, dtd CLOB, pdf BLOB);

Then store it as a single message.

Adding New Recipients After Messages are Enqueued

Question

We want to use the queue table to support message assignments. For example, when other business areas send messages to Oracle, they do not know who should be assigned to process these messages, but they know the messages are for Human Resources (HR). So all messages will go to the HR supervisor.

At this point, the message has been enqueued in the queue table. The HR supervisor is the only recipient of this message, and the entire HR staff have been pre-defined as subscribers for this queue). Can the HR supervisor add new recipients, namely additional staff, to the message_properties.recipient_list on the existing the message in the queue table?

We do not have multiple consumers (recipients) when the messages are enqueued, but we want to replace the old recipient, or add new recipients after the message has already been in the queue table. This new message will then be dequeued by the new recipient. Is this workable? Or do we have to remove the message from old recipient, then enqueue the same message contents to the new recipient?

Answer

You cannot change the recipient list after the message is enqueued. If you do not specify a recipient list then subscribers can subscribe to the queue and dequeue the message.

In your case, the new recipient should be a subscriber to the queue. Otherwise, you will have to dequeue the message and enqueue it again with the new recipient.

XML and AQ

Question

In the OTN document, "Using XML in Oracle Database Applications, Part 4, Exchanging Business Data Among Applications" Nov. 1999, it says that an Oracle database can enqueue and dequeue XML messages and process them. How does it do this?

Do I have to use XML-SQL Utility (XSU) in order to insert an XML file into a table before process it, or can I enqueue an XML file directly, parse it, and dispatch its messages via the AQ process? Must I use XML-SQL Utility every time I want to INSERT or UPDATE XML data into an Oracle Database?

Answer

AQ supports enqueing and dequeing objects. These objects can have an attribute of type CLOB containing an XML Document, as well as other interested "factored out" metadata attributes that might make sense to send along with the message. Refer to the latest AQ document, Oracle8i Application Developer's Guide - Advanced Queuing, to get specific details and see more examples.

Retrieving and Parsing JMS Clients with XML Content From AQ

Question

We need a tool to parse messages, JMS clients with XML content, from an AQ queue and then update tables and fields in an ODS (Operational Data Store). In short, we want to retrieve and parse XML documents and map specific fields to database tables and columns.

Is intermedia Text a solution?

Answer

The easiest way to do this is using Oracle XML Parser for Java and Java Stored Procedures in tandem with AQ inside Oracle8i.

Question 2

We can use XML-SQL Utility if we go with a custom solution. Our main concentration is supply-chain. We want to get metadata information such as, AQ enqueue/dequeue times, JMS header information,.... based on queries on certain XML tag values. Can we just store the XML in a CLOB and issue queries using intermedia Text?

Answer 2

Your question said "parsing" messages, so that threw me off. It also mentioned putting message metadata in regular tables.

• If you store XML as CLOBs then you can definitely search it using interMedia Text, but this only helps you find a particular message that matches a criteria.

• If you need to do aggregation operations over the metadata, view the metadata from existing relational tools, or use normal SQL predicates on the metadata, then having it "only" stored as XML in a CLOB is not going to be good enough.

You can combine interMedia Text XML searching with some amount of redundant metadata storage as "factored out" columns and use SQL statements that combine normal SQL predicates with the interMedia Text CONTAINS() clause to have the best of both.

Ensuring that an Enqueue is Successful?

Question

I am looking for ways to ensure the enqueue is successful. I am deploying AQ on Oracle8i. After successfully executing the enqueue statement in a PL/SQL procedure, I found that the queue had not actually started. Also, there was no error message returned after the enqueue. I have somehow lost the message.

Is there a way to deal with this situation?

Answer

Try the following:

1. After creating the queue, issue the dbms_aqadm.start_queue command.

2. After enqueuing a message, issue a COMMIT.