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;
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 (
For a more complex scenario like >>
Vendor(ID,Name,Equipments (occurs multiple times),Feedback (occurs Multiple Times))
Create the Types as written below>>
create type Equipment_type is OBJECT
create type Equipment_Arr is VARRAY(50) of Equipment_type;
create type Feedback_type is OBJECT
create type Feedback_Arr is VARRAY(50) of Feedback_type;
create type vendor is OBJECT
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.
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.