Skip Headers

Oracle9i XML Database Developer's Guide - Oracle XML DB
Release 2 (9.2)

Part Number A96620-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF

Exchanging XML Data Using Advanced Queueing (AQ) and Oracle Streams

This chapter describes how XML data can be exchanged using Oracle Advanced Queueing. It contains the following sections:

What Is AQ?

Oracle Advanced Queuing (AQ) provides database integrated message queuing functionality. AQ:

Integration of message queuing with Oracle9i database brings the integrity, reliability, recoverability, scalability, performance, and security features of Oracle9i to message queuing. Integration with Oracle9i also facilitates the extraction of intelligence from message flows.

How Do AQ and XML Complement Each Other?

XML has emerged as a standard format for business communications. XML is being used not only to represent data communicated between business applications, but also, the business logic that is encapsulated in the XML.

In Oracle9i, AQ supports native XML messages and also allows AQ operations to be defined in the XML-based Internet-Data-Access-Presentation (IDAP) format. IDAP, an extensible message invocation protocol, is built on Internet standards, using HTTP and email protocols as the transport mechanism, and XML as the language for data presentation. Clients can access AQ using this.

AQ and XML Message Payloads

Figure 24-1 shows an Oracle9i database using AQ to communicate with three applications, with XML as the message payload. The general tasks performed by AQ in this scenario are:

This is an intra- and inter-business scenario where XML messages are passed asynchronously among applications using AQ.

Figure 24-1 shows XML payload messages transported using AQ in the following ways:

The figure also shows that AQ clients can access data using OCI, Java, or PL/SQL.

Figure 24-1 Advanced Queuing and XML Message Payloads

Text description of adque437.gif follows
Text description of the illustration adque437.gif

AQ Enables Hub-and-Spoke Architecture for Application Integration

A critical challenge facing enterprises today is application integration. Application integration involves getting multiple departmental applications to cooperate, coordinate, and synchronize in order to execute complex business transactions.

AQ enables hub-and-spoke architecture for application integration. It makes integrated solution easy to manage, easy to configure, and easy to modify with changing business needs.

Messages Can Be Retained for Auditing, Tracking, and Mining

Message management provided by AQ is not only used to manage the flow of messages between different applications, but also, messages can be retained for future auditing and tracking, and extracting business intelligence.

Viewing Message Content with SQL Views

AQ also provides SQL views to look at the messages. These SQL views can be used to analyze the past, current, and future trends in the system.

Advantages of Using AQ

AQ provides the flexibility of configuring communication between different applications.

Oracle Streams and AQ

Oracle Streams (Streams) enables you to share data and events in a stream. The stream can propagate this information within a database or from one database to another. The stream routes specified information to specified destinations. This provides greater functionality and flexibility than traditional solutions for capturing and managing events, and sharing the events with other databases and applications.

Streams enables you to break the cycle of trading off one solution for another. It enable you to build and operate distributed enterprises and applications, data warehouses, and high availability solutions. You can use all the capabilities of Oracle Streams at the same time.

You can use Streams to:

Streams Message Queuing

Streams allows user applications to:

Streams introduces a new type of queue that stages messages of SYS.AnyData type. Messages of almost any type can be wrapped in a SYS.AnyData wrapper and staged in SYS.AnyData queues. Streams interoperates with Advanced Queuing (AQ), which supports all the standard features of message queuing systems, including multiconsumer queues, publishing and subscribing, content-based routing, internet propagation, transformations, and gateways to other messaging subsystems.

See Also:

Oracle9i Streams, and its Appendix, Appendix A, "XML Schema for LCRs".

XMLType Attributes in Object Types

You can now create queues that use Oracle object types containing attributes of the new, opaque type, XMLType. These queues can be used to transmit and store messages that are XML documents. Using XMLType, you can do the following:

Internet Data Access Presentation (IDAP)

You can access AQ over the Internet by using Simple Object Access Protocol (SOAP). Internet Data Access Presentation (IDAP) is the SOAP specification for AQ operations. IDAP defines XML message structure for the body of the SOAP request. An IDAP-structured message is transmitted over the Internet using transport protocols such as HTTP or SMTP.

IDAP uses the text/xml content type to specify the body of the SOAP request. XML provides the presentation for IDAP request and response messages as follows:

IDAP Architecture

Figure 24-2 shows the following components needed to send HTTP messages:

Figure 24-2 IDAP Architecture for Performing AQ Operations Using HTTP

Text description of adxml101.gif follows
Text description of the illustration adxml101.gif

XMLType Queue Payloads

You can create queues with payloads that contain XMLType attributes. These can be used for transmitting and storing messages that contain XML documents. By defining Oracle objects with XMLType attributes, you can do the following:

Example 24-1 Using AQ and XMLType Queue Payloads: Creating the OverSeas Shipping Queue Table and Queue and Transforming the Representation

