CREATE ROLE my_aq_adm_role;
CREATE ROLE my_aq_user_role;
begin
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(privilege => 'ENQUEUE_ANY',grantee => 'my_aq_user_role',admin_option => FALSE);
end;
begin
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'DEQUEUE_ANY',
grantee => 'my_aq_user_role',
admin_option => FALSE);
end;
create tablespace sp_foraq datafile 'aq_tbs.dbf' size 50 M;
---AQ Admin
CREATE USER aqadm IDENTIFIED BY aqadm DEFAULT TABLESPACE sp_foraq TEMPORARY TABLESPACE temp;
GRANT my_aq_adm_role TO aqadm;
GRANT connect, resource TO aqadm;
--AQ User
CREATE USER aquser IDENTIFIED BY aquser DEFAULT TABLESPACE sp_foraq TEMPORARY TABLESPACE temp;
grant connect,resource to aquser;
GRANT my_aq_user_role TO aquser;
GRANT EXECUTE ON dbms_aqadm to aqadm;
--change user.
CONNECT aqadm/aqadm;
CREATE TYPE queue_message_type AS OBJECT(
no NUmBER,
title VARCHAR2(30),
text VARCHAR2(2000)
);
GRANT EXECUTE ON queue_message_type TO my_aq_user_role;
GRANT EXECUTE ON DBMS_AQ TO my_aq_user_role;
begin
DBMS_AQADM.create_queue_table(queue_table => 'queue_message_table',queue_payload_type => 'queue_message_type');
end;
begin
DBMS_AQADM.create_queue(queue_name => 'message_queue',queue_table => 'queue_message_table');
end;
begin
dbms_aqadm.start_queue(queue_name => 'message_queue');
end;
/* use to delete
begin
dbms_aqadm.stop_queue(queue_name => 'message_queue');
dbms_aqadm.drop_queue(queue_name => 'message_queue');
dbms_aqadm.drop_queue_table(queue_table => 'queue_message_table');
end;
*/
CONNECT aquser/aquser;
--enqueue
DECLARE
queue_options DBMS_AQ.enqueue_options_t;
message_properties DBMS_AQ.message_properties_t;
message_id
RAW(16);
my_message aqadm.queue_message_type;
BEGIN
my_message := aqadm.queue_message_type(1,
'This is a sample message',
'This message has been posted on' || to_char(SYSDATE,'DD.MM.YYYY HH24:MI:SS'));
DBMS_AQ.enqueue(queue_name => 'aqadm.message_queue',
enqueue_options => queue_options,
message_properties => message_properties,
payload => my_message,
msgid => message_id);
COMMIT;
END;
--dequeue
DECLARE
queue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
message_id RAW(2000);
my_message aqadm.queue_message_type;
BEGIN
DBMS_AQ.DEQUEUE(
queue_name => 'aqadm.message_queue',
dequeue_options => queue_options,
message_properties => message_properties,
payload => my_message,
msgid => message_id);
COMMIT;
DBMS_OUTPUT.PUT_LINE(
'Dequeued no: ' || my_message.no);
DBMS_OUTPUT.PUT_LINE(
'Dequeued title: ' || my_message.title);
DBMS_OUTPUT.PUT_LINE(
'Dequeued text: ' || my_message.text);
END;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21818314/viewspace-622635/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21818314/viewspace-622635/