Saturday, May 3, 2014

In this post, I will show you how to create advanced queue in Oracle which is compatible with Oracle SOA.

You can either use sys user or create different user for this advanced queue. If you plan to use another user then you need to provide AQ privileges to that user.

Follow below steps to create new user and provide AQ privileges to that user.

sqlplus system/password as SYSDBA
                     
GRANT connect, resource TO aquser IDENTIFIED BY aquser;
GRANT aq_user_role TO aquser;
GRANT execute ON sys.dbms_aqadm TO aquser;
GRANT execute ON sys.dbms_aq TO aquser;
GRANT execute ON sys.dbms_aqin TO aquser;
GRANT execute ON sys.dbms_aqjms TO aquser;

To create advanced queue you need to run below command.

Create Advanced Queue

BEGIN
    DBMS_AQADM.CREATE_QUEUE_TABLE(
            QUEUE_TABLE =>'SampleAQTable',
            QUEUE_PAYLOAD_TYPE =>'RAW',
            COMPATIBLE => '10.0');
END;

Create Advanced Queue Table

BEGIN
    DBMS_AQADM.CREATE_QUEUE(
            QUEUE_NAME =>'SampleAQ',
            QUEUE_TABLE =>'SampleAQTable');
END;


You need to start the queue before using it.

Start Advanced Queue

BEGIN
DBMS_AQADM.START_QUEUE (
QUEUE_NAME => 'SampleAQ');
END;

Execute below command to check the queue and table.

SELECT object_name, object_type FROM user_objects where object_name like '%SAMPLE%';


Your queue is ready to integrate with Oracle SOA.




Run below command if you want to drop AQ and AQ table.

First you need to stop the queue then only you can drop it.

Stop advanced queue

BEGIN
    DBMS_AQADM.STOP_QUEUE (
    QUEUE_NAME => 'SampleAQ');
END;

Drop advanced queue

BEGIN
    DBMS_AQADM.DROP_QUEUE (
    QUEUE_NAME => 'SampleAQ');
END;

Drop advanced queue table

BEGIN
    DBMS_AQADM.DROP_QUEUE_TABLE (
    QUEUE_TABLE => 'SampleAQTable');

END;



5 comments :

  1. Hi Vivek,
    Thanks for the post.
    I have following doubts:
    1. Does AQ have the same RETRY mechanism like JMS queue. In my project we are using AQ as source and JMS as target. So incase if i want to retry, then what will be the best approach.

    2. Also can we search and manually reprocess the pending message in OSB.
    (I think we need to create separate web application for above scenario).

    Please suggest.

    Thanks in adavance.

    Best Regards,
    Pankaj

    ReplyDelete
    Replies
    1. Regarding your first scenario, if your target is JMS then you can apply retry mechanism to JMS adapter instead of AQ.

      OSB is stateless and does not store any instance data. How you confirm that message is in pending state ?

      Vivek Garg

      Delete
  2. Hi Vivek,
    Thanks for your reply.
    1. Yes, we can do it in JMS but just wanted to know If it is possible in AQ also.
    2.Lets take an scenario where DB is down and we will retry till 3 times then we will log the message or send it to error queue. From error queue I want to reprocess manually.
    Is it possible as I dont want to create a saparate service which will pick up the message from error queue and send it back to Inbound queue as probably it will be in infinite loop.

    Thanks in advance.

    Pankaj

    ReplyDelete
    Replies
    1. Hi Pankaj,

      1.) It depends on payload type of your AQ.
      2.) If your error queue is AQ then in case of DB is down, put your message to error AQ with some delay. Use pick activity in your BPEL which picks the message from both main queue and error queue. Make sure you add delay when you put message to error AQ because we don't have polling frequency in case of AQ adapter.

      You can refer my post, how to add delay in AQ.

      http://soawork.blogspot.com/2014/06/delay-in-oracle-aq-adapter.html

      Vivek Garg

      Delete
  3. Hi Vivek,
    Thanks for the information.


    Pankaj

    ReplyDelete