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 184.108.40.206.0 – Production)
SQL Developer 3.2.10.09
SQL Plus 220.127.116.11
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 ( id NUMBER(10), title VARCHAR2(100), details VARCHAR2(4000), email_address VARCHAR2(1000) ) /
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.
BEGIN dbms_aqadm.create_queue_table ( queue_table => 'feedback_queue_table', queue_payload_type => 'q_message', comment => 'Creating feedback queue table' ); END; /
With the queue table in place, the queue can now be created. This is achieved by running:
BEGIN dbms_aqadm.create_queue ( queue_name => 'feedback_queue', queue_table => 'feedback_queue_table', comment => 'feedback queue' ); END; /
Once created, the new feedback queue can be started by running:
BEGIN dbms_aqadm.start_queue ( queue_name => 'feedback_queue' ); END; /
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 ) IS lkv_queue_name CONSTANT VARCHAR2(60) := 'aquser.feedback_queue'; lt_eopt dbms_aq.enqueue_options_t; lt_mprop dbms_aq.message_properties_t; lo_the_feedback q_message; lt_enq_msgid RAW(16); BEGIN dbms_aq.enqueue ( queue_name => lkv_queue_name, enqueue_options => lt_eopt, message_properties => lt_mprop, payload => p_feedback, msgid => lt_enq_msgid ); COMMIT; END feedback_nq; /
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:
select t.user_data 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:
DECLARE lt_feedback aquser.q_message; BEGIN lt_feedback := aquser.q_message ( id => 1, title => 'Compliment', details => 'Sample details', email_address => 'email@example.com' ); feedback_nq ( p_feedback => lt_feedback ); END;
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 GRANT EXECUTE ON q_message to
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 ) IS lo_feedback_message aquser.q_message; BEGIN lo_feedback_message := aquser.q_message ( id => feedback_seq.nextval, title => pv_title, details => pv_details, email_address => pv_email_address ); aquser.feedback_nq ( p_feedback => lo_feedback_message ); END add_feedback;
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.
add_feedback ( 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.