Oracle AQ Adapter Introduction

This post describes about Oracle AQ Adapter along with its features.

Oracle Streams Advanced Queuing (AQ) provides a flexible mechanism for
bidirectional, asynchronous communication between participating applications.
Advanced queues are an Oracle database feature, and are therefore scalable and
reliable. Other features of Oracle database, such as backup and recovery (including
any-point-in-time recovery), logging, transactional services, and system management,
are also inherited by advanced queues.

AQ Adapter Features:

The Oracle AQ Adapter is both a producer and a consumer of AQ messages. The
enqueue operation is exposed as a JCA outbound interaction. The dequeue operation
is exposed as a JCA inbound interaction.

The Oracle AQ Adapter supports ADT (Oracle object type), XMLType, and RAW queues
as payloads. It also supports extracting a payload from one ADT member column.

Prerequisites to use AQ Adapter :

To work with AQ Adapter, we need to have following resources in data base.

• Queues
• Queue Table 

Follow the below steps for creating above resources.
[Note: Use Sql developer or putty or SqlPlus to execute following SQL Script]

Creating User:
           create user SATISH_AQ identified by SATISH_AQ;

Providing privileges for SATISH_AQ:

     GRANT aq_administrator_role TO SATISH_AQ;
     GRANT create type           TO SATISH_AQ;
     GRANT create sequence       TO SATISH_AQ;
     execute DBMS_AQADM.GRANT_TYPE_ACCESS('SATISH_AQ');

Create/Drop Object Type:Createing the message of required structure which we are going to publish through SOA Process.

    CREATE OR REPLACE type SATISH_AQ.ORDERMSGTYPE
    AS
      object
               (
                 ORDER_ID    NUMBER(10),
                ITEM_NAME   VARCHAR2(100),
                description VARCHAR(1000)
              )
   ;

  DROP type SATISH_AQ.ORDERMSGTYPE;

Create/Drop Queue Table and Queue:
     BEGIN
                     DBMS_AQADM.CREATE_QUEUE_TABLE( queue_table =>                                                                 'SATISH_AQ.ORDER_QUEUE_TBL', MULTIPLE_CONSUMERS => true,                               QUEUE_PAYLOAD_TYPE => 'SATISH_AQ.OrderMsgType' );

                     DBMS_AQADM.CREATE_QUEUE ( queue_name =>                                                           'SATISH_AQ.ORDER_QUEUE', queue_table =>                                                                                   'SATISH_AQ.ORDER_QUEUE_TBL' );
   END;

   EXECUTE dbms_aqadm.drop_queue (queue_name => 'SATISH_AQ.ORDER_QUEUE');
   EXECUTE dbms_aqadm.drop_queue_table (queue_table =>                                                                            'SATISH_AQ.ORDER_QUEUE_TBL');

Start/Stop Queue : we need to start the queue before publish the data.
     BEGIN
                  DBMS_AQADM.START_QUEUE('SATISH_AQ.ORDER_QUEUE');
     END;
 
    BEGIN
                DBMS_AQADM.STOP_QUEUE('SATISH_AQ.ORDER_QUEUE');
    END;

To Verify the data in the Queue table, you can use below queries.
    SELECT * FROM SATISH_AQ.AQ$ORDER_QUEUE_TBL;
    SELECT MSG_STATE FROM SATISH_AQ.AQ$ORDER_QUEUE_TBL;


Now we are going to create and configuring the data source jndi for AQ Adapter.Please follow the below steps.

1. Open EM Console to create Data Source jndi for the same user which we have created in above steps. Follow the below sceen shots to create Data Source Jndi.                                                                                                                                             
2.                                                         
3. Configuring AQ Adapter Connection Factory with Data Source Jndi .                                                                                                                                                       
4.  
5. Now we need to update the deployment of AQ Adapter. 

The resources which are required for the AQ Adapter has been done.We will continue to explore  AQ Adapter  with an use case in the next post.