PHP with Oracle AQ

Create PL/SQL Procedure to Enqueue Message

Posted on 16th October 2017

We now create a PL/SQL procedure named enq to Enqueue a message to the salary_review queue that we created in the previous chapter.

The enq procedure take two input parameters employee id and increment percentage. These parameters are used to create the payload of type salary_review. We then pass this payload and the queue name to the ENQUEUE procedure of DBMS_AQ package.

CREATE or REPLACE PROCEDURE enq(empid in number, incr in number) AS
  emp_sal salary_review;
  enqueue_options dbms_aq.enqueue_options_t;
  message_properties dbms_aq.message_properties_t;
  enq_id raw(16);
  emp_sal := salary_review(empid, incr);
  DBMS_AQ.ENQUEUE(queue_name         => 'hr.salary_review_queue',
                  enqueue_options    => enqueue_options,
                  message_properties => message_properties,
                  payload            => emp_sal,
                  msgid              => enq_id);

To test the above procedure, run:


You can then query the Queue table to confirm the message has been added to the queue.

SELECT qt.q_name,qt.user_data.empid, qt.USER_DATA.incr FROM review_queue_tab qt;

Post a comment


Nothing the first to share wisdom.