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');