/

9
Exchanging XML Data Using Oracle AQ

This chapter contains the following sections:

• What is AQ?

• How do AQ and XML Complement Each Other?

• Internet-Data-Access-Presentation (IDAP)
  • IDAP Architecture

  • IDAP Message Body is an AQ XML Document

  • IDAP Client Requests for Enqueue

  • IDAP Client Requests for Dequeue

  • IDAP Client Requests for Registration

  • IDAP Server Response to Enqueue

  • Server Response to a Dequeue Request

  • Server Response to a Register Request

  • Notification

  • IDAP and AQ XML Schemas

• AQXMLServlet

• XMLType Queues

• AQ XML Message Format Transformation

• Frequently Asked Questions (FAQs): XML and Advanced Queuing

What is AQ?

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

• Enables and manages asynchronous communication of two or more applications using messages.

• Supports point-to-point and publish/subscribe communication models.

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.

See "Internet-Data-Access-Presentation (IDAP)" .

AQ and XML Message Payloads

Figure 9-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:

• Message flow using subscription rules

• Message management

• Extracting business intelligence from messages

• Message transformation

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

• Intra-business. Typical examples of this kind of scenario include sales order fulfillment and supply-chain management.

• Inter-business processes. Here multiple integration hubs can communicate over the Internet backplane. Examples of inter-business scenarios include travel reservations, coordination between manufacturers and suppliers, transferring of funds between banks, and insurance claims settlements, among others.

  Oracle uses this in its enterprise application integration products. XML messages are sent from applications to an AQ hub, here shown as an OIS hub. This serves as a "message server" for any application that wants the message. Through this hub and spoke architecture, XML messages can be communicated asynchronously to multiple loosely-coupled receiving applications.

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

• Web-based application that uses an AQ operation over an HTTP connection using IDAP

• Accounting application that uses AQ to propagate an XML message over a Net* connection.

• Shipping and inventory application that uses AQ to propagate an IDAP/XML message directly to the database over HTTP.

Figure 9-1 Advanced Queueing and XML Message Payloads

Text description of the illustration adxml015.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.

Advanced Queuing 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.

Internet-Data-Access-Presentation (IDAP)

You can now perform AQ operations over the Internet by using Internet Data Access Presentation (IDAP). IDAP defines the message structure using XML. IDAP- structured message is sent over the Internet using using transport protocols such as HTTP or SMTP.

XML and the IDAP Interface

The Internet Data Access Presentation (IDAP) uses the Content-Type of text/xml to specify the body of the request containing an XML-encoded method request. XML provides the presentation for IDAP request and response messages as follows:

• All protocol tags are scoped to the IDAP namespace.

• The sender includes namespaces in IDAP elements and attributes.

• The receiver processes IDAP messages that have correct namespaces; for requests with incorrect namespaces, the receiver returns an invalid request error.

• The receiver processes IDAP messages without namespaces as though they had the correct namespaces, if the context is valid.

• The IDAP namespace has the value http://ns.oracle.com/AQ/schemas/envelope

• An XML document forming the request of an IDAP invocation does not require the use of an XML DTD or a schema.

  See Also: Oracle9i Application Developer's Guide - Advanced Queuing

IDAP Architecture

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

• Client program which sends XML messages, that conform to IDAP format, to the AQ Servlet. This can be any HTTP client, such as, Web browsers.

• The Webserver or ServletRunner which hosts the AQ servlet that can interpret the incoming XML messages, for example, Apache/Jserv or Tomcat

• Oracle9i. The AQ servlet connects to this server to perform operations in your queues.

The AQ client program sends XML messages (conforming to IDAP) to the AQ servlet. Any HTTP client, for example Web browsers, can be used. The Web server/ServletRunner hosting the AQ servlet interprets the incoming XML messages. Examples include Apache/Jserv or Tomcat. The AQ servlet connects to the Oracle database server and performs operations on the users' queues.

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

Text description of the illustration adxml101.gif

Figure 9-3 shows IDAP architecture when using SMTP. For SMTP, you will need the following two additional components:

• An Email Server

• An LDAP Server

The Email server verifies client signatures using certificates stored in LDAP and then routes the request to the AQ servlet.

Figure 9-3 IDAP Architecture for Performing AQ Operations Using SMTP
Text description of the illustration adxml102.gif

IDAP Method Invocation

A method invocation is performed by creating the request header and body and processing the returned response header and body. The request and response headers can consist of standard transport protocol-specific and extended headers.

HTTP Headers

The POST method within the HTTP request header performs the IDAP method invocation. The request should include the header IDAPMethodName, whose value indicates the method to be invoked on the target. The value consists of a URI followed by a "#", followed by a method name (which must not include the "#" character), as follows:

IDAPMethodName: http://ns.oracle.com/AQ/schemas/access#AQXmlSend

The URI used for the interface must match the implied or specified namespace qualification of the method name element in the IDAP:Body part of the payload.

SMTP Headers

In the case of SMTP (email), the method invocation can be done by the filter interface of the email server, which invokes a Java method with the email-message-body as argument. This results in remote invocation of the POST method on the AQ servlet. The response is emailed directly to the recipient specified in the reply of the message. The response header can contain SMTP-protocol-related headers also.

IDAP Message Structure

IDAP structures a message request or response as follows:

• IDAP envelope (the root or top element in an XML tree))

• IDAP header (first element under the root)

• IDAP body (the AQ XML document)

The IDAP Envelope

The tag of this root element is IDAP:Envelope. IDAP defines a global attribute IDAP:encodingStyle that indicates serialization rules used instead of those described by the IDAP specification. This attribute may appear on any element and is scoped to that element and all child elements not themselves containing such an attribute. Omitting IDAP:encodingStyle means that type specification has been followed (unless overridden by a parent element).

The IDAP envelope also contains namespace declarations and additional attributes, provided they are namespace-qualified. Additional namespace-qualified subelements can follow the body.

IDAP Headers

The tag of this first element under the root is IDAP:Header. An IDAP header passes necessary information, such as the transaction ID, with the request. The header is encoded as a child of the IDAP:Envelope XML element. Headers are identified by the name element and are namespace-qualified. A header entry is encoded as an embedded element.

The IDAP Body

The IDAP body, tagged IDAP:Body, contains a first subelement whose name is the method name. This method request element contains elements for each input and output parameter. The element names are the parameter names. The body also contains IDAP: Fault, indicating information about an error.