In the BooksOnline application, assume that the Overseas Shipping site represents the order as SYS.XMLType. The Order Entry site represents the order as an Oracle object, ORDER_TYP.

The Overseas queue table and queue are created as follows:

   queue_table        => 'OS_orders_pr_mqtab',            
   comment            => 'Overseas Shipping MultiConsumer Orders queue table', 
   multiple_consumers => TRUE,                             
   queue_payload_type => 'SYS.XMLTtype',                       
   compatible         => '8.1');

dbms_aqadm.create_queue (                                   
   queue_name   => 'OS_bookedorders_que',        
   queue_table  => 'OS_orders_pr_mqtab');

Since the representation of orders at the Overseas Shipping site is different from the representation of orders at the Order Entry site, a transformation is applied before messages are propagated from the Order Entry site to the Overseas Shipping site.

/*  Add a rule-based subscriber (for Overseas Shipping) to the Booked orders 
queues with Transformation. Overseas Shipping handles all non-US orders: */ 
  subscriber     aq$_agent; 
  subscriber := aq$_agent('Overseas_Shipping','OS.OS_bookedorders_que',null); 

   queue_name     => 'OE.OE_bookedorders_que', 
   subscriber     => subscriber, 
   rule           => 'tab.user_data.orderregion = ''INTERNATIONAL''',
   transformation => 'OS.OE2XML'); 

For more details on defining transformations that convert the type used by the Order Entry application to the type used by Overseas shipping, see Oracle9i Application Developer's Guide - Advanced Queuing the section on Creating Transformations in Chapter 8.

Example 24-2 Using AQ and XMLType Queue Payloads: Dequeuing Messages

Assume that an application processes orders for customers in Canada. This application can dequeue messages using the following procedure:

/*  Create procedures to enqueue into single-consumer queues: */ 
create or replace procedure get_canada_orders() as 
deq_msgid                RAW(16); 
dopt                     dbms_aq.dequeue_options_t; 
mprop                    dbms_aq.message_properties_t; 
deq_order_data           SYS.XMLTtype; 
no_messages              exception; 
pragma exception_init    (no_messages, -25228); 
new_orders               BOOLEAN := TRUE; 
        dopt.wait := 1;

/* Specify dequeue condition to select Orders for Canada */ 
        dopt.deq_condition := 'tab.user_data.extract(
