4 Replies Latest reply: Dec 27, 2012 10:20 AM by 981907 RSS

    Notification grouping

    981907
      I am having trouble with notification grouping. This works but sends notification on every enqueue:

      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')
      ) ;
      reg_list := sys.aq$_reg_info_list(reginfo);
      dbms_aq.register(reg_list => reg_list,
      reg_count => 1);
      commit;
      end;
      /

      When I try to add the grouping it will not compile:

      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'),
      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;
      /

      results in:

      ORA-06550: line 5, column 16:
      PLS-00306: wrong number or types of arguments in call to 'AQ$_REG_INFO'
      ORA-06550: line 5, column 5:
      PL/SQL: Statement ignored

      I am using Oracle 11.2.0.2. Thank you.

      Edited by: user1142241 on Dec 27, 2012 7:21 AM
        • 1. Re: Notification grouping
          758358
          Hi,

          The methods in AQ$_REG_INFO are overloaded, if you only pass NAME, NAMESPACE, CALLBACK, CONTEXT then it will work, but when you add the NTFN* parameters the method expects values for TIMEOUT and QOSFLAGS too, hence the PLS-00306: wrong number or types of arguments in call to 'AQ$_REG_INFO'

          Pass values for these 2 parameters and it should work.

          quosflags - Can be set to one or more of the following values to specify the notification quality of service:
          NTFN_QOS_RELIABLE- This value specifies that reliable notification is required. Reliable notifications persist across instance and database restarts.

          NTFN_QOS_PAYLOAD - This value specifies that payload delivery is required. It is supported only for client notification and only for RAW queues.

          NTFN_QOS_PURGE_ON_NTFN - This value specifies that the registration is to be purged automatically when the first notification is delivered to this registration location.

          timeout - Specifies an automatic expiration period for the registration. If you want no timeout, then set this attribute to 0.

          HTH,

          Paul
          • 2. Re: Notification grouping
            981907
            Paul,

            Thank you for your quick reply. While I was attempting to get this to work, I played with that as I suspected it might be something like that. However, I receive the same result with:

            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'),
            quosflags => 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;
            /


            ORA-06550: line 5, column 16:
            PLS-00306: wrong number or types of arguments in call to 'AQ$_REG_INFO'
            ORA-06550: line 5, column 5:
            PL/SQL: Statement ignored
            • 3. Re: Notification grouping
              758358
              If this is copied and pasted straight from your terminal then it looks like you have a typo:
              user1142241 wrote:
              context => HEXTORAW('FF'),
              quosflags => dbms_aq.NTFN_QOS_RELIABLE, <<<< HERE - should be qosflags - not qUosflags
              timeout => 0,
              Thanks
              Paul
              • 4. Re: Notification grouping
                981907
                Paul,

                Thank you again for another quick reply. Thanks for catching that. I copied from your first post but I should have looked into that before posting again. I am now getting a different error:

                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

                A Google search indicates that grouping is not supported with HTTP only OCI and PL/SQL. I will try UTL_HTTP within PL/SQL. Looks like I have more work to do. Thank you for your time Paul.