Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dequeue not yet committed message

Dmitrii DunaevAug 22 2019 — edited Aug 22 2019

Hello everyone, we use Oracle 11.2.

Lets say I enqueue 100 messages in my session. There is a single user column in the queue table that holds unique keys. Now I want to dequeue a single message with a specific key, but I want to dequeue only the one that my sessions enqueued and not yet committed, I do not want to dequeue any messages inserted by any other sessions. In other words I just want to rollback a single message and commit the other 99. How do I do that?

Thank you in advance.

Comments

jflack

Someone correct me if I'm wrong, but I don't think what you want to do is possible.  Once a message is in the queue, you either commit and save all the enqueued messages, or rollback and save none.  So you probably need logic that will not enqueue the message with the specific key in the first place.

Or, this may be a good case for a GLOBAL TEMPORARY TABLE.  Instead of queuing your messages, INSERT them into the table.  When you need to delete one, just delete it from the table.  When you are ready to commit, loop through the table, queue the messages that are still there, then commit.

Mike Kutz

Forget slow-by-slow enqueueing.

Enqueue in bulk.

https://docs.oracle.com/database/121/ARPLS/d_aq.htm#ARPLS65296

MK

jflack

Forgot that procedure was there, thanks, Mike.

Dmitrii:  If the amount of data to be queued isn't a lot ("a lot" being a relative term) then you might skip the temporary table and just add the messages to a PL/SQL collection (TABLE OF payload type), then you can use a bulk enqueue as Mike suggests to enqueue the whole table at once.  Define the table in a package body to give it session scope.  But if the amount is large enough to have an impact on memory used, then use the table, BULK COLLECT into a collection when you read the table, and enqueue it block by block.

1 - 3

Post Details

Added on Aug 22 2019
3 comments
304 views