For performing AQ operations, the IDAP body must contain an AQ XML document. The AQ XML document has the namespace http://ns.oracle.com/AQ/schemas/access

IDAP Method Invocation Body: "IDAP Payload"

IDAP method invocation consists of a method request and optionally a method response. The IDAP method request and method response are HTTP request and response, respectively, whose content is an XML document that consists of the root and mandatory body elements. This XML document is referred to as IDAP payload in the rest of this chapter.

The IDAP payload is defined as follows:

• IDAP root element is the top element in the XML tree.

• IDAP payload headers contain additional information that must travel with the request.

• The method request is represented as an XML element with additional elements for parameters. It is the first child of the IDAP:Body element. This request can be one of the AQ XML client requests described in the next section

• The response is the return value or error/exception that is passed back to the client. The encoding rules are as follows:

Requests: Outcomes at the Receiving Site

At the receiving site, a request can have one of the following four outcomes:

1. The HTTP infrastructure on the receiving site was able to receive and process the request. The HTTP infrastructure passes the headers and body to the IDAP infrastructure.

2. The HTTP infrastructure on the receiving site could not receive and process the request. The result is an HTTP response containing an HTTP error in the status field and no XML body.

3. The IDAP infrastructure on the receiving site was able to decode the input parameters, dispatch to an appropriate server indicated by the server address, and invoke an application-level function corresponding semantically to the method indicated in the method request. The result of the method request consists of a response or error.

4. IDAP infrastructure on the receiving site could not decode the input parameters, dispatch to an appropriate server indicated by the server address, and invoke an application-level function corresponding semantically to the interface or method indicated in the method request. The result of the method is a error indicating a error that prevented the dispatching infrastructure on the receiving side from successful completion.

In (c) and (d), additional message headers may, for extensibility, again be present in the request results.

Results from a Method Request

The results of the request are provided in a request-response format. The HTTP response must be of Content-Type "text/xml".

An IDAP result indicates success. An a error indicates failure. The method response will never contain both a result and an error. The different types of responses and errors are described in the next section

IDAP Message Body is an AQ XML Document

The body of an IDAP message is an AQ XML document, which represents:

• Client requests for enqueue, dequeue, and registration

• Server responses to client requests for enqueue, dequeue, and registration

• Notifications from the server to the client

  Note: AQ Internet Access is supported only for 8.1-style queues. 8.0-style queues cannot be accessed using IDAP.

IDAP Client Requests for Enqueue

Client requests for enqueue--SEND and PUBLISH requests--use the following methods:

• AQXmlSend--to enqueue to a single-consumer queue

• AQXmlPublish--to enqueue to multiconsumer queues/topics

AQXmlSend and AQXmlPublish take the arguments and argument attributes shown in Table 9-1. Required arguments are shown in bold.

Table 9-1 IDAP Client Requests for Enqueue--Arguments and Attributes for AQXmlSend and AQXmlPublish

Argument	Attribute
producer_options	destination--specify the queue/topic to which messages are to be sent. The destination element has an attribute lookup_type which determines how the destination element value is interpreted DATABASE (default)--destination is interpreted as schema.queue_name LDAP--the LDAP server is used to resolve the destination
	visibility ON_COMMIT--The enqueue is part of the current transaction. The operation is complete when the transaction commits. This is the default case. IMMEDIATE--effects of the enqueue are visible immediately after the request is completed. The enqueue is not part of the current transaction. The operation constitutes a transaction on its own.
	transformation--the PL/SQL transformation to be invoked before the message is enqueued
message_set--contains one or more messages.	Each message consists of a message_header and message_payload
message_header	message_id--unique identifier of the message, supplied during dequeue
	correlation--correlation identifier of the message
	expiration--duration in seconds that a message is available for dequeuing. This parameter is an offset from the delay. By default messages never expire. If the message is not dequeued before it expires, then it is moved to the exception queue in the EXPIRED state
	delay--duration in seconds after which a message is available for processing
	priority--the priority of the message. A smaller number indicates higher priority. The priority can be any number, including negative numbers.
	sender_id--the application-specified identifier agent_name, address, protocol agent_alias--if specified, resolves to a name, address, protocol using LDAP
	recipient_list--overrides the default subscriber list; lookup_type defines if the recipients are specified or looked up in LDAP agent_name, address, protocol agent_alias--if specified, resolves to a name, address, protocol using LDAP
	message_state--the state of the message is filled in automatically during dequeue 0: The message is ready to be processed. 1: The message delay has not yet been reached. 2: The message has been processed and is retained. 3: The message has been moved to the exception queue.
	exception_queue--in case of exceptions the name of the queue to which the message is moved if it cannot be processed successfully. Messages are moved in two cases: The number of unsuccessful dequeue attempts has exceeded max_retries or the message has expired. All messages in the exception queue are in the EXPIRED state. The default is the exception queue associated with the queue table. If the exception queue specified does not exist at the time of the move, then the message is moved to the default exception queue associated with the queue table, and a warning is logged in the alert file. If the default exception queue is used, then the parameter returns a NULL value at dequeue time.
message_payload	this can have different sub-elements based on the payload type of the destination queue/topic. The different payload types are described in the next section
AQXmlCommit	this is an empty element--if specified, the user transaction is committed at the end of the request

Message Payloads

AQ supports messages of the following types:

• RAW Queues

• Oracle Object (ADT) Type Queues

• Java Message Service (JMS) Type Queues/Topics

All these types of queues can be accessed using IDAP. If the queue holds messages in RAW, Oracle object, or JMS format, XML payloads are transformed to the appropriate internal format during enqueue and stored in the queue. During dequeue, when messages are obtained from queues containing messages in any of the above formats, they are converted to XML before being sent to the client.

The message payload type depends on the type of the queue on which the operation is being performed. A discussion of the queue types follows:

RAW Queues

The contents of RAW queues are raw bytes. The user must supply the hex representation of the message payload in the XML message. For example, <raw>023f4523</raw>.

Oracle Object (ADT) Type Queues

For ADT queues that are not JMS queues (that is, they are not type AQ$_JMS_*), the type of the payload depends on the type specified while creating the queue table that holds the queue. The XML specified here must map to the SQL type of the payload for the queue table.

