I am trying to dequeue message with a specific priority using deq_condition (in Oracle Advance Queue), but always getting no message found. I have tried the following:
dequeue_options.deq_condition := 'tab.priority = 10';
dequeue_options.deq_condition := 'priority = 10';
I have also tried to return a true condition like this:
dequeue_options.deq_condition := '1 = 1';
but always getting no message found and if I remove this condition then I get the queued message. any idea ?
Simple check if in the queue is a message with the given priority. Substitute aqtab for your queue table name
select * from aqtab where priority = 10 order by ENQ_TIME;
Very probably you'll see no messege, as this condition priority = 10 is simple added to the query performing the dequeue.
Note, that to set the message priority you use message properties
l_message_properties dbms_aq.message_properties_t;
Simple assign the required priority ...
l_message_properties.priority := 10;
... and pass the properties as a parameter to the DBMS_AQ.enqueue
After a commit you should see the message in the queue table using the above query and you should be able to dequeue it with the deq_condition