Similar to the enq procedure we will now create a procedure named deq to Dequeue a message from the salary_review queue. The deq procedure uses the DEQUEUE procedure in DBMS_AQ package to dequeue a message and the payload from that message is assigned to two OUT parameters empid and incr.
CREATE OR REPLACE PROCEDURE deq(empid out number, incr out number) AS
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
emp_sal salary_review;
enq_id raw(16);
BEGIN
dequeue_options.wait := 0;
DBMS_AQ.DEQUEUE(queue_name => 'hr.salary_review_queue',
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => emp_sal,
msgid => enq_id);
empid := emp_sal.empid;
incr := emp_sal.incr;
COMMIT;
END;
To test the above procedure, run:
SET SERVEROUTPUT ON;
DECLARE
empid number(6);
incr number(2);
BEGIN
deq(empid, incr);
DBMS_OUTPUT.PUT_LINE('Employee ID :' || empid);
DBMS_OUTPUT.PUT_LINE('Increment :' || incr);
EXCEPTION
WHEN OTHERS THEN
IF sqlcode = -25228 THEN
DBMS_OUTPUT.PUT_LINE('No more messages in queue');
END IF;
END;
Nothing yet..be the first to share wisdom.