See Also: Chapter 5, "Database Support for XML" Chapter 7, "XML SQL Utility (XSU)" for more details on mapping SQL types to XML.

ADT Type Queues Example

Assume the queue is defined to be of type EMP_TYP, which has the following structure:

create or replace type emp_typ as object (
     empno NUMBER(4),
     ename VARCHAR2(10),
     job VARCHAR2(9),
     mgr NUMBER(4),
     hiredate DATE,
     sal   NUMBER(7,2),
     comm  NUMBER(7,2)
     deptno NUMBER(2));

The corresponding XML representation is:

              <EMP_TYP>
              	  <EMPNO>1111</EMPNO>
              	  <ENAME>Mary</ENAME>
          	      <MGR>5000</MGR>
          	      <HIREDATE>1996-01-01 0:0:0</HIREDATE>
                <SAL>10000</SAL>
                <COMM>100.12</COMM>
          	      <DEPTNO>60</DEPTNO>
          	    </EMP_TYP>

Java Message Service (JMS) Type Queues/Topics

For queues with JMS types (that is, those with payloads of type AQ$_JMS_*), there are four different XML elements, depending on the JMS type. Hence, IDAP supports queues/topics with the following JMS types:

• TextMessage

• MapMessage

• BytesMessage

• ObjectMessage

  Note: JMS queues with payload type StreamMessage are not supported through IDAP.

Table 9-2 lists the JMS types and XML components. The distinct XML element for each JMS type is shown in its respective column. Required elements are shown in bold.

Table 9-2 JMS Types and XML Components  

jms_text_message	jms_map_message	jms_bytes_message	jms_object_message
Used for queues/topics with payload type:
AQ$_JMS_TEXT_MESSAGE	AQ$_JMS_MAP_MESSAGE	AQ$_JMS_BYTES_MESSAGE	AQ$_JMS_OBJECT_MESSAGE
oracle_jms_properties
user_properties
text_data--string representing the text payload	map_data--set of name-value pairs called items, consisting of: name int_value or string_value or long_value or double_value or boolean_value or float_value or short_value or byte_value	bytes_data--hex representation of the payload bytes	ser_object_data--hex representation of the serialized object

All JMS messages consist of the following common elements:

• oracle_jms_properties, which consists of
  • type--type of the message

  • reply_to--consists of an agent_name, address, and protocol

  • userid--supplied by AQ; client cannot specify

  • appid--application identifier

  • groupid--group identifier

  • group_sequence--sequence within the group identified by group_id

  • timestamp--the time the message was sent, which cannot be specified during enqueue. It is automatically populated in a message that is dequeued.

  • recv_timestamp--the time the message was received

• user_properties--in addition to the above predefined properties, users can also specify their own message properties as name-value pairs. The user_properties consists of a list of property elements. Each property is a name-value pair consisting of the following:
  • name--property name

  • int_value--integer property value or

    string_value--string property value or

    long_value--long property value or

    double_value--double property value or

    boolean_value--boolean property value or

    float_value-- float property value or

    short_value--short property value or

    byte_value--byte property value or

The following examples show enqueue requests using the different message and queue types.

IDAP Enqueue Request Example1 -- Sending an ADT Message to a Single-Consumer Queue

The queue QS.NEW_ORDER_QUE has a payload of type ORDER_TYP.

 <?xml version="1.0"?>
   <Envelope xmlns= "http://ns.oracle.com/AQ/schemas/envelope">
      <Body>
        <AQXmlSend xmlns = "http://ns.oracle.com/AQ/schemas/access">
          <producer_options>
            <destination>QS.NEW_ORDERS_QUE</destination>
          </producer_options>
          
          <message_set>
            <message_count>1</message_count>	
          
            <message>
              <message_number>1</message_number>
          
              <message_header>
                	<correlation>ORDER1</correlation>
             	   <sender_id>
          	        <agent_name>scott</agent_name>
          	      </sender_id> 
              </message_header>
          
              <message_payload>	

               <ORDER_TYP>
                     <ORDERNO>100</ORDERNO>
                     <STATUS>NEW</STATUS>
                     <ORDERTYPE>URGENT</ORDERTYPE>
                     <ORDERREGION>EAST</ORDERREGION>
                     <CUSTOMER>
                        <CUSTNO>1001233</CUSTNO>
                        <CUSTID>MA1234555623212</CUSTID>
                        <NAME>AMERICAN EXPRESS</NAME>
                        <STREET>EXPRESS STREET</STREET>
                        <CITY>REDWOOD CITY</CITY>
                        <STATE>CA</STATE>
                        <ZIP>94065</ZIP>
                        <COUNTRY>USA</COUNTRY>
                     </CUSTOMER>
                     <PAYMENTMETHOD>CREDIT</PAYMENTMETHOD>
                     <ITEMS>
                        <ITEMS_ITEM>
                           <QUANTITY>10</QUANTITY>
                           <ITEM>
                              <TITLE>Perl</TITLE>
                              <AUTHORS>Randal</AUTHORS>
                              <ISBN>ISBN20200</ISBN>
                              <PRICE>19</PRICE>
                           </ITEM>
                           <SUBTOTAL>190</SUBTOTAL>
                        </ITEMS_ITEM>
                        <ITEMS_ITEM>
                           <QUANTITY>20</QUANTITY>
                           <ITEM>
                              <TITLE>XML</TITLE>
                              <AUTHORS>Micheal</AUTHORS>
                              <ISBN>ISBN20212</ISBN>
                              <PRICE>59</PRICE>
                           </ITEM>
                           <SUBTOTAL>590</SUBTOTAL>
                        </ITEMS_ITEM>
                     </ITEMS>
                     <CCNUMBER>NUMBER01</CCNUMBER>
                     <ORDER_DATE>2000-08-23 0:0:0</ORDER_DATE>
               </ORDER_TYP>
             </message_payload>
            </message>
          </message_set>
        </AQXmlSend>  
      </Body>
</Envelope> 

IDAP Enqueue Request Example 2 -- Publishing an ADT Message to a Multiconsumer Queue

The multiconsumer queue AQUSER.EMP_TOPIC has a payload of type EMP_TYP. EMP_TYP has the following structure:

create or replace type emp_typ as object (
     empno NUMBER(4),
     ename VARCHAR2(10),
     job VARCHAR2(9),
     mgr NUMBER(4),
     hiredate DATE,
     sal   NUMBER(7,2),
     comm  NUMBER(7,2)
     deptno NUMBER(2));

 

A PUBLISH request has the following format:

<?xml version="1.0"?>   
<Envelope xmlns= "http://ns.oracle.com/AQ/schemas/envelope">
      <Body>
        <AQXmlPublish xmlns = "http://ns.oracle.com/AQ/schemas/access">
          <producer_options>
            <destination>AQUSER.EMP_TOPIC</destination>
          </producer_options>
          

          <message_set>
            <message_count>1</message_count>	
          
            <message>
              <message_number>1</message_number>
          
              <message_header>
                 	<correlation>NEWEMP</correlation>
          	       <sender_id>
          	          <agent_name>scott</agent_name>
          	       </sender_id> 
              </message_header>
          
              <message_payload>	
          	    <EMP_TYP>
              	  <EMPNO>1111</EMPNO>
              	  <ENAME>Mary</ENAME>
          	      <MGR>5000</MGR>
          	      <HIREDATE>1996-01-01 0:0:0</HIREDATE>
                <SAL>10000</SAL>
                <COMM>100.12</COMM>
          	      <DEPTNO>60</DEPTNO>
          	    </EMP_TYP>
             </message_payload>
            </message>
          </message_set>
        </AQXmlPublish>  
      </Body>
</Envelope> 

IDAP Enqueue Request Example 3 -- Sending a Message to a JMS Queue

The JMS queue AQUSER.JMS_TEXTQ has payload type JMS Text message (SYS.AQ$_JMS_TEXT_MESSAGE). The send request has the following format:

<?xml version="1.0"?>
<Envelope xmlns= "http://ns.oracle.com/AQ/schemas/envelope">
      <Body>
      
        <AQXmlSend xmlns = "http://ns.oracle.com/AQ/schemas/access">
          <producer_options>
            <destination>AQUSER.JMS_TEXTQ</destination>
          </producer_options>
          
          <message_set>
            <message_count>1</message_count>	
          
            <message>
              <message_number>1</message_number>
          
              <message_header>
                	<correlation>text_msg</correlation>
          	        <sender_id>
          	   	        <agent_name>john</agent_name>
          	        </sender_id>
              </message_header>
          
              <message_payload>	
          
              	<jms_text_message>	
               	 <oracle_jms_properties>
             	    <appid>AQProduct</appid>
          	       <groupid>AQ</groupid>
          	      </oracle_jms_properties>
          
              	  <user_properties>
              	    <property>
              	      <name>Country</name>
          	          <string_value>USA</string_value>
          	        </property>
          	        <property>
          	           <name>State</name>
          	          <string_value>California</string_value>
          	        </property>
          	       </user_properties>

          	       <text_data>All things bright and beautiful</text_data>
               </jms_text_message>
             </message_payload>
            </message>
          </message_set>          
        	</AQXmlSend>  
      </Body>
</Envelope> 

IDAP Enqueue Request Example 4 -- Sending/Publishing and Committing the Transaction

<?xml version="1.0"?>   
<Envelope xmlns= "http://ns.oracle.com/AQ/schemas/envelope">
      <Body>
        <AQXmlPublish xmlns = "http://ns.oracle.com/AQ/schemas/access">
          <producer_options>
            <destination>AQUSER.EMP_TOPIC</destination>
          </producer_options>
          
          <message_set>
            <message_count>1</message_count>	
          
            <message>
              <message_number>1</message_number>
          
              <message_header>
          	<correlation>NEWEMP</correlation>
          	<sender_id>
          	   <agent_name>scott</agent_name>
          	</sender_id> 
              </message_header>
          
              <message_payload>	
                	<EMP_TYP>
              	    <EMPNO>1111</EMPNO>
              	    <ENAME>Mary</ENAME>
          	        <MGR>5000</MGR>
          	        <HIREDATE>1996-01-01 0:0:0</HIREDATE>
                  <SAL>10000</SAL>
                  <COMM>100.12</COMM>
          	        <DEPTNO>60</DEPTNO>
          	     </EMP_TYP>
             </message_payload>
            </message>
          </message_set>

        <AQXmlCommit/>

        </AQXmlPublish>  
      </Body>
</Envelope> 

IDAP Client Requests for Dequeue

Client requests for dequeue use the AQXmlReceive method. Table 9-3 lists AQXmlReceive method's arguments and argument attributes. Required arguments are shown in bold.

Table 9-3 IDAP Client Requests for Dequeue--Arguments and Attributes for AQXmlReceive  

Argument	Attribute
consumer_options	destination--specify the queue/topic from which messages are to be received. The destination element has an attribute lookup_type which determines how the destination element value is interpreted DATABASE (default)--destination is interpreted as schema.queue_name LDAP--the LDAP server is used to resolve the destination
	consumer_name--Name of the consumer. Only those messages matching the consumer name are accessed. If a queue is not set up for multiple consumers, then this field should not be specified
	wait_time--the time (in seconds) to wait if there is currently no message available which matches the search criteria
	selector--criteria used to select the message, specified as one of: correlation--the correlation identifier of the message to be dequeued. message_id-- the message identifier of the message to be dequeued condition--dequeue message that satisfy this condition. A condition is specified as a Boolean expression using syntax similar to the WHERE clause of a SQL query. This Boolean expression can include conditions on message properties, user data properties (object payloads only), and PL/SQL or SQL functions (as specified in the where clause of a SQL query). Message properties include priority, corrid and other columns in the queue table To specify dequeue conditions on a message payload (object payload), use attributes of the object type in clauses. You must prefix each attribute with tab.user_data as a qualifier to indicate the specific column of the queue table that stores the payload. The deq_condition parameter cannot exceed 4000 characters.
	visibility ON_COMMIT (default)--The dequeue is part of the current transaction. The operation is complete when the transaction commits. IMMEDIATE--effects of the dequeue are visible immediately after the request is completed. The dequeue is not part of the current transaction. The operation constitutes a transaction on its own.
	dequeue_mode--Specifies the locking behavior associated with the dequeue. The dequeue_mode can be specified as one of: REMOVE (default): Read the message and update or delete it. This is the default. The message can be retained in the queue table based on the retention properties. BROWSE: Read the message without acquiring any lock on the message. This is equivalent to a select statement. LOCKED: Read and obtain a write lock on the message. The lock lasts for the duration of the transaction. This is equivalent to a select for update statement.
	navigation_mode--Specifies the position of the message that will be retrieved. First, the position is determined. Second, the search criterion is applied. Finally, the message is retrieved. The navigation_mode can be specified as one of: FIRST_MESSAGE: Retrieves the first message which is available and matches the search criteria. This resets the position to the beginning of the queue. NEXT_MESSAGE (default): Retrieve the next message which is available and matches the search criteria. If the previous message belongs to a message group, then AQ retrieves the next available message which matches the search criteria and belongs to the message group. This is the default. NEXT_TRANSACTION: Skip the remainder of the current transaction group (if any) and retrieve the first message of the next transaction group. This option can only be used if message grouping is enabled for the current queue.
	transformation--the PL/SQL transformation to be invoked after the message is dequeued

The following examples show dequeue requests using different attributes of AQXmlReceive.

IDAP Dequeue Request Example 1-- Receiving Messages from a Single-Consumer Queue

Using the single-consumer queue QS.NEW_ORDERS_QUE, the receive request has the following format:

<?xml version="1.0"?>
<Envelope xmlns= "http://ns.oracle.com/AQ/schemas/envelope">
      <Body>
        <AQXmlReceive xmlns = "http://ns.oracle.com/AQ/schemas/access">
          <consumer_options>
            <destination>QS.NEW_ORDERS_QUE</destination>
            <wait_time>0</wait_time>
          </consumer_options>
        	</AQXmlReceive>  
      </Body>
</Envelope> 

IDAP Dequeue Request Example 2 -- Receiving Messages that Satisfy a Specific Condition

Using the multiconsumer queue AQUSER.EMP_TOPIC with subscriber APP1 and condition deptno=60, the receive request has the following format:

<?xml version="1.0"?>
<Envelope xmlns= "http://ns.oracle.com/AQ/schemas/envelope">
      <Body>
        <AQXmlReceive xmlns = "http://ns.oracle.com/AQ/schemas/access">
          <consumer_options>
            <destination>AQUSER.EMP_TOPIC</destination>
            <consumer_name>APP1</consumer_name>
            <wait_time>0</wait_time>
            <selector>
                 <condition>tab.user_data.deptno=60</condition>
            </selector>
          </consumer_options>
        	</AQXmlReceive>  
      </Body>
</Envelope> 

IDAP Dequeue Request Example 3 -- Receiving Messages and Committing

In the dequeue request examples, if you include AQXmlCommit at the end of the RECEIVE request, the transaction is committed upon completion of the operation. In "IDAP Dequeue Request Example 1-- Receiving Messages from a Single-Consumer Queue", the receive request can include the commit flag as follows:

<?xml version="1.0"?>
<Envelope xmlns= "http://ns.oracle.com/AQ/schemas/envelope">
      <Body>
        <AQXmlReceive xmlns = "http://ns.oracle.com/AQ/schemas/access">
          <consumer_options>
            <destination>QS.NEW_ORDERS_QUE</destination>
            <wait_time>0</wait_time>
          </consumer_options>

          	<AQXmlCommit/>

        	</AQXmlReceive>  
      </Body>
</Envelope>

IDAP Dequeue Request Example 4 -- Browsing Messages

Messages are dequeued in REMOVE mode by default. To receive messages from QS.NEW_ORDERS_QUE in BROWSE mode, modify the receive request as follows:

<?xml version="1.0"?>
<Envelope xmlns= "http://ns.oracle.com/AQ/schemas/envelope">
      <Body>
        <AQXmlReceive xmlns = "http://ns.oracle.com/AQ/schemas/access">
          <consumer_options>
            <destination>QS.NEW_ORDERS_QUE</destination>
            <wait_time>0</wait_time>
            <dequeue_mode>BROWSE</dequeue_mode>
          </consumer_options>
         	</AQXmlReceive>  
      </Body>
</Envelope> 

IDAP Client Requests for Registration

Client requests for registration use the AQXmlRegister method. Table 9-4 lists AQXmlRegister's arguments and argument attributes. Required arguments are shown in bold.

.

Table 9-4 Client Registration--Arguments and Attributes for AQXmlRegister

Argument	Attribute
register_options	destination--specify the queue or topic on which notifications are registered. The destination element has an attribute lookup_type which determines how the destination element value is interpreted DATABASE (default)--destination is interpreted as schema.queue_name LDAP--the LDAP server is used to resolve the destination
	consumer_name--the consumer name for multiconsumer queues or topics. For single consumer queues, this parameter must not be specified
	notify_url--where notification is sent when a message is enqueued. The form can be http://<url> or mailto://<email address> or plsql://<pl/sql procedure>.

IDAP Register Request Example 1-- Registering for Notification at an Email Address

To notify an email address of messages enqueued for consumer APP1 in queue AQUSER.EMP_TOPIC, the register request has the following format:

<?xml version="1.0"?>
<Envelope xmlns= "http://ns.oracle.com/AQ/schemas/envelope">
      <Body>
      
        <AQXmlRegister xmlns = "http://ns.oracle.com/AQ/schemas/access">

          <register_options>
            <destination>AQUSER.EMP_TOPIC</destination>
            <consumer_name>APP1</consumer_name>
            <notify_url>mailto:[e-mail]</notify_url>
          </register_options>
          
          <AQXmlCommit/>
          
        	</AQXmlRegister>  
      </Body>
</Envelope> 

Commit Request

A request to commit all actions performed by the user in a session uses the AQXmlCommit method.

Commit Request Example

A commit request has the following format.

<?xml version="1.0"?>
<Envelope xmlns="http://ns.oracle.com/AQ/schemas/envelope">
   <Body>
      <AQXmlCommit xmlns="http://ns.oracle.com/AQ/schemas/access"/>
   </Body>
</Envelope>

Rollback Request

A request to roll back all actions performed by the user in a session uses the AQXmlRollback method. Actions performed with IMMEDIATE visibility are not rolled back.

Rollback Request Example

A rollback request has the following format:

<?xml version="1.0"?>
<Envelope xmlns="http://ns.oracle.com/AQ/schemas/envelope">
   <Body>
      <AQXmlRollback xmlns="http://ns.oracle.com/AQ/schemas/access"/>
   </Body>
</Envelope>

IDAP Server Response to Enqueue

The response to an enqueue request to a single-consumer queue uses the AQXmlSendResponse method. The components of the response are shown in Table 9-5.

