Posts
Create a USER with Administrator Role:
CONNECT / AS SYSDBA
CREATE USER aq_admin IDENTIFIED BY aq_admin DEFAULT TABLESPACE users
GRANT connect TO aq_admin;
GRANT create type TO aq_admin;
GRANT aq_administrator_role TO aq_admin;
ALTER USER aq_admin QUOTA UNLIMITED ON users;
Create a USER with USER role:
CREATE USER aq_user IDENTIFIED BY aq_user DEFAULT TABLESPACE users;
GRANT connect TO aq_user;
GRANT aq_user_role TO aq_user;
Define Payload
The format or structure of a message is called the payload. While creating a queue, we need to tell Oracle the Payload structure.
CONNECT aq_admin/aq_admin
CREATE OR REPLACE TYPE event_msg_type AS OBJECT (
Header_ID NUMBER,
Line_ID NUMBER,
Current_status VARCHAR2(50),
);
/
GRANT EXECUTE ON event_msg_type TO aq_user;
Create Queue Table
Queues are implemented using a queue table which can hold multiple queues with the same payload type.
GRANT EXECUTE ON event_msg_type TO aq_user;
EXECUTE DBMS_AQADM.create_queue_table
( queue_table => 'aq_admin.event_queue_tab',
queue_payload_type => 'aq_admin.event_msg_type'
);
Create Queue
EXECUTE DBMS_AQADM.create_queue
(queue_name => 'aq_admin.event_queue',
queue_table => 'aq_admin.event_queue_tab'
);
Start Queue
EXECUTE DBMS_AQADM.start_queue
(queue_name => 'aq_admin.event_queue',
enqueue => TRUE
);
Grant Privilege to AQ_USER
CONNECT aq_admin/aq_admin
EXECUTE DBMS_AQADM.grant_queue_privilege
( privilege => 'ALL',
queue_name => 'aq_admin.event_queue',
grantee => 'aq_user',
grant_option => TRUE
);
Enqueue Message
Messages can be written to the queue using the
DBMS_AQ.ENQUEUE procedure.
CONNECT aq_user/aq_user
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(1,1,'Entered');
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
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 ('Header ID : ' ||l_event_msg.Header_id);
DBMS_OUTPUT.put_line ('Line ID : ' ||l_event_msg.line_id);
DBMS_OUTPUT.put_line ('Status : ' ||l_event_msg.status);
COMMIT;
END;
/
Posted in: Oracle Advanced Concepts,Oracle Notebook,Technical
0 comments:
Post a Comment