AQ and SOA - Part1 - Creation of AQ queue

Oracle AQ is a database queuing system and is implemented in database tables. So the first thing we need is database access.

Requirements:

If you have the database schema/user details in which you are going to create the queue then skip below step, otherwise, 

Create a user:

sqlplus sys@localhost:1521/xe as sysdba

create user AQ_SOA_USER identified by oracle;

Grant priviledge to create tables and queues:

grant create session, resource, AQ_ADMINISTRATOR_ROLE,AQ_USER_ROLE to AQ_SOA_USER;

grant execute on DBMS_AQADM to AQ_SOA_USER; -- enque/dequeue priviledge to the user 

grant execute on DBMS_AQ to AQ_SOA_USER;  -- to enable compilation of a PL/SQL procedure 

once we have the user details, login to database using above user(AQ_SOA_USER), you can use SQLDeveloper/Jdeveloper or any other tool available.

Create Object

create or replace type employee_type as object(

employee_id number,

employee_number varchar2(60),

employee_name varchar2(60)

);

Create a queue table

begin

DBMS_AQADM.CREATE_QUEUE_TABLE (

queue_table => 'AQ_SOA_USER.employee_queue_table',

queue_payload_type => 'AQ_SOA_USER.employee_type');

end;

Create a queue

BEGIN

DBMS_AQADM.CREATE_QUEUE (

queue_name =>'AQ_SOA_USER.employee_queue',

queue_table=>'AQ_SOA_USER.employee_queue_table');

END;

SELECT queue_table,type,object_type,recipients FROM USER_QUEUE_TABLES;

Start the queue

exec DBMS_AQADM.START_QUEUE('AQ_SOA_USER.employee_queue');

Enqueue AQ queue with PL/SQL package

DECLARE

enqueue_options dbms_aq.enqueue_options_t;

message_properties dbms_aq.message_properties_t;

message_handle RAW(16);

message employee_type ;

message_id NUMBER;

BEGIN

message := employee_type (10, 'D1001', 'John');

enqueue_options.VISIBILITY := DBMS_AQ.ON_COMMIT;

enqueue_options.SEQUENCE_DEVIATION := null;

message_properties.EXPIRATION := DBMS_AQ.NEVER;

DBMS_AQ.ENQUEUE (

queue_name => 'employee_queue',

enqueue_options => enqueue_options,

message_properties => message_properties,

payload => message,

msgid => message_handle);

COMMIT;

END;

/

SELECT * FROM aq$employee_queue_table;

SELECT user_data FROM aq$employee_queue_table;

Script to drop a queue if required:

begin

  dbms_aqadm.stop_queue('AQ_SOA_USER.employee_queue');

exception 

  when others then 

     if sqlcode = -24010 then null; else raise; end if;

end; 

/

begin

    dbms_aqadm.drop_queue(queue_name =>'AQ_SOA_USER.employee_queue');

exception 

  when others then 

     if sqlcode = -24010 then null; else raise; end if;

end; 

/

begin    

    dbms_aqadm.drop_queue_table(

                   queue_table => 'AQ_SOA_USER.employee_queue_table'

                   );

exception 

  when others then 

     if sqlcode = -24002 then null; else raise; end if;

end;

/

Next - Posting message to AQ queue from SOA