Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 967
  • Last Modified:

Dequeueing by message id

I'm attempting to dequeue a specific message by its message ID using PL/SQL.

I retrieve the message ID from the queue table, but when I run the dequeue, I get
ORA-25263, no message in queue SCHEMA.QUEUE with message ID C0C68C7D93694022A93157ED55406D53"

What am I doing wrong? I also tried hextoraw() on the msgid but that made no difference.
script.sql
0
Elisas
Asked:
Elisas
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
See if the following note from Oracle Support helps:
ORA-25263 Returned While Dequeueing a Message From AQ Using DBMS_AQ.NO_WAIT option (Doc ID 1344845.1)

It is for NO_WAIT but based on the online docs you are only waiting 1 second so it might be the same thing.
0
 
ElisasAuthor Commented:
A greater delay does not seem to make a difference (I've tried as long as 10 minutes).

I have no access to the trace file so I cannot confirm or deny an ORA-00054 as the doc you're referring to suggests, but there are no other processes working on the queue so I don't see why there would be a lock.
Also, dequeueing the entire queue without specifying a message id works just fine.
0
 
slightwv (䄆 Netminder) Commented:
Sorry but that is all the information I can locate.

If you have a test case that creates the queue, enqueues a message then tries to dequeue, I'll be happy to run it on me development database to see if I can reproduce what you are seeing and attempt to figure out the issue.

If not, I suggest you open an SR with Oracle Support.  They will have the necessary tools to help you troubleshoot this and trace properly if necessary.
0
 
ElisasAuthor Commented:
I realized what went wrong as I was making a test case. Now I just feel stupid.

Since I'm dequeueing in a loop, the message is gone on the 2nd iteration. Since an exception then is thrown, the queue is apparently rolled back.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now