''/ORDER_TYP/CUSTOMER/COUNTRY/text()'').getStringVal()=''CANADA'''; dopt.consumer_name : = 'Overseas_Shipping'; WHILE (new_orders) LOOP BEGIN dbms_aq.dequeue( queue_name => 'OS.OS_bookedorders_que', dequeue_options => dopt, message_properties => mprop, payload => deq_order_data, msgid => deq_msgid); commit; dbms_output.put_line(' Order for Canada - Order: ' || deq_order_data.getStringVal()); EXCEPTION WHEN no_messages THEN dbms_output.put_line (' ---- NO MORE ORDERS ---- '); new_orders := FALSE; END; END LOOP; end;

Enqueue Using AQ XML Servlet

You can perform enqueue requests over the Internet using IDAP.

See Also:

Oracle9i Application Developer's Guide - Advanced Queuing for further information about sending AQ requests using IDAP.


In the BooksOnLine application, the Order Entry system uses a priority queue to store booked orders. Booked orders are propagated to the regional booked orders queues. At each region, orders in these regional booked orders queues are processed in the order of the shipping priorities.

Example 24-3 PL/SQL (DBMS_AQADM Package)

The following calls create the priority queues for the Order Entry application.

/* Create a priority queue table for OE: */ 
EXECUTE dbms_aqadm.create_queue_table( \                        
   queue_table         => 'OE_orders_pr_mqtab', \          
   sort_list           =>'priority,enq_time', \ 
   comment             => 'Order Entry Priority  \
                          MultiConsumer Orders queue table',\ 
   multiple_consumers  => TRUE, \                           
   queue_payload_type  => 'BOLADM.order_typ', \                      
   compatible          => '8.1', \                                  
   primary_instance    => 2, \                                       
   secondary_instance  => 1); 
EXECUTE dbms_aqadm.create_queue ( \                                
   queue_name          => 'OE_bookedorders_que', \       
   queue_table         => 'OE_orders_pr_mqtab'); 

Assume that a customer, John, wants to send an enqueue request using SOAP. The XML message will have the following format.

<?xml version="1.0"?>
   <Envelope xmlns= "">
        <AQXmlSend xmlns = "">
                        <NAME>JOHN DASH</NAME>
                        <STREET>100 EXPRESS STREET</STREET>
                        <CITY>REDWOOD CITY</CITY>
                              <TITLE>Perl handbook</TITLE>
                              <TITLE>JDBC guide</TITLE>
                     <ORDER_DATE>08/23/2000 12:45:00</ORDER_DATE>

Dequeue Using AQ XML Servlet

You can perform dequeue requests over the Internet using SOAP.

See Also:

Oracle9i Application Developer's Guide - Advanced Queuing for information about receiving AQ messages using SOAP.

In the BooksOnline scenario, assume that the East shipping application receives AQ messages with a correlation identifier 'RUSH' over the Internet.

Example 24-4 Receiving and Dequeuing AQ XML Messages

The dequeue request will have the following format:

<?xml version="1.0"?>
<Envelope xmlns= "">
        <AQXmlReceive xmlns = "">



IDAP and AQ XML Schemas

IDAP exposes a SOAP and AQ XML schema to the client. All documents sent by the parser are validated against these schemas:

Frequently Asked Questions (FAQs): XML and Advanced Queuing

Can I Store AQ XML Messages with Many PDFs as One Record?

I am 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. I need to store all this information, including some imagery files, in the database table, in this case, the queuetable.

Can I enqueue this message into an Oracle queue table as one record or one piece? Or do I 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 that these sets of records are correlated? Also, I want to ensure that I dequeue this.

Answer: You can achieve this in the following ways:

Do I Specify Payload Type as CLOB First, Then Enqueue and Store?

[Follow on question from preceding FAQ] Do I 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 I separate this single message into individual pieces when I dequeue this message?

Answer: 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.

Can I Add New Recipients After Messages Are Enqueued?

I 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?

I do not have multiple consumers (recipients) when the messages are enqueued, but I 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 I 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.

How Does Oracle Enqueue and Dequeue and Process XML Messages?

In an OTN document, 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 processing it, or can I enqueue an XML file directly, parse it, and dispatch its messages through the AQ process? Must I use XML SQL Utility every time I want to INSERT or UPDATE XML data into Oracle9i Database?

Answer: AQ supports enqueing and dequeing objects. These objects can have an attribute of type XMLType 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, Oracle9i Application Developer's Guide - Advanced Queuing to get specific details and see more examples.

How Can I Parse Messages with XML Content from AQ Queues?

I need a tool to parse messages with XML content, from an AQ queue and then update tables and fields in an ODS (Operational Data Store). In short, I want to retrieve and parse XML documents and map specific fields to database tables and columns. Is Oracle9i Text a solution?

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

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

Regarding the use of XSU:

You can combine Oracle9i 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 Oracle9i Text CONTAINS() clause to have the best of both.

See Also:

Chapter 7, "Searching XML Data with Oracle Text".

Can I Prevent the Listener from Stopping Until the XML Document Is Processed?

I receive XML messages from clients as messages and need to process them as soon as they come in. Each XML document takes about 15 seconds to process. I am using PL/SQL. One PL/SQL procedure starts the listener and dequeues the message and calls another procedure to process the XML document. The problem is that the listener is held up until the XML document is processed. Meanwhile messages accumulate in the queue.

What is the best way to handle this? Is there a way for the listener program to call the XML processing procedure asynchronously and return to listening? Java is not an option at this point.

Answer: After receiving the message, you can submit a job using the DBMS_JOB package. The job will be invoked asynchronously in a different database session.

Oracle9i has added PL/SQL callbacks in the AQ notification framework. This allows you register a PL/SQL callback which is invoked asynchronously when a message shows up in a queue.

How Can I Use HTTPS with AQ?

I need to send XML messages to suppliers using HTTPS and get a response back.

Sending a message using HTTP does not appear to much of a problem using something like, however there does not seem to be anything about making a HTTPS connection. Products like Portal and OC4J seem to have an HTTP client in the source code does Oracle/anyone have one for HTTPS connections which can be reused?

Answer: You can use Internet access functionality of Oracle9i AQ. Using this functionality, you can enqueue and dequeue messages over HTTP(S) securely and transactionally using XML. You can get more details on this functionality at:

What Are the Options for Storing XML in AQ Message Payloads?

When storing XML in AQ message payloads, is there any other way of natively doing this other than having an ADT as the payload with sys.xmltype as part of the ADT? For example, create or replace type object xml_data_typ AS object (xml_data sys.XMLType); My understanding is that you can ONLY have either a RAW or ADT as message payloads.

Answer: In Oracle9.0.1, this is the only way to store XMLTypes in queues. In Oracle9i Release 2 (9.2), you can create queues with payload and attributes as XMLType.

Can We Compare IDAP and SOAP?

Answer: IDAP is the SOAP specification for AQ operations. IDAP is the XML specification for AQ operations. SOAP defines a generic mechanism to invoke a service. IDAP defines these mechanisms to perform AQ operations.

IDAP in addition has the following key properties not defined by SOAP: