[SOLVED] Getting priority messages from Oracle Advanced Queues

Getting priority messages from Oracle Advanced Queues

Currently I have a piece of code that enqueues and dequeues messages using oracle advanced queues.

What I need to do is first check for messages with a high priority, before getting the others. But I can't seem to do this.

Here is a sample of my code:

Public Function GetQMsg(ByRef sMsg, ByRef vMsgId As Variant, Optional lWait As Long = ORAAQ_DQ_NOWAIT) As Long

gobjDB.LastServerErrReset
sMsg = ""

On Error GoTo cmdErr
gobjQ.Wait = lWait     ' ORAAQ_DQ_NOWAIT
gobjQ.Navigation = ORAAQ_DQ_FIRST_MSG ' to refresh snapshot of msgs waiting
'gobjQ.DequeueMode = ORAAQ_DQ_BROWSE
vMsgId = gobjQ.Dequeue()

sMsg = gobjMsg.Value

GetQMsg = 0
GoTo Fin

cmdErr:
If gobjDB.LastServerErr = 25228 Then
    ' no msg available:
    GetQMsg = QUEUE_EMPTY
Else
    GetQMsg = gobjDB.LastServerErr
End If

Fin:
End Function

If I add the following line into the code I can see the msg priority, but I can't workout how to return the highest priority messages first.

'Debug.Print "Message Priority: " & gobjMsg.Priority

I appreciate this is using legacy developments but its fundamental to the bigger application.

So my questions is how to I get the messages returned in priority order ?

Thanks

Solution

When you create a queue table you can set up parameter "sort_list".

 DBMS_AQADM.CREATE_QUEUE_TABLE (
queue_table        => 'aq.priority_msg', 
sort_list          => 'PRIORITY desc,ENQ_TIME', 
queue_payload_type => 'aq.Message_typ');

And queue table will be sorted by priority and enq_time. Dduring deque you will get the message with highest priority.