PHP with Oracle AQ

Creating Oracle Advanced Queues

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.

Message

A message is the smallest unit of information inserted into and retrieved from a queue. A message consists of the following:

  • Control information (metadata)
  • Payload (data)

Queue

A queue is a repository for messages.

Queue Table

Queues are stored in queue tables. Each queue table is a database table and contains one or more queues.

Creating the Salary Review Queue

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

Create Object Type

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

Create Queue Table

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;

Create Queue

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;

Start Queue

Finally you can start the queue:

begin
 DBMS_AQADM.START_QUEUE(
   Queue_name  => 'hr.salary_review_queue',
   Enqueue     => true);
end;


Post a comment

Name

Your Comment

Email (We dont publish it)

Comments

Nothing yet..be the first to share wisdom.