Oracle Advanced Queuing is a means for building an asynchronous request/response mechanism around a so-called queue and two processes:
ENQUEUE, which puts
MESSAGES into a queue, and
DEQUEUE, which reads the queue.
Advanced queuing provides sophisticated mechanisms for distributing messages across queues and for queue subscription. These mechanisms are all built on top of these basic elements (
With the Event-Driven Publishing API you can use these queues to store and transmit report jobs. You can even build your own queuing mechanism if the one provided with Oracle Reports Services does not fit your needs.
A queue is a table in the database that holds, along with several administrative columns, an object column that represents a message. In our case the message is the parameter list.
dbms_AQadm.Create_Queue_Table to create the physical table in the database. You must pass it a name for the table and a name for the object type that will define the message for this queue.
… execute dbms_AQadm.Create_Queue_Table
(queue_Table=>'queuename._tab', queue_Payload_Type=>'SRW_PARAMLIST_OBJECT', compatible=>'9.0');
In earlier examples, we created the object type
SRW_PARAMLIST_OBJECT that encapsulates the
SRW_PARAMLIST type in object notation so it can be used as a message.
After creating the queue table, you must create the queue with
dbms_AQadm.Create_Queue and start the queue with
… execute dbms_AQadm.Create_Queue
prompt … starting queue execute dbms_AQadm.Start_Queue
Note:You'll find a complete example for setting up, creating, and starting a simple queue in the demo file
srwAQsetup.sql, included with your Oracle Reports Services installation.
Having created and started the queue, what you need now is a procedure that creates a message in this queue and a procedure that reads out the queue and submits the job to the server. These are discussed in the following sections.
The enqueuing procedure is responsible for putting a message into the queue. This procedure can be part of your application, called by a database-trigger, or provided through an external mechanism. In this section, we will provide an example of creating a stored procedure that puts a simple message in this queue.
Because our message is the parameter list itself, the procedure is fairly easy. We use the same code we used in earlier sections to create a parameter list. In addition to the variables we used, we define an
object variable to hold the message we will put into the queue.
… plist_object SRW_ParamList_Object; …
After creating the parameter list we create the actual message object using the object constructor.
… plist_object := SRW_PARAMLIST_OBJECT(plist); …
… dbms_aq.enqueue(queue_name => 'myQueue',
enqueue_options => enqueue_options, message_properties => message_properties, payload => PList_Object, msgid => message_handle);
The message is put into the queue. Because we did not set up any message distribution, the message will stay in the queue until it is fetched by a dequeuing-procedure, which is discussed in the next section.
A dequeuing procedure reads out all available messages in a queue and processes them. In our case, we want to read out the message and submit a job to the server using the parameter list that was attached to the message.
To accomplish this, we follow this example:
dequeue_options.wait := 1;
DBMS_AQ.DEQUEUE(queue_name => 'myQueue',
dequeue_options => dequeue_options, message_properties => message_properties, payload => PList_Object, msgid => message_handle);
COMMIT; plist := plist_object.params; r_jid := SRW.RUN_REPORT(plist);
end loop; exception when aq_timeout then
This code example will read out the queue until all messages have been processed. Time allowed for processing is determined by the timeout defined in the second line of code. This timeout defines the amount of seconds the dequeue procedure should wait for a message before creating a timeout exception.
DBMS_AQ.DEQUEUE built-in is provided by Advanced Queuing for reading out messages. It puts the payload of the message, the object that holds the information, into the object defined by the payload parameter.
plist, we extract the information from the payload object. As mentioned before, our object holds a parameter list. It is stored in the attribute
PARAMS inside the object. The extracted parameter list is then handed over to
SRW.RUN_REPORT for submitting the job.