Advanced Queue in Oracle Database

Here we will create advanced queue in Oracle Database and will perform enqueue and dequeue operations also using DBMS_AQADM package. Follow the below steps to achieve this.

Add below two roles to user for queuing operation in database.

• AQ_ADMINISTRATOR_ROLEA�- Allows creation and administration of queuing infrastructure.
• AQ_USER_ROLEA�- Allows access to queues for enqueue and dequeue operations.

GRANT aq_administrator_role TOA�system;

GRANT aq_user_role TO system;

Define Payload for Queue

The payload is defined using object type before creating queue.

CREATE OR REPLACE TYPE emp_msg_type AS OBJECT (

nameA�A�A�A�A�A�A�A�A�A�A� VARCHAR2(20),

idA�A�A�A�A�A�A�A�A�A�A�A�A� NUMBER,

experienceA�A�A�A�A� NUMBER,

ageA�A�A�A�A�A�A�A�A�A�A�A� NUMBER,

salaryA�A�A�A�A�A�A�A�A� NUMBER,

cityA�A�A�A�A�A�A�A�A�A�A� VARCHAR2(20),

bonusA�A�A�A�A�A�A�A�A�A� NUMBER

);

GRANT EXECUTE ON emp_msg_type TO system;

Create Queue

Now create queue table using payload and then create queue on the table. All operations will be performed on DBMS_AQADM package. You can query queue table using below format.

select *A�from aq$emp_queue_tab;

EXECUTE DBMS_AQADM.create_queue_table (

queue_tableA�A�A�A�A�A�A�A�A�A�A� =>A� ’emp_queue_tab’,

queue_payload_typeA�A�A�A� =>A� ’emp_msg_type’);

EXECUTE DBMS_AQADM.create_queue (

queue_nameA�A�A�A�A�A�A�A�A�A�A� =>A� ’emp_queue’,

queue_tableA�A�A�A�A�A�A�A�A�A� =>A� ’emp_queue_tab’);

EXECUTE DBMS_AQADM.start_queue (

queue_nameA�A�A�A�A�A�A�A� => ’emp_queue’,

enqueueA�A�A�A�A�A�A�A�A�A�A� => TRUE);

Enqueue message to queue:

DECLARE

l_enqueue_optionsA�A�A�A� DBMS_AQ.enqueue_options_t;

l_message_propertiesA� DBMS_AQ.message_properties_t;

l_message_handleA�A�A�A�A� RAW(16);

l_event_msgA�A�A�A�A�A�A�A�A�A� emp_msg_type;

BEGIN

l_event_msg := emp_msg_type(‘Leena’, 1, 3, 25, 1000, ‘Kolkata’, 100);

DBMS_AQ.enqueue(queue_nameA�A�A�A�A�A�A�A�A� => ’emp_queue’,

enqueue_optionsA�A�A�A� => l_enqueue_options,

message_propertiesA� => l_message_properties,

payloadA�A�A�A�A�A�A�A�A�A�A�A� => l_event_msg,

msgidA�A�A�A�A�A�A�A�A�A�A�A�A�A� => l_message_handle);

COMMIT;

END;

Dequeue message from queue:

DECLARE

l_dequeue_optionsA�A�A�A� DBMS_AQ.dequeue_options_t;

l_message_propertiesA� DBMS_AQ.message_properties_t;

l_message_handleA�A�A�A�A� RAW(16);

l_event_msgA�A�A�A�A�A�A�A�A�A� emp_msg_type;

BEGIN

DBMS_AQ.dequeue(queue_nameA�A�A�A�A�A�A�A�A� => ’emp_queue’,

dequeue_optionsA�A�A�A� => l_dequeue_options,

message_propertiesA� => l_message_properties,

payloadA�A�A�A�A�A�A�A�A�A�A�A� => l_event_msg,

msgidA�A�A�A�A�A�A�A�A�A�A�A�A�A� => l_message_handle);

DBMS_OUTPUT.put_line (‘Employee Name: ‘ || l_event_msg.name);

DBMS_OUTPUT.put_line (‘Employee ID: ‘ || l_event_msg.id);

DBMS_OUTPUT.put_line (‘Employee Experience: ‘ || l_event_msg.experience);

DBMS_OUTPUT.put_line (‘Employee Age: ‘ || l_event_msg.age);

DBMS_OUTPUT.put_line (‘Employee Salary: ‘ || l_event_msg.salary);

DBMS_OUTPUT.put_line (‘Employee City: ‘ || l_event_msg.city);

DBMS_OUTPUT.put_line (‘Employee Bonus: ‘ || l_event_msg.bonus);

COMMIT;

END;

Now we will implement the same queuing operations in SOA Suite 12C also. Please refer the next article for that.