AQ Adapter Implementation in SOA 12C

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

UseCase:- In this usecase, we will try to dequeue message from Request AQ through BPEL and insert in Response AQ

1.   First of all, we need to create AQ’s on the database , then will start developing SOA composite. In order to create Request AQ, use below script:- 

Create user Demo identified by oracle;  

GRANT CONNECT,RESOURCE,DBA TO Demo;

grant EXECUTE ON DBMS_AQ to Demo;

CREATE OR REPLACE type Demo.REQ_AQ_TABLE_TYP as object(

ORDERID NUMBER,

 orderedQty VARCHAR2(100),

 status VARCHAR2(100)

);

EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ('Demo.REQ_aq_table','Demo.REQ_aq_table_typ');

EXECUTE DBMS_AQADM.CREATE_QUEUE ('Demo.REQ_aq','Demo.REQ_aq_table');

EXECUTE DBMS_AQADM.START_QUEUE ('Demo.REQ_aq');

grant all privileges on Demo.REQ_AQ_TABLE_TYP to Demo;

After executing above script, we can confirm it from database:-

Similarly Create Response Queue by executing below script:-

create type Demo.RESP_AQ_TABLE_TYP as object(

ORDERNumber NUMBER,

 orderedQty VARCHAR2(100)

 );

EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ('Demo.RESP_aq_table','Demo.RESP_aq_table_typ');

EXECUTE DBMS_AQADM.CREATE_QUEUE ('Demo.RESP_aq','Demo.RESP_aq_table');

EXECUTE DBMS_AQADM.START_QUEUE ('Demo.RESP_aq');

grant all privileges on Demo.RESP_AQ_TABLE_TYP to Demo;

2.) Create a new SOA project and name it as ‘AQDemo’

3.) Drag and drop AQ Adapter from Components Pallet to the Left hand lane i.e ‘Exposed Service’ of the design view of composite.xml[AQDemo.xml]

•    Give name as ‘AQPollingService’  and click next
• 
  
  

4.)Click on '+' sign to add new database connection if it doesn't exist ,  else select Database Connection from drop down . Enter the Database details and click OK.

5.)After selecting the connection, Enter the JNDI name Example:- 'eis/AQ/Demo_DB'. If someone wants to   use existing  JNDI on server, then select it by clicking search button on right side. It will open a popup and we can select the JNDI from that wizard.

6.)Choose 'Define From Operation and schema'  option and click next

7.)Choose 'Dequeue' operation as we want to poll message from Req AQ

 
8.)Select Database Schema from Drop down. It is 'Demo'  on our case. Then Choose Queue Name by Clicking on Browse Button. then click next.

9.)  If someone wants to set ‘Correlation ID’ or ‘Dequeue Condition’ then set it on below wizard. Otherwise go  with empty values and click next

10.)    Select the option ‘Whole Object REQ_AQ_TABLE_TYP’ and click next

11.)Click Finish

12.) Similarly create one more AQ Adapter for Enqueuing the message in Resp queue which is ‘RESP_AQ’ in our case.

Drag and drop AQ Adapter from Components Pallet to the Right hand lane i.e ‘External References’ of the design view of composite.xml[AQDemo.xml] 

Repeat the steps mentioned above for creating Req Queue.

                           I.          Give the name of service as ‘AQInsertService’.

                         II.          After Reaching step  4 of Configuration wizard, select operation as ‘Enqueue’ and click next. 

             III. On step 5, Choose the database Schema & Queue Name of Response queue as below.

13.) Create one-way BPEL Process ‘AQDemo’ and connect it with AQAdapters through wires as below:-

14.)Before Deployment of SOA Composite, we need to Configure JNDI of connection Pool which was mentioned while creating AQ adapters.

Go to http://<hostname>:<port>/console 

                           a.          Go to Deployments -> AqAdapter -> Configuration -> Outbound Connection Pools -> New

And Create new JNDI for connection pool as below:-

           b.)  After that open the new created JNDI and go to Properties tab.

Enter the value of XADataSourceName. [We need to create new datasource on server or we can use existing Datasource]

 
           c.)   After creating AQAdapter Connection pool,we need to update the Plan.xml

Click on Deployments -> AQAdapter -> Click Update

           d.) Click next and then click Finish.

 

Testing

In order to test it, Create new SOAPUI project and create TestSuite in that.

Create one TestSuite Step of ‘JDBC request’. And use below code to insert message in request Queue.

DECLARE

  enqueue_options dbms_aq.enqueue_options_t;

  message_properties dbms_aq.message_properties_t;

  message_handle RAW(16);

  MESSAGE Demo.REQ_AQ_TABLE_TYP;

  message_id NUMBER;

BEGIN

  MESSAGE                            := Demo.REQ_AQ_TABLE_TYP(12345,'70','Approved');

  DBMS_AQ.ENQUEUE ( queue_name => 'Demo.REQ_aq', enqueue_options => enqueue_options, message_properties => message_properties, payload => MESSAGE, msgid => message_handle);

  COMMIT;

END

From RESP_AQ, we can confirm the message insertion as below:-                                         

Watch this space for more analysis on AQ Adapter. Will  cover implementation of New features of AQ Adapter in 12C SOA.