Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 234 Big Data Appliance
- 1.9K Data Science
- 449.7K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.3K Development
- 17 Developer Projects
- 138 Programming Languages
- 292K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 157 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 387 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
Dequeue not yet committed message

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.
Answers
-
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.
-
Forget slow-by-slow enqueueing.
Enqueue in bulk.
https://docs.oracle.com/database/121/ARPLS/d_aq.htm#ARPLS65296
MK
-
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.