The DBMS_AQADM package is also used to
grant privileges on queues so that other users can access them:
CONNECT aq_admin/aq_admin@tsh9i
EXECUTE DBMS_AQADM.grant_queue_privilege ( -
privilege => 'ALL', -
queue_name => 'aq_admin.event_queue', -
grantee => 'aq_user', -
grant_option => FALSE);
At this point the queue can be used for enqueue and dequeue
operations by the AQ_USER user.
Enqueue Message
Messages can be written to the queue using the DBMS_AQ.ENQUEUE
procedure:
CONNECT aq_user/aq_user@tsh9i
DECLARE
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_event_msg AQ_ADMIN.event_msg_type;
BEGIN
l_event_msg := AQ_ADMIN.event_msg_type('REPORTER', 1, 2);
DBMS_AQ.enqueue(queue_name => 'aq_admin.event_queue',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_event_msg,
msgid => l_message_handle);
COMMIT;
END;
/
Dequeue Message
Messages can be read from the queue using the DBMS_AQ.DEQUEUE
procedure:
CONNECT aq_user/aq_user@tsh9i
SET SERVEROUTPUT ON
DECLARE
l_dequeue_options DBMS_AQ.dequeue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_event_msg AQ_ADMIN.event_msg_type;
BEGIN
DBMS_AQ.dequeue(queue_name => 'aq_admin.event_queue',
dequeue_options => l_dequeue_options,
message_properties => l_message_properties,
payload => l_event_msg,
msgid => l_message_handle);
DBMS_OUTPUT.put_line ('Event Name : ' ||
l_event_msg.name);
DBMS_OUTPUT.put_line ('Event Current Status: ' ||
l_event_msg.current_status);
DBMS_OUTPUT.put_line ('Event Next Status : ' ||
l_event_msg.next_status);
COMMIT;
END;
/
Variations
The DEQUEUE_OPTIONS_T , DEQUEUE_OPTIONS_T
and MESSAGE_PROPERTIES_T types can be used to vary the way
messages are enqueued and dequeued. This is where the real flexibility
of advanced queuing becomes evident. The discussion of these options is
beyond the scope of this article. |
Webmaster Said:
Thank you.