.

Table 9-5 Server Response to an Enqueue to a Single-Consumer Queue (AQXmlSendResponse)  

Response	Attribute
status_response	status_code--indicates success (0) or failure (-1) error_code--Oracle code for the error error_message--description of the error
send_result	destination--where the message was sent message_id--identifier for every message sent

IDAP Server Request Example 1 -- Enqueuing a Single Message to a Single-Consumer Queue

The result of a SEND request to the single consumer queue QS.NEW_ORDERS_QUE has the following format:

<?xml version = '1.0'?>
<Envelope xmlns="http://ns.oracle.com/AQ/schemas/envelope">
   <Body>
      <AQXmlSendResponse xmlns="http://ns.oracle.com/AQ/schemas/access">
         <status_response>
            <status_code>0</status_code>
         </status_response>
         <send_result>
            <destination>QS.NEW_ORDERS_QUE</destination>
            <message_id>12341234123412341234</message_id>
         </send_result>
      </AQXmlSendResponse>
   </Body>
</Envelope>

The response to an enqueue request to a multiconsumer queue or topic uses the AQXmlPublishResponse method. The components of the response are shown in Table 9-6.

.

Table 9-6 Server Response to an Enqueue to a Multiconsumer Queue or Topic (AQXmlPublishResponse)

Response	Attribute
status_response	status_code--indicates success (0) or failure (-1) error_code--Oracle code for the error error_message--description of the error
publish_result	destination--where the message was sent message_id--identifier for every message sent

IDAP Server Request Example 2-- Enqueuing to a Multiconsumer Queue

The result of a SEND request to the multiconsumer queue AQUSER.EMP_TOPIC has the following format:

<?xml version = '1.0'?>
<Envelope xmlns="http://ns.oracle.com/AQ/schemas/envelope">
   <Body>
      <AQXmlPublishResponse xmlns="http://ns.oracle.com/AQ/schemas/access">
         <status_response>
            <status_code>0</status_code>
         </status_response>
         <publish_result>
            <destination>AQUSER.EMP_TOPIC</destination>
            <message_id>23434435435456546546546546</message_id>
         </publish_result>
      </AQXmlPublishResponse>
   </Body>
</Envelope>

Server Response to a Dequeue Request

The response to a dequeue request uses the AQXmlReceiveResponse method. The components of the response are shown in Table 9-7.

.

Table 9-7 Server Response to a Dequeue from a Queue or Topic (AQXmlReceiveResponse)

Response	Attribute
status_response	status_code--indicates success (0) or failure (-1) error_code--Oracle code for the error error_message--description of the error
receive_result	destination--where the message was sent message_set--the set of messages dequeued

IDAP Server Dequeue Response Example 1 -- Receiving Messages from an ADT Queue (AQXmlReceiveResponse)

The result of a RECEIVE request on the queue AQUSER.EMP_TOPIC with a payload of type EMP_TYP has the following format:

<?xml version = '1.0'?>
<Envelope xmlns="http://ns.oracle.com/AQ/schemas/envelope">
   <Body>
      <AQXmlReceiveResponse xmlns="http://ns.oracle.com/AQ/schemas/access">
         <status_response>
            <status_code>0</status_code>
         </status_response>
         <receive_result>
            <destination>AQUSER.EMP_TOPIC</destination>
            <message_set>
               <message_count>1</message_count>
               <message>
                  <message_number>1</message_number>
                  <message_header>
                     <message_id>1234344545565667</message_id>
                     <correlation>TKAXAP10</correlation>
                     <priority>1</priority>
                     <delivery_count>0</delivery_count>
                     <sender_id>
                        <agent_name>scott</agent_name>
                     </sender_id>
                     <message_state>0</message_state>
                  </message_header>
                  <message_payload>
                    <EMP_TYP>
              	        <EMPNO>1111</EMPNO>
              	        <ENAME>Mary</ENAME>
          	            <MGR>5000</MGR>
          	            <HIREDATE>1996-01-01 0:0:0</HIREDATE>
                      <SAL>10000</SAL>
                      <COMM>100.12</COMM>
          	            <DEPTNO>60</DEPTNO>
          	          </EMP_TYP>
                 </message_payload>
               </message>
            </message_set>
         </receive_result>
      </AQXmlReceiveResponse>
   </Body>
</Envelope>

Server Response to a Register Request

The response to a register request uses the AQXmlRegisterResponse method, which consists of status_response. (SeeTable 9-7 for a description of status_response.)

Commit Response

The response to a commit request uses the AQXmlCommitResponse method, which consists of status_response. (SeeTable 9-7 for a description of status_response.)

Example

The response to a commit request has the following format:

<?xml version = '1.0'?>
<Envelope xmlns="http://ns.oracle.com/AQ/schemas/envelope">
   <Body>
      <AQXmlCommitResponse xmlns="http://ns.oracle.com/AQ/schemas/access">
         <status_response>
            <status_code>0</status_code>
         </status_response>
      </AQXmlCommitResponse>
   </Body>
</Envelope>

Rollback Response

The response to a rollback request uses the AQXmlRollbackResponse method, which consists of status_response. (SeeTable 9-7 for a description of status_response.)

Notification

When an event for which a client has registered occurs, a notification is sent to the client at the URL specified in the REGISTER request. AQXmlNotification consists of:

• notification_options, which has
  • destination--the destination queue/topic on which the event occurred

  • consumer_name--in case of multiconsumer queues/topics, this refers to the consumer name for which the event occurred

• message_set--the set of message properties.

Response in Case of Error

In case of an error in any of the above requests, a FAULT is generated. The FAULT element consists of:

• faultcode - error code for fault

• faultstring - indicates a client error or a server error. A client error means that the request is not valid. Server error indicates that the AQ servlet has not been set up correctly

• detail, which consists of
  • status_response

IDAP and AQ XML Schemas

IDAP presentation exposes the following two schemas to the client. All documents sent by the Parser are validated against these two schemas:

• IDAP schema -- http://ns.oracle.com/AQ/schemas/envelope

  This describes the structure of the document. Each document has an envelope, header, and body.

• AQ XML schema. -- http://ns.oracle.com/AQ/schemas/access

  This describes the IDAP body contents for Internet access to AQ features.

  See Also: Oracle9i Application Developer's Guide - Advanced Queuing

