AQ Adapter for Complex Custom Objects

This post would concentrate on creating AQ for consuming custom message structures. The pre-requisites for creating a custom AQ is -

1) Oracle DB 10g – AQ queues are created at DB
2) Admin user credential with sysdba role to grant permission for 
normal user (e.g. scott) as in setup_user_grant.sql
3) user credentials for normal user (scott).

Step #1: Granting Permissions to Normal User

Connect tot he oracle DB "sys as sysdba" and run the following script

GRANT CONNECT, RESOURCE to scott;
GRANT EXECUTE ON SYS.DBMS_AQ to scott;
GRANT EXECUTE ON SYS.DBMS_AQADM to scott;
GRANT EXECUTE ON SYS.DBMS_AQIN to scott;
commit;

Step #2: Create Objects (Simple/Complex)

Now log out and re-connect as normal user (scott)

For simple objects like (Student:Roll_Num, Name, Age) run the following >>

create type STUDENT_TYPE as OBJECT (
  ROLL_NUM VARCHAR2(128),
  NAME VARCHAR2(512),
  AGE number
);

For a more complex scenario like >>

Vendor(ID,Name,Equipments (occurs multiple times),Feedback (occurs Multiple Times))

Equipment(ID,Description,Rate)

Feedback(ID,Description)

Create the Types as written below>>

create type Equipment_type is OBJECT
(
id NUMBER,
description VARCHAR(100),
rate NUMBER(10,2)
);

create type Equipment_Arr is VARRAY(50) of Equipment_type;

create type Feedback_type is OBJECT
(
id NUMBER,
description VARCHAR(100)
);

create type Feedback_Arr is VARRAY(50) of Feedback_type;

create type vendor is OBJECT
(
id NUMBER,
name VARCHAR(100),
Equipments Equipment_Arr,
feedback Feedback_Arr 
);

Step #3: Create and Start Queue

Here we need to create the table corresponding to object created above and link it with a queue and start the queue.

begin
  dbms_aqadm.create_queue_table('stud_in_table', 'student_type');  
  dbms_aqadm.create_queue('stud_in_queue', 'stud_in_table');
  dbms_aqadm.start_queue('stud_in_queue');

  dbms_aqadm.create_queue_table('stud_out_table', 'student_type');  
  dbms_aqadm.create_queue('stud_out_queue', 'stud_out_table');
  dbms_aqadm.start_queue('stud_out_queue');
end;

Here we created two queues- in-queue and out-queue, one for reading the contents and another one for writing contents in it. (In case of the complex scenario above, the queue table would be created only for vendor and not for equipment/feedback).

Once this procedure is completed successfully, this AQ can be utilized by BPEL AQ Adapter for enqueuing or dequeuing the object of interest.

The configuration of AQ is not covered in this post you may visit here for a step by step procedure for configuring the AQ Adapter in BPEL.