Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Andrew.

Asked: January 13, 2021 - 4:24 pm UTC

Last updated: January 18, 2021 - 5:54 am UTC

Version: 18c XE

Viewed 100+ times

You Asked

Environment: Oracle 18XE 64-bit for Windows.
I have a question about dequeueing an array of messages from persistent queue.
It’s a simple point-to-point messaging. Queue is “single_consumer”, without propagation.
I registered my PL/SQL callback function.
I need to know an exact size of array of messages to dequeue
in every call of my callback function from Oracle AQ internal job.
And I found the only legal way how to have done it. And this way is to register callback
with qosflags parameter of sys.aq$reg_info equal to dbms_aq.NTFN_QOS_PAYLOAD.
Here is the registration PL/SQL block:
declare 
    v_qosflags number :=  dbms_aq.NTFN_QOS_PAYLOAD;
    r_info SYS.AQ$_REG_INFO;
   begin
      r_info := SYS.AQ$_REG_INFO(
                'STERN.FOUNDERS_QUEUE',
                DBMS_AQ.NAMESPACE_AQ,
                'plsql://stern.dosomecalc',
                HEXTORAW('FF')
                );
       r_info.qosflags := v_qosflags; 
       r_info.ntfn_grouping_class := dbms_aq.NTFN_GROUPING_CLASS_TIME ;  
       r_info.ntfn_grouping_value := 60;
       r_info.ntfn_grouping_type := dbms_aq.NTFN_GROUPING_TYPE_SUMMARY ;    
       
       DBMS_AQ.REGISTER (
          SYS.AQ$_REG_INFO_LIST(
                     r_info
             ),
          1
          );
 end;

Here is the declaration of callback procedure. It is a standard declaration:
create or replace procedure dosomecalc
(context    RAW
                             ,reginfo    SYS.AQ$_REG_INFO
                             ,descr      SYS.AQ$_DESCRIPTOR
                             ,payload    raw
                             ,payloadl   NUMBER)


Now, thankfully to qosflags parameter initialized with dbms_aq.NTFN_QOS_PAYLOAD ,my callback function is registered in such a way that I always can see real size
of messages to dequeue in callback session. It may be evaluated as counting size of descr.msgid_array part of descr parameter.
Without setting of qosflags during registration to some value - this part of descr parameter always comes empty to callback procedure call.
Once I know the real size of messages array , I can use it in
Dbms_aq.dequeue_array(…, array_size => descr.msgid_array.count,…) /*dequeuing call*/.

inside my callback function.
Than, after analyze of contents of descr parameter, I found in it an ntfnsRecdInGrp element,
and decided that ntfnsRecdInGrp is always equal to descr.msgid_array.count,
and just made for programmer’s convenience, just for duplicate descr.msgid_array.count.
AQ documentation says:
msgid_array  - Group notification message ID list
ntfnsRecdInGrp - Notifications received in group

That was why I decided that they are equal by value.
It was a my mistake. When I use callback with array size equal to descr.msgid_array.count
everything is OK. With ntfnsRecdInGrp – no. Sometimes descr.msgid_array.count
and ntfnsRecdInGrp equal to each other, sometimes not.
Now the question is:
What is the meaning of ntfnsRecdInGrp part of descr parameter? Why it is not the same as
Msgid_array.count?

and we said...

and decided that ntfnsRecdInGrp is always equal to descr.msgid_array.count,


Notifications are not directly related to the messages. In most common usages, you get notified about a message and thus they appear to be one-to-one.

From the docs

1.5.3 Notification Grouping by Time
Notification applications may register to receive a single notification for all events that occur within a specified time interval. Notification Clients may specify a start time for the notifications. Additionally, they must specify a time as the grouping class and the time interval as the grouping value.

A repeat count may be used to limit the number of notifications delivered. Clients can receive two types of grouping events, Summary or Last. A summary notification is a list of Message Identifiers of all the messages for the subscription. If last was specified as a grouping type, notification would have information about the last message in the notification interval. A count of the number of messages in the interval is also sent. The registration interfaces in PLSQL and OCI allow for specification of the START_TIME, REPEAT_COUNT, GROUPING CLASS, GROUPING VALUE, GROUPING TYPE in the AQ$_REGISTRATION_INFO and the OCI subscription Handle.


So if you want to know how many messages are in the array, then use the array count.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.