AQXMLServlet

AQXMLServlet is a Java class that extends oracle.AQ.xml.AQxmlServlet class. AQxmlServlet class in turn extends javax.servlet.http.HttpServlet class.

See Also: Oracle9i Application Developer's Guide - Advanced Queuing for information on creating and deploying AQ XML Servlet.

Accessing AQXMLServlet with HTTP

See: Oracle9i Application Developer's Guide - Advanced Queuing, for setting up AQ to receive XML messages over HTTP.

How AQ Client Makes a Request to AQ Servlet Using HTTP

The general AQ client procedure making a request using HTTP to the AQ Servlet, is as follows:

1. The AQ client opens an HTTP(S) connection to the server. For example: https://aq.us.oracle.com:8000/aqserv/servlet/AQTestServlet

   This opens a connection to port 8000 on aq.us.oracle.com

2. The AQ client logs in to the server by either:
   • HTTP basic authentication (with or without SSL)

   • SSL certificate based client authentication.

3. The AQ client constructs the XML message representing the Send, Publish, Receive or Register request. For example:

   <?xml version="1.0"?>
   <Envelope xmlns="http://ns.oracle.com/AQ/schemas/envelope">
         <Body>
           <AQXmlSend  xmlns = "http://ns.oracle.com/AQ/schemas/access">
             <producer_options>
               <destination>OE.OE_NEW_ORDERS_QUE</destination>
             </producer_options>
             <message_set>
               <message_count>1</message_count>	          
               <message>
                 <message_number>1</message_number>   
                 <message_header>
                   	<correlation>XML_ADT_SINGLE_ENQ</correlation>
                    	<sender_id>
             	           <agent_name>john</agent_name>
             	       </sender_id>
                 </message_header>
                 <message_payload>	
                 <ORDER_TYP>
                        <ORDERNO>100</ORDERNO>
                        <STATUS>NEW</STATUS>
                        <ORDERTYPE>NORMAL</ORDERTYPE>
                        <ORDERREGION>EAST</ORDERREGION>
                        <CUSTOMER>
                           <CUSTNO>1001233</CUSTNO>
                           <CUSTID>JOHN</CUSTID>
                           <NAME>AMERICAN EXPRESS</NAME>
                           <STREET>EXPRESS STREET</STREET>
                           <CITY>REDWOOD CITY</CITY>
                           <STATE>CA</STATE>
                           <ZIP>94065</ZIP>
                           <COUNTRY>USA</COUNTRY>
                        </CUSTOMER>
                        <PAYMENTMETHOD>CREDIT</PAYMENTMETHOD>
                        <ITEMS>
                           <ITEMS_ITEM>
                              <QUANTITY>10</QUANTITY>
                              <ITEM>
                                 <TITLE>Perl</TITLE>
                                 <AUTHORS>Randal</AUTHORS>
                                 <ISBN>ISBN20200</ISBN>
                                 <PRICE>19</PRICE>
                              </ITEM>
                              <SUBTOTAL>190</SUBTOTAL>
                           </ITEMS_ITEM>
                         </ITEMS>
                        <CCNUMBER>NUMBER01</CCNUMBER>
                        <ORDER_DATE>2000-08-23 0:0:0</ORDER_DATE>
                  </ORDER_TYP>
                </message_payload>
               </message>
             </message_set>
             </AQXmlSend>
         </Body>
   </Envelope> 
   
   

4. The AQ client sends an HTTP POST to the Servlet at the remote server.

How AQ Servlet Processes a Request Using HTTP

The AQ servlet's general procedure for making a request using HTTP is as follows:

1. The server accepts the client HTTP(S) connection

2. The server authenticates the user (AQ agent) specified by the client

3. The server receives the POST request

4. AQ servlet is invoked. If this is the first request being serviced by this servlet, the servlet is initialized - its init( ) method is invoked. The init() method creates a connection pool to the Oracle9i server using the AQxmlDataSource parameters (sid, host, port, aq servlet super-user name, password) provided by the client.

5. AQ servlet processes the message as follows:
   1. If this is the first request from this client, a new HTTP session is created. The XML message is parsed and its contents are validated. If a SessionID is passed by the client in the HTTP headers, then this operation is performed in the context of that session - this is described in detail in Oracle9i Application Developer's Guide - Advanced Queuing

   2. The servlet determines which object (queue/topic) the agent is trying to perform operations on. For example, in the above request sequence (Step 3 in "How AQ Client Makes a Request to AQ Servlet Using HTTP"), the agent "JOHN" is trying to access the OE.OE_NEW_ORDERS_QUE.

   3. After that the servlet looks through the list of database users that map to this AQ Agent (using the AQ$INTERNET_USERS view). If any one of these db_users has privileges to access the queue/topic specified in the request, the aq servlet super-user creates a session on behalf of this db_user.

   4. For example, in the above example, say, "JOHN" was mapped to the database user "OE" using the DBMS_AQADM.ENABLE_DB_ACCESS call. The servlet will create a session for the agent "JOHN" with the privileges of database user OE.

   5. If there is no transaction active in this HTTP session, then a new database transaction is started. Subsequent requests in this session will be part of the same transaction until an explicit commit or rollback request is made.

   6. Now the requested operation (send/publish/receive/register) is performed.

   7. The response is formatted as an XML message and sent back the client. For exampl, the response for the above request could be:

        <Envelope xmlns="http://ns.oracle.com/AQ/schemas/envelope">
         <Body>
            <AQXmlSendResponse
               xmlns="http://ns.oracle.com/AQ/schemas/access">
               <status_response>
                  <status_code>0</status_code>
               </status_response>
               <send_result>
                  <destination>OE.OE_NEW_ORDERS_QUE</destination>
                  <message_id>12341234123412341234123412341234</message_id>
               </send_result>
            </AQXmlSendResponse>
         </Body>
      </Envelope>
      
      

      The response also includes the session id in the HTTP headers as a cookie. For example: Tomcat sends back session ids as JSESSIONID=239454ds2343

XMLType Queues

Storing and Querying XML Documents with Advanced Queueing (AQ)

Advanced Queuing (AQ) supports the storage of XML documents in queues and provides the ability to query the XML documents. XML can be used with Oracle AQ in the following two cases:

• XML data stored in queues. XML payloads are supported by AQ queues. XML messages can be stored as the XMLType datatype.

