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:
and then after the “Get Feedback” button had been pressed, the dequeued messages are displayed in a report:
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: