Monthly Archives: February 2013

Queuing for Oracle APEX (Part 2)

In Part 1 of this article I described the steps required to first create a queue and then how to enqueue messages using server side PL/SQL called from an Apex application.

In part 2 I will describe the steps required to dequeue the messages and how they may be displayed using Apex.

The first step is to write the procedure that will dequeue the messages from the queue. This procedure should be created in the same schema as the Queue Owner and the execute privilege should be given to Apex user.

CREATE OR REPLACE PROCEDURE feedback_dq
IS

   lkv_queue_name        CONSTANT VARCHAR2(60) := 'aquser.feedback_queue';
   lt_dq_opt             dbms_aq.dequeue_options_t;
   lt_mprop              dbms_aq.message_properties_t;
   lo_the_feedback       q_message;
   lt_deq_msgid          RAW(16);
   lb_further_feedback   BOOLEAN := TRUE;

   le_no_messages        EXCEPTION;
   PRAGMA EXCEPTION_INIT(le_no_messages, -25228);

BEGIN

   lt_dq_opt.wait := DBMS_AQ.NO_WAIT;

   WHILE lb_further_feedback
   LOOP
      BEGIN
         dbms_aq.dequeue
         (
            queue_name         => lkv_queue_name,
            dequeue_options    => lt_dq_opt,
            message_properties => lt_mprop,
            payload            => lo_the_feedback,
            msgid              => lt_deq_msgid
         );

         INSERT INTO feedback(id,
                              title,
                              details,
                              email_address,
                              date_time_created)
         VALUES(lo_the_feedback.ID,
                lo_the_feedback.title,
                lo_the_feedback.details,
                lo_the_feedback.email_address,
                SYSDATE);

         COMMIT;

      EXCEPTION

         WHEN le_no_messages THEN

            lb_further_feedback := FALSE;

      END;

   END LOOP;

END feedback_dq;

The procedure used to dequeue the messages is straight forward so I will only explain the salient points.

The wait parameter of the dequeue option is set to NO_WAIT (Line 16). This ensures that if there are no messages, control returns from dbms_aq.dequeue.

dbms_aq.dequeue is called (line 21) and if there is a message its contents are inserted into the feedback table.

When there are no more messages and because NO_WAIT has been specified dbms_aq.dequeue raises an ORA-25228. This is handled in the exception section which sets the boolean controlling the loop false and allows the procedure to complete.

With the dequeue procedure in place all that is left is to create an Apex page that will call the feedback_dq procedure and display the contents in a report on the same page.

Here is the page I created. The “Get Feedback button” simply calls the procedure feedback_dq and the report is based on the feedback table. So before any messages have been dequeued the page looks like this:

Untitled

and then after the “Get Feedback” button had been pressed, the dequeued messages are displayed in a report:

Untitled

Summary

In this two part article I have shown how you get started with using Oracle Streams Advanced Queuing with Application Express.

Acknowledgements and further reading:

Oracle Developer.net introduction to advanced queuing

Oracle Streams Advanced Queuing User’s Guide

Queuing for Oracle APEX (Part 1)

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 => '[email protected]'
                  );
  
   feedback_nq
   (
      p_feedback => lt_feedback
   );
  
END;      

Once run, a message will be enqueued. This can be confirmed by querying the feedback queue table:

Untitled

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

Untitled

When user presses Submit they see the following screen:

Untitled

If we run our query against the feedback queue table you can see that the message from Apex is has been enqueued:

Untitled

In part 2 I will show how to dequeue these messages and display them in an Apex report.