Skip Headers

Oracle® Application Developer's Guide - XML 10g (9.0.4) Part Number B12099-01 |
Home | Contents | Index

/

# 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 Oracle database brings the integrity, reliability, recoverability, scalability, performance, and security features of Oracle to message queuing. Integration with Oracle 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 Oracle, 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 Oracle 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: Oracle8i 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

- Oracle. 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 l`ong_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 -- 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 -- 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

    <?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-- 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 -- 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-- 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:app1@hotmail.com</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 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 -- Messages from an ADT Queue

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 Oracle 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

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 PL/SQL Function

- 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 PL/SQL Function

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, Oracle8i 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 Oracle 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 Oracle.

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

#### Answer 2

- If you store XML as CLOBs then you can definitely search it using Oracle 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 Oracle 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 Oracle 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.

Oracle 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.

| Copyright © 2001, 2003 Oracle Corporation. All Rights Reserved. |
Home | Contents | Index