0 Replies Latest reply: Dec 27, 2012 2:46 PM by 981907 RSS

    Notification Grouping

    981907
      We are trying to implement notification grouping in 11.2.0.2. We can update thousands of rows at a time and do not want thousands of notifications. Our first attempt failed because we were trying:

      declare
      reginfo sys.aq$_reg_info;
      reg_list sys.aq$_reg_info_list;
      begin
      reginfo := sys.aq$_reg_info(
      name => 'CASHFEED_QUEUE',
      namespace => dbms_aq.namespace_aq,
      callback => 'http://xx.xx.x.xxx/CBOracleService/ServiceCBDataImporter/ProcessImport/CashFeed',
      context => HEXTORAW('FF'),
      qosflags => dbms_aq.NTFN_QOS_RELIABLE,
      timeout => 0,
      ntfn_grouping_class => dbms_aq.NTFN_GROUPING_CLASS_TIME,
      ntfn_grouping_value => 60,
      ntfn_grouping_type => dbms_aq.NTFN_GROUPING_TYPE_LAST,
      ntfn_grouping_start_time => sysdate,
      ntfn_grouping_repeat_count => dbms_aq.NTFN_GROUPING_FOREVER
      ) ;
      reg_list := sys.aq$_reg_info_list(reginfo);
      dbms_aq.register(reg_list => reg_list,
      reg_count => 1);
      commit;
      end;
      /

      and we received:

      ORA-24943: cannot use ntfn grouping with this receive protocol, presentation and/or QoS

      A Google search showed that we can't group with HTTP callback, NTFN_QOS_RELIABLE or XML payloads (all of which we used). So, we converted to:

      declare
      reginfo sys.aq$_reg_info;
      reg_list sys.aq$_reg_info_list;
      begin
      reginfo := sys.aq$_reg_info(
      name => 'CASHFEED_QUEUE',
      namespace => dbms_aq.namespace_aq,
      callback => 'plsql://BN06U.SP_AQ_CASHFEED_NOTIFICATION?PR=0',
      context => HEXTORAW('FF'),
      qosflags => dbms_aq.NTFN_QOS_PAYLOAD,
      timeout => 0,
      ntfn_grouping_class => dbms_aq.NTFN_GROUPING_CLASS_TIME,
      ntfn_grouping_value => 60,
      ntfn_grouping_type => dbms_aq.NTFN_GROUPING_TYPE_LAST,
      ntfn_grouping_start_time => sysdate,
      ntfn_grouping_repeat_count => dbms_aq.NTFN_GROUPING_FOREVER
      ) ;
      reg_list := sys.aq$_reg_info_list(reginfo);
      dbms_aq.register(reg_list => reg_list,
      reg_count => 1);
      commit;
      end;
      /

      Notice the change from HTTP to PLSQL, NTFN_QOS_RELIABLE to NTFN_QOS_PAYLOAD, plus we changed the payload type from XML to RAW in the queue table. In the PL/SQL we utilize UTL_HTTP to hit the WCF.

      However, we still get ORA-24943: cannot use ntfn grouping with this receive protocol, presentation and/or QoS
      ORA-06512: at "SYS.DBMS_AQ", line 737
      ORA-06512: at line 19

      Any ideas why we are still getting this error? Thank you.

      Edited by: user1142241 on Dec 27, 2012 12:45 PM