Script categories

Friday, 21 October 2011

PL/SQL AQ Notification


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