Oracle Queues getting stopped even though there are Ongoing transaction to be dequeued
Sarith Sreedharan, March 15, 2023 - 1:18 pm UTC
Hi Chris,
We had some 20 transactions to be dequeued on the queue before running the given PL sql block to stop the queue.
As per the details given in Oracle forum, if the Wait option is given as TRUE, then it waits to see if there are any outstanding transactions before stopping the queue.
Wait :- Specifies whether to wait for the completion of outstanding transactions. TRUE means wait if there are any outstanding transactions. In this state no new transactions are allowed to enqueue to or dequeue from this queue. FALSE means return immediately either with a success or an error.
So can you advise why in this instance it actually allowed to stop the queue even though the existing records were not dequeued.
March 15, 2023 - 6:31 pm UTC
The answer's right there:
In this state no new transactions are allowed to enqueue to or dequeue from this queue.
Once you call this (even with wait) you can't dequeue any more messages. My understanding is WAIT => TRUE allows transactions the process of enqueuing/dequeuing messages to complete. It doesn't wait for the whole queue to be empty.
Oracle Queues getting stopped even though there are Ongoing transaction to be dequeued
Sarith Sreedharan, March 16, 2023 - 9:26 am UTC
Hi Chris,
Thanks for your quick response.
yes I was clear that no new transaction would be dequeued from the queue or enqueued to the queue.
Our only Concern was if the queue is stopped in between the dequeuing , message may be lost and was initially of the understanding that all pending records would get processed. Now I have got the clarity that when the queue is going to be stopped, system will only check if the ongoing transaction in progress get completed and queue would get stopped. Any further records pending on the queue would get dequeued only when the dequeue option is enabled back.
I hope I have summarized it correctly. if so we can mark this closed.
March 16, 2023 - 12:50 pm UTC
That is my understanding too