This discussion is archived
4 Replies Latest reply: Dec 27, 2012 8:20 AM by 981907 RSS

Notification grouping

981907 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points