Creating Advance queue (AQ) in Oracle DB and using in Oracle SOA Suite

In this post we will see, how to create AQ and use it in oracle SOA services.

Login to your database with SYS user and create user AQADMN and then create the tables and queues in that user.

Login as SYS –>

CONNECT sys/welcome1 as sysdba

create user aqadmn identified by welcome1 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

GRANT aq_administrator_role TO aqadmn;

GRANT connect               TO aqadmn;

GRANT create type           TO aqadmn;

GRANT create sequence       TO aqadmn;

EXECUTE dbms_aqadm.grant_type_access(‘aqadmn’);

ALTER USER aqadmn QUOTA UNLIMITED ON users;

Login as AQADMN–>

connect aqadmn / welcome1;

CREATE TYPE aqmsgtype AS OBJECT ( message_id     NUMBER(15)  , description VARCHAR2(100)  ,text           VARCHAR2(100) );

Creating Queue Table–>

BEGIN

DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => ‘aqadmn.msg_quetab’ ,       queue_payload_type  => ‘aqadmn.aqmsgtype’ );

END;

Creating Queue–>

BEGIN

DBMS_AQADM.CREATE_QUEUE (queue_name  => ‘msg_queue’ , queue_table => ‘aqadmn.msg_quetab’ , queue_type  => DBMS_AQADM.NORMAL_QUEUE , max_retries => 0, retry_delay => 0 , retention_time => 1209600, dependency_tracking => FALSE , comment             => ‘Test Message Type Queue’ , auto_commit => FALSE );

END;

Starting Queue–>

BEGIN

DBMS_AQADM.START_QUEUE(‘msg_queue’);

END;

Now login to your weblogic administration console and create a data source and point to the database that we have created above.

Go To deployment and click on Aq adapter and create a new jndi outbound connection to point to the above data source.

Now we will create a simple process which is En-queue the data to the queue.

Go To external references, and drag AQ adapter from component palette.

Enter the name and select the connection.

aq1

Select the operation as Enqueue and click on Next. aq2

Select the database schema as “aqadmn” and browse for the queue.

aq3

Click on Next and , select the business payload as “Whole object AQMSGTYPE” and click on Next.

aq4

click on Finish to complete the adapter configuration.

Now my Bpel process looks like below.

aq5

That’s it, you are done with AQ configuration, Test the composite and you will get the new message in your AQ queue.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s