Beware of the following bugs when using PL/SQL AQ Notifications:
Available on Metalink:
11g
1. Bug 2556939: AQ PL/SQL NOTIFICATION DOES
NOT WORK WHEN JOB_QUEUE_PROCESSES<4
10g and 11g
1. AQ PL/SQL Notification reporting ORA-06502 when schema.queue_name
length is greater than 30 characters [ID 465220.1]
2. AQ PL/SQL Notification No Longer Work Due To "register_driver()"
Jobs Not Terminating [ID 331372.1]
3. Procedure invoked by AQ PL/SQL notification is executed as SYS not
the Enqueuer [ID 552771.1]
10g only
1. Natively Compiled PL/SQL is not executed properly when using Advanced
Queueing PL/SQL Notification in a RAC environment [ID 434024.1]
2. ORA-25263 produced in Job Queue Process trace files when using AQ
PL/SQL Notification [ID 302659.1]
Read this for a better understanding of PL/SQL notification.
1. What Is The Advanced Queuing Asynchronous
Notification Feature[ID 235397.1]
variable queue_user varchar2(30);
-- MODIFY THE USERNAME TO MATCH THE SCHEMA to test on
exec :queue_user := '&Schema_name';
Ensure schema has the AQ_ADMINISTRATOR_ROLE and the AQ_USER_ROLE granted from SYS.
Create Queue first
/* Create a user defined data type first */
create or replace type ty_Msg as object
(
id number
,msg varchar2(100)
);
/
set serveroutput on
begin
dbms_aqadm.create_queue_table
(queue_table => 't_aq_ems_queue'
,queue_payload_type => 'ty_Msg'
,multiple_consumers => true
,comment => 'Multiconsumer queue table');
dbms_output.put_line('Queue table T_AQ_EMS_QUEUE created');
exception
when others then
dbms_output.put_line('Failure ' || sqlerrm);
end;
/
set serveroutput on
begin
dbms_aqadm.create_queue(queue_name => 'aq_ems_queue'
,queue_table => 't_aq_ems_queue');
dbms_output.put_line('Queue AQ_EMS_QUEUE sucessfully created');
exception
when others then
dbms_output.put_line('Failure ' || sqlerrm);
end;
/
Create the Notification Routines next
-- Create dummy log table to prove our Notification routines were
-- successfully called, by inserting into LOG_MSG table within
-- each routine.
create table log_msg (msg varchar2(200)
,t timestamp default current_timestamp)
/
-- Both of the following two procedures, once registered, will
-- automatically be invoked when a successful en-queue occurs.
create or replace procedure
notify_Proc1( context raw
,reginfo sys.aq$_reg_info
,descr sys.aq$_descriptor
,payload raw
,payloadl number) as
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle raw(16);
message ty_Msg;
begin
dequeue_options.msgid := descr.msg_id;
dequeue_options.consumer_name := descr.consumer_name;
dbms_aq.dequeue(queue_name => descr.queue_name,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
insert into Log_Msg (Msg)
values (Message.Msg || ' ' || reginfo.name);
commit;
end notify_Proc1;
/
create or replace procedure notify_Proc2
( context raw
,reginfo sys.aq$_reg_info
,descr sys.aq$_descriptor
,payload raw
,payloadl number) as
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle raw(16);
message ty_Msg;
begin
dequeue_options.msgid := descr.msg_id;
dequeue_options.consumer_name := descr.consumer_name;
dbms_aq.dequeue(queue_name => descr.queue_name,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
insert into Log_Msg (Msg)
values (Message.Msg || ' ' || reginfo.name);
commit;
end notify_Proc2;
/
set serveroutput on
declare
l_Proc_Name1 varchar2(30) := 'notify_Proc1';
l_Proc_Name2 varchar2(30) := 'notify_Proc2';
begin
dbms_aq.register
(sys.aq$_reg_info_list
(sys.aq$_reg_info(:queue_user || '.aq_ems_queue:SUB1'
,dbms_aq.namespace_aq
,'plsql://' || :queue_user ||'.'|| l_Proc_Name1
,hextoraw('FF')
)
, sys.aq$_reg_info(:queue_user || '.aq_ems_queue:SUB2'
,dbms_aq.namespace_aq
,'plsql://' || :queue_user ||'.'|| l_Proc_Name2
,hextoraw('FF') )
)
, 2); -- Indicates number of notifiers
dbms_output.put_line('Procedures '
|| l_Proc_Name1
|| ' and '
|| l_Proc_Name2
|| ' have been registered');
end;
/
Finally, the test
Ensure the queue is started first
exec dbms_aqadm.start_queue(queue_name=>:queue_user||'.aq_ems_queue');SQL> truncate table log_msg;
Table truncated.
SQL> set serveroutput on
SQL> declare
2 enqueue_options dbms_aq.enqueue_options_t;
3 message_properties dbms_aq.message_properties_t;
4 message_handle raw(16);
5 message ty_Msg :=ty_Msg(1,'test 1 AQ demo 2');
6 begin
7
8 dbms_aq.enqueue
9 (queue_name => :queue_user||'.aq_ems_queue'
10 ,enqueue_options => enqueue_options
11 ,message_properties => message_properties
12 ,payload => message
13 ,msgid => message_handle);
14
15 commit;
16
17 dbms_output.put_line('Message sucessfully enqueued ' );
18
19 exception
20 when others then
21 dbms_output.put_line('Failure ' || sqlerrm);
22 end;
23 /
Message sucessfully enqueued
PL/SQL procedure successfully completed.
SQL> select * from log_msg;
MSG T
------------------------------------------------- -------------------------
test 1 AQ demo 2 "XDB_DEV4"."AQ_EMS_QUEUE":"SUB2" 21-OCT-11 16.44.36.782000
test 1 AQ demo 2 "XDB_DEV4"."AQ_EMS_QUEUE":"SUB1" 21-OCT-11 16.44.36.797000
No comments:
Post a Comment