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 11.2.0.1.0 – Production)
SQL Developer 3.2.10.09
SQL Plus 11.2.0.1
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 => 'oracle@oracle.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.