In this first of a two part article I will demonstrate the use of Oracle Streams Advanced Queuing (AQ) with Application Express. The first part will cover the creating a new queue and adding a message to the queue. Whilst the second part will concentrate on how to dequeue messages.
In building the demonstration I have used:
Oracle Database 11gR2 (Enterprise Edition Release 126.96.36.199.0 – Production)
SQL Developer 3.2.10.09
SQL Plus 188.8.131.52
Application Express 4.2
For this example, the requirement is that we want to gather feedback from our users but we want to process that feedback at a time when the system is idle or has spare capacity. As with any requirement there are many different ways that this could be achieved but for this example I will be using AQ.
AQ is a big subject and can become complex very quickly. In order to keep the examples simple, the AQ objects created and manipulated within these article are done so with the minimum options used.
I want to keep all my AQ related objects separate from the schema where my application is run from so the first step is to create a new user and then give that user certain roles and privileges.
CREATE USER aquser IDENTIFIED BY aquser
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE TO aquser
GRANT EXECUTE ON dbms_aq TO aquser
GRANT EXECUTE ON dbms_aqadm to aquser
I now log in to the database as aquser and create a schema level object type. This object will be referenced when the queue is created and will be used to store the feedback as a message on the queue.
CREATE TYPE q_message AS OBJECT
With the object type created, the next step is to create the queue table. The queue table is used to store the messages until they are dequeued. It is created via the api dbms_aqadm.create_queue_table. The parameters show the name of the queue table and the reference to the object that was created in the previous step.
queue_table => 'feedback_queue_table',
queue_payload_type => 'q_message',
comment => 'Creating feedback queue table'
With the queue table in place, the queue can now be created. This is achieved by running:
queue_name => 'feedback_queue',
queue_table => 'feedback_queue_table',
comment => 'feedback queue'
Once created, the new feedback queue can be started by running:
queue_name => 'feedback_queue'
Now that the Feedback queue has been created and started, it is ready for messages to be enqueued. To do that I have created a feedback_nq procedure which in essence is just a wrapper for the dbms_aq.enqueue procedure. Feedback_nq accepts one parameter, which is of the same type used in creating the Feedback queue:
CREATE OR REPLACE PROCEDURE feedback_nq
p_feedback IN q_message
lkv_queue_name CONSTANT VARCHAR2(60) := 'aquser.feedback_queue';
queue_name => lkv_queue_name,
enqueue_options => lt_eopt,
message_properties => lt_mprop,
payload => p_feedback,
msgid => lt_enq_msgid
I can now run a pl/sql anonymous block to test this procedure. Before I run the test I check the the queue table is empty:
from aq$feedback_queue_table t
This query completes successfully but returns zero rows because there are now messages on the queue. So lets add a message:
lt_feedback := aquser.q_message
id => 1,
title => 'Compliment',
details => 'Sample details',
email_address => '[email protected]'
p_feedback => lt_feedback
Once run, a message will be enqueued. This can be confirmed by querying the feedback queue table:
Because the Application that will ultimately call the procedure is in another schema, grants are required on the feedback_nq procedure and the object used to contain the message.
GRANT EXECUTE ON feedback_nq to <<schema your apex application can see>>
GRANT EXECUTE ON q_message to <<schema your apex application can see>>
All the objects required by the new user AQuser have now been created so we can now move on to creating a couple of supporting objects for our Apex interface.
Start a new database session, this time logging into the application schema create the following objects.
CREATE SEQUENCE feedback_seq
CREATE OR REPLACE PROCEDURE add_feedback
pv_title IN VARCHAR2,
pv_details IN VARCHAR2,
pv_email_address IN VARCHAR2
lo_feedback_message := aquser.q_message
id => feedback_seq.nextval,
title => pv_title,
details => pv_details,
email_address => pv_email_address
p_feedback => lo_feedback_message
The procedure assigns the incoming parameters to the queue object type and once done it calls the feedback_nq procedure.
The final step is to call this procedure from our Apex application.
I created a new Apex database application, added two HTML pages. On page one I added several form text items and a button. Under page processing I created a new process called add feedback which contains a call to the procedure and passes it the values of the page items.
pv_title => P1_HEADING,
pv_details => P1_FEEDBACK,
pv_email_address => P1_EMAIL
The screen looks like this:
When user presses Submit they see the following screen:
If we run our query against the feedback queue table you can see that the message from Apex is has been enqueued:
In part 2 I will show how to dequeue these messages and display them in an Apex report.