We create a brand new oracle multi-consumer queue. We have en-queued the messages into the queue and are processed successfully.
But when we checked the STATE Column of the messages in the queue table it was showing as "0". But it is showing as "PROCESSED" in the
AQ$<QUEUE_TABLE> msg_state column.
We are unable to figure out why there is a mismatch. Can any one help us ?
the column value AQ$<QUEUE_TABLE>.MSG_STATE = 'PROCESSED' corresponds to column value <QUEUE_TABLE>.STATE = 2
(see definition of view AQ$<QUEUE_TABLE for the DECODE statement).
In your case you see a value of STATE = 0 instead of STATE = 2. Is this correct ? The value 0 corresponds to a state "READY" (the delay
is gone and the message is ready for dequeuing).
The AQ documentation says: "After the specified delay, the message is in the READY state and available for dequeuing."
Normally, you cannot see the processed data in your AQ because the enqueued data will be deleted after
successful dequeuing. You wrote that they were processed successfully.