Tuesday, December 4, 2012

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


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

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 :)


No comments: