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