Oracle Advanced Queuing (AQ) is a database integrated message queuing infrastructure that allows different distributed applications to share information asynchronously. This messaging infrastructure consists of the following components.
A message is the smallest unit of information inserted into and retrieved from a queue. A message consists of the following:
A queue is a repository for messages.
Queues are stored in queue tables. Each queue table is a database table and contains one or more queues.
Before you can create the Advanced Queues, you need to grant the hr user necessary privileges to create and manage the Queues. The predefined role AQ_ADMINISTATOR_ROLE gives an Oracle user, privileges to create and administer Queues and Queue tables and EXECUTE privileges on Advanced Queuing packages.
Log in to your Oracle database as SYS user and execute the following statement:
GRANT AQ_ADMINISTRATOR_ROLE TO hr;
Now log back in as hr user and continue the rest of the sections
First of all you create an Object Type which defines the content of your message payload.
CREATE OR REPLACE TYPE salary_review AS OBJECT ( empid NUMBER(6), incr NUMBER(2) );
Next we create a Queue table to hold the queue. A queue table can hold multiple queues with the same payload type. The following creates a Queue Table review_queue_tab which can have queues with payload type salary_review.
BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE( Queue_table => 'hr.review_queue_tab', Queue_payload_type => 'hr.salary_review'); END;
After creating the Queue Table you can create the queue. The following statement creates the salary_review_queue and specifies that this queue must be kept in the review_queue_tab Queue Table.
BEGIN DBMS_AQADM.CREATE_QUEUE( Queue_name => 'hr.salary_review_queue', Queue_table => 'hr.review_queue_tab', ); END;
Finally you can start the queue:
begin DBMS_AQADM.START_QUEUE( Queue_name => 'hr.salary_review_queue', Enqueue => true); end;