How to Setup AQ With Examples

Create User

Login to SQLPLUS using sys as sysdba
In the following code you are creating user called "fusiondev" and password "fusiondev"

create user fusiondev identified by fusiondev

Setting Up basic Privileges

grant resource, connect, create view to fusion dev

Setting Up AQ Privileges

In the following code you are giving both administrator and user roles to the fusiondev user

grant aq_administrator_role and aq_user_role to fusiondev.

Setting Up Message Type ( Or Object)

In the following code you are setting up an object with a message strcture of
MessageType, and actual Message. This message type can be used further to query the queue to see what message are there etc., but it's not mandatory.

create or replace type OrderMsgType as object (
MessageType VARCHAR2(10),
Message CLOB
);

Create Queue Table

In the following code you are creating a Queue Table called "ORDER_QUEUE_TBL" that there are multiple consumers and the message structure is of "OrderMsgType" which you creaated above.

BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE(
queue_table => 'fusiondev.ORDER_QUEUE_TBL',
multiple_consumers => TRUE,
queue_payload_type => 'OrderMsgType'
);

Create Queue

In the following code you are creating a Queue Table called "ORDER_QUEUE" and it's tied to ORDER_QUEUE_TBL which you creaated above.

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

Starting Queue

Here you are starting the queue and ready to be consumed.

BEGIN
DBMS_AQADM.START_QUEUE('fusiondev.ORDER_QUEUE');
END;

Now your AQ is Ready to be published/consumed

Stopping Queue

Here you are Stopping the queue and ready to be consumed.

BEGIN
DBMS_AQADM.STOP_QUEUE('fusiondev.ORDER_QUEUE');
END;

Browse the Queue

Here you are Stopping the queue and ready to be consumed.

select * from AQ$ORDER_QUEUE_TBL

State of the Message

At any given time the message have different states. We will talk about those in detail later.

select MSG_STATE from AQ$ORDER_QUEUE_TBL

BPEL Process - PublishAQOnDemand

This process creates a message and publishes it to AQ. This message is consumed by two recepients identified as ConsumeAQMessage1 & ConsumeAQMessage2.
Download Link

BPEL Process - ConsumeAQMessage1 & ConsumeAQMessage2

These processes consumes the message.
Download Link
Download Link

WoW Factor

Step 1

Now that you have the processes spoon fed to you.. Now create a bpel process consume the message. While defined the adapter in the BPEL Process Designer set the consumer name to "MyAQLearningProcess" and deploy.

Step 2

Then go to BPEL Console and click on the Process "PublishAQOnDemand" go to the Descriptor Page you will see something like below.

Step 3

Now Add your "MyAQLearningProcess" to the end of the recepientList and click on update descriptor button. So it should look like ConsumeAQMessage1, ConsumeAQMessage2, MyAQLearningProcess

Step 4

Then when you execute "PublishAQOnDemand" Process you will see 3 new instances created.