Oracle database integration using Advance Queuing (AQ)

AQ is a proprietary messaging technology from Oracle that can be handy for integrating Oracle databases with applications (e.g. OSB, BPEL, Java, even VisualBasic) or other Oracle databases.

The advantages of using AQ (messaging integration style) compared to the traditional jdbc connection (database integration style):

• reliability (AQ supports message persistence, retry)
• avoid sharing/locking problem of database integration style
• messaging integration style is more scalable than point to point integration using jdbc connection

Architecture example

A typical use case is a data push scenario: a change in the database (e.g. employee data) needs to be propagate to different applications/databases.

• a queue message is generated in a Oracle database (e.g. using plsql / trigger when an update is occurred)
• a jca adapter is made using JDeveloper/SOA Suite (AQ adapter)
• using this jca adapter the queue can be consumed by OSB, Soa Suite BPEL or Java code.
• the queue can be also consumed by other Oracle databases

The AQ also supports more scenarios such as publish-subscribe or sending messages in opposite directions than in the usecase above (the message is produced by applications and then consumed by Oracle database).

How

Create the payload object using CREATE TYPE payload_name AS OBJECT
Create the queue table using DBMS_AQADM.CREATE_QUEUE_TABLE 
Create the queue using  DBMS_AQADM.CREATE_QUEUE 

To use:

Start the queue using DBMS_AQADM.START_QUEUE
Create a payload
Send the queue payload using DBMS_AQ.ENQUEUE

You can see the numbers of messages in the queue using: SELECT COUNT(*) FROM queue_table_name;
You can view the messages payload using: SELECT user_data FROM queue_table_name;

For examples, please refer to http://www.oracle-developer.net/display.php?id=411

For generating jca adapter using JDeveloper and the example of consuming the queue using OSB, please refer to the book" Oracle Service Bus 11g Development Cookbook" chapter 7.

Notes
You need these priveleges:  AQ_ADMINISTRATOR_ROLE to create the queue and EXECUTE ON DBMS_AQ to execute the plsql to send the queue.

Source: Steve's blogs http://soa-java.blogspot.com/

Any comments are welcome :)

---

References: