PHP with Oracle AQ

Create PL/SQL Procedure to Enqueue Message

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);
BEGIN
  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);
  COMMIT;
END;

To test the above procedure, run:

begin 
  enq(101,14);
end;

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

Name

Your Comment

Email (We dont publish it)

Comments

Nothing yet..be the first to share wisdom.