Skip to Main Content


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$reg_info equal to dbms_aq.NTFN_QOS_PAYLOAD.
Here is the registration PL/SQL block:
    v_qosflags number :=  dbms_aq.NTFN_QOS_PAYLOAD;
    r_info SYS.AQ$_REG_INFO;
      r_info := SYS.AQ$_REG_INFO(
       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 ;    

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

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


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