SOA 11g - Create Advance Queue (AQ)

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;

Tweet