• XML data generated from existing queues with ADT or RAW payloads. Used by applications that already store their data in ADT or RAW queues and where new applications, written on the same data, need to use XML as the message format.

  See Also: Chapter 5, "Database Support for XML", for more details on XML support in the database.

Structuring and Managing Message Payloads with Object Types

With Oracle AQ, you can use object types to structure and manage the payload of messages. Using strongly typed content, content whose format is defined by an external type system, the following AQ features are made available:

• Content-based routing: AQ can examine the content and automatically route messages to another queue based on content.

• Content-based subscription: A publish and subscribe system can be built on top of a messaging system so that you can create subscriptions based on content.

• Querying: The ability to execute queries on the content of messages allows you to examine current and processed messages for various applications, including message warehousing.

Creating Message Payloads Queues Containing XMLType Attributes

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:

• Store more than one type of XML document in the same queue. The documents are stored internally as CLOBs.

• Selectively dequeue messages with XMLType attributes using the operators XMLType.existsNode(), XMLType.extract(), and so on.

  See Also: Oracle9i Application Developer's Guide - XML for details on XMLType operations.

  For details on XMlType operations refer to Application Developer's guide - XML
• Define transformations to convert Oracle objects to XMLType.

• Define rule-based subscribers that query message content using XMLType operators such as XMLType.existsNode() and XMLType.extract().

XMLType Queues Example 1: Creating XMLType Queue Tables for a Queue Object Type Containing Messages with XMLType Attributes

In the BooksOnline application, assume that the Overseas Shipping site represents the order as ORDER_XML_TYP, with the order information in an XMLType attribute. The Order Entry site represents the order as an Oracle object, ORDER_TYP.

ORDER_XML_TYP is a composite type that contains an XMLType attribute:

CREATE OR REPLACE TYPE order_xml_typ as OBJECT (
   orderno  NUMBER,
   details  XMLTYPE);

The Overseas queue table and queue are created as follows:

BEGIN
dbms_aqadm.create_queue_table(                          
   queue_table        => 'OS_orders_pr_mqtab',            
   comment            => 'Overseas Shipping MultiConsumer Orders queue table', 
   multiple_consumers => TRUE,                             
   queue_payload_type => 'OS.order_xml_typ',                       
   compatible         => '8.1');
END;

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

AQ XML Message Format Transformation

You can specify transformations between different Oracle and user-defined types. Transformations can be created in any of the following ways:

• PL/SQL functions (including callouts). See AQ Message Transformation Example 1: Creating a Single PL/SQL Function that Returns an XMLType Object or Constructor of Target Type

• SQL expressions

• Java stored procedures

with a return type of the target type.

Only one-to-one message transformation is supported. The transformation engine is integrated with Advanced Queuing to facilitate transformation of messages as they move through the database messaging system.

An Advanced Queuing application can enqueue or dequeue messages from a queue in the format specified by the application. An application can also specify a message format when subscribing to queues.

The AQ propagator transforms messages to the format of the destination queue message, as specified by the remote subscription. The transformation function cannot write the database state or commit/rollback the current transaction. Transformations are exported with a schema or a full database export.

AQ Message Transformation Example 1: Creating a Single PL/SQL Function that Returns an XMLType Object or Constructor of Target Type

An Order Entry site represents the order as Oracle object, ORDER_TYP.

Since the Overseas Shipping site subscribes to messages in the OE_BOOKEDORDERS_QUE queue, a transformation is applied before messages are propagated from the Order Entry site to the Overseas Shipping site.

ORDER_XML_TYP is a composite type that contains an XMLType attribute:

CREATE OR REPLACE TYPE order_xml_typ as OBJECT (
   orderno  NUMBER,
   details  XMLTYPE);

The transformation is defined as follows:

CREATE OR REPLACE FUNCTION CONVERT_TO_ORDER_XML(input_order TYPE OE.ORDER_TYP)
RETURN OS.ORDER_XML_TYP AS
  xdata SYS.XMLType;
  new_order OS.ORDER_XML_TYP;
BEGIN
  xdata := XMLType.createXML(input_order, NULL);
  new_order := OS.ORDER_XML_TYP(input_order.orderno, xdata);
  RETURN new_order;
END CONVERT_TO_ORDER_XML;

execute dbms_transform.create_transformation(
   schema =>         'OS', 
   name   =>         'OE2XML',
   from_schema =>    'OE', 
   from_type =>      'ORDER_TYP',
   to_schema =>      'OS', 
   to_type =>        'ORDER_XML_TYP',
   transformation => 'CONVERT_TO_ORDER_XML(source.user_data)');

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

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

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           OS.order_xml_typ; 
no_messages              exception; 
pragma exception_init    (no_messages, -25228); 
new_orders               BOOLEAN := TRUE; 
  
begin 
        dopt.wait := 1;

/* Specify dequeue condition to select Orders for Canada */ 
        dopt.deq_condition := 'tab.user_data.xdata.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 No: ' ||  
                                   deq_order_data.orderno);
         
          EXCEPTION 
            WHEN no_messages THEN 
                 dbms_output.put_line (' ---- NO MORE ORDERS  ---- '); 
                 new_orders := FALSE; 
          END; 
        END LOOP; 
end; 

See Also Oracle9i Application Developer's Guide - Advanced Queuing, Chapter 8, for more detail on how to implement structured message payloads applications using either DBMS_AQADM or Java (JDBC) Oracle9i Supplied PL/SQL Packages Reference for more information about DBMS_TRANSFORM.

Frequently Asked Questions (FAQs): XML and Advanced Queuing

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

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 that these sets 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 the 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.

Can We Add 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.

How Does Oracle Enqueue and Dequeue and Process XML Messages?

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 processing 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 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 We Parse Messages with XML Content From AQ Queues?

Question

We 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, we want to retrieve and parse XML documents and map specific fields to database tables and columns.

Is Oracle9i Text (intermedia Text/Context) 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 Oracle9i.

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 Oracle9i Text (intermedia Text)?

Answer 2

• If you store XML as CLOBs then you can definitely search it using Oracle9i Text (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 Oracle9i Text (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 Oracle9i Text (interMedia Text) CONTAINS() clause to have the best of both.

See Also: Chapter 8, "Searching XML Data with Oracle Text".

Can we Prevent the Listener From Stopping Until the XML Document is Processed?

Question

We 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. We are 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.