|Oracle8i Application Developer's Guide - XML
Release 3 (8.1.7)
Part Number A86030-01
Using Oracle Advanced Queuing (AQ) in XML Data Exchange, 6 of 6
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.
You can achieve this in the following ways:
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?
No. You create an object type, for example:
Then store it as a single message.
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?
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.
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?
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.
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?
The easiest way to do this is using Oracle XML Parser for Java and Java Stored Procedures in tandem with AQ inside Oracle8i.
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?
Your question said "parsing" messages, so that threw me off. It also mentioned putting message metadata in regular tables.
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.
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?
Try the following: