Oracle AQ Setup

PROMPT "Start of Create_BPEL_Q_Subscriptions.sql"

set serveroutput on;

/

declare

  l_subscriberName varchar2(1024) := 'ICSTUDENT';

  l_rule varchar2(4096) := 'tab.user_data.event_name IN (''ic.oracle.apps.ar.hz.PersonBO.create'',''ic.oracle.apps.ar.hz.PersonBO.update'',''ic.oracle.apps.ar.hz.PersonCustBO.update'',''ic.oracle.apps.ar.hz.PersonContBO.sync'',''ic.oracle.apps.per.personTypeUsage.Sync'')';

  l_packagename                 VARCHAR2(4096) := 'APPS.ICSOA_STUDENT_PKG';

  l_procedurename               VARCHAR2(4096) := 'EventSubscriber';

 

 

    l_subscriber       sys.aq$_agent;

    l_queueNameSHORT varchar2(1024) := 'WF_BPEL_Q';

    l_queueName varchar2(1024) := 'APPS.' || l_queueNameSHORT;

 

  l_reginfo             sys.aq$_reg_info;

  l_reg_list            sys.aq$_reg_info_list;

 

 

  cursor c_subscriptions(p_consumer_name in varchar2,p_queue_name in varchar2) is

    select s.*

    from "SYS"."DBA_QUEUE_SUBSCRIBERS" s

    where s.queue_name=p_queue_name

    and s.consumer_name=p_consumer_name

  ;

  TYPE t_subscriptions IS TABLE OF c_subscriptions%ROWTYPE INDEX BY PLS_INTEGER;  

  l_subscriptions t_subscriptions;

  l_cur number;  

 

  cursor c_regs(p_consumer_name in varchar2,p_queue_name in varchar2) is

    select r.*

    from "SYS".DBA_SUBSCR_REGISTRATIONS r

    where r.subscription_name like '%' || p_queue_name || '%:%' || p_consumer_name || '%'

  ;

  TYPE t_regs IS TABLE OF c_regs%ROWTYPE INDEX BY PLS_INTEGER;  

  l_regs t_regs;

  l_curReg number;  

 

  l_num number;

begin

  DBMS_OUTPUT.ENABLE(1000000);

  dbms_output.put_line('START Create_BPEL_Q_Subscriptions.sql');

 

  open c_subscriptions(l_subscriberName, l_queueNameSHORT);

  fetch c_subscriptions bulk collect into l_subscriptions;

  close c_subscriptions;

 

  l_cur := l_subscriptions.FIRST;

  while (l_cur is not null) LOOP

    dbms_output.put_line('Deleting Subscription ' || l_subscriptions(l_cur).consumer_name);

     l_subscriber := sys.aq$_agent(l_subscriptions(l_cur).consumer_name,NULL, NULL);

     DBMS_AQADM.REMOVE_SUBSCRIBER(

        queue_name => l_queueName,

        subscriber => l_subscriber

      );

     

        l_cur := l_subscriptions.NEXT(l_cur);

  END LOOP; --Loop through subs  

 

  dbms_output.put_line('Deleting Registrations');

  open c_regs(l_subscriberName, l_queueNameSHORT);

  fetch c_regs bulk collect into l_regs;

  close c_regs;

  l_curReg := l_regs.FIRST;

  while (l_curReg is not null) LOOP

        dbms_output.put_line('Deleting Registration - ' || l_regs(l_curReg).subscription_name || '-' || l_regs(l_curReg).location_name);

        l_reginfo := sys.aq$_reg_info(

                            l_regs(l_curReg).subscription_name,

                            DBMS_AQ.NAMESPACE_AQ,

                            l_regs(l_curReg).location_name,

                            l_regs(l_curReg).user_context

                      );

        l_reg_list  := sys.aq$_reg_info_list(l_reginfo);  

        dbms_aq.unregister(l_reg_list,1);  

       

        l_curReg := l_regs.NEXT(l_curReg);

  END LOOP; --Loop through regs  

   

  --Check there are no registrations

  select count('x')

  into l_num

  from "SYS".DBA_SUBSCR_REGISTRATIONS

  where subscription_name like '%' || l_queueNameSHORT || '%:%' || l_subscriberName || '%'  

  ;

  if l_num<>0 then

    raise_application_error(-20000,'ERROR Registraiton exists (should have been deleted):' || to_char(l_num));

  end if;

 

  --check there are no subscribers

  select count('x')

  into l_num

  from "SYS"."DBA_QUEUE_SUBSCRIBERS"

  where queue_name=l_queueNameSHORT

  and consumer_name=l_subscriberName  

  ;

  if l_num<>0 then

    raise_application_error(-20000,'ERROR Subscription exists (should have been deleted):' || to_char(l_num));

  end if;

 

 

    dbms_output.put_line('Adding Subscription ' || l_subscriberName);

    dbms_output.put_line('  for rule ' || l_rule);

   l_subscriber := sys.aq$_agent(l_subscriberName,NULL, NULL);

   DBMS_AQADM.ADD_SUBSCRIBER (

      queue_name => l_queueName,

      subscriber => l_subscriber,

      rule => l_rule

    );

  l_reginfo := sys.aq$_reg_info(

                      l_queueName || ':' || l_subscriberName,

                      DBMS_AQ.NAMESPACE_AQ,

                            'plsql://' || l_packagename || '.' || l_procedurename || '?PR=0',

                      HEXTORAW('FF')

                );

  l_reg_list  := sys.aq$_reg_info_list(l_reginfo);  

    dbms_output.put_line('Adding Registration plsql://' || l_packagename || '.' || l_procedurename || '?PR=0');

  dbms_aq.register(l_reg_list,1);  

 

 

  --Check there is 1 registrations

  select count('x')

  into l_num

  from "SYS".DBA_SUBSCR_REGISTRATIONS

  where subscription_name like '%' || l_queueNameSHORT || '%:%' || l_subscriberName || '%'  

  ;

  if l_num<>1 then

    raise_application_error(-20000,'ERROR Registraiton not created:' || to_char(l_num));

  end if;

 

  --check there is 1 subscribers

  select count('x')

  into l_num

  from "SYS"."DBA_QUEUE_SUBSCRIBERS"

  where queue_name=l_queueNameSHORT

  and consumer_name=l_subscriberName  

  ;

  if l_num<>1 then

    raise_application_error(-20000,'ERROR Subscription not created:' || to_char(l_num));

  end if;  

 

  dbms_output.put_line('END Create_BPEL_Q_Subscriptions.sql');

  commit;

end;

/

PROMPT "End of Create_BPEL_Q_Subscriptions.sql"