A while back, I had an email asking why when a message has been removed from a Workflow-related advanced queue, it still remained visible:

We have a requirement to delete the future planned event from the WF_DEFERRED table. I am executing the below code to delete / remove a record from WF_DEFERRED table, however it is not deleting.

BEGIN
  WF_QUEUE.purgeevent (wf_queue.deferredqueue,'<Msg ID>', TRUE);
  COMMIT;
END;

Looking at the example code that they gave me, there is nothing particularly wrong with what they were doing – personally, I’m wary of using the WF_QUEUE APIs to manipulate messages on queues because I prefer to use DBMS_AQ and DBMS_AQADM when working with queues.  The reason I’m so reluctant to rely on the Workflow package stems from when Workflow 2.6 first shipped and introduced BES.  Working for a large banking client in London, we were the first implementation of Workflow which really pushed what BES was designed to do – however, dealing with Workflow product development regularly made me shudder with some of their suggestions on how to work with queues.  At the time, the team leader that I was working with was one of the leading lights of the Oracle integration space, and between us we concluded that we were better steering clear of the Workflow manipulation of queues (anything that relies on polling to check something which could / should have been written as a blocking dequeue was not a good sign, nor was the idea that the product was only shipping ready to work with single consumer queues…)

Anyway, that’s my personal preference – you can use the WF_QUEUE package if you want, but you won’t find anything that I work with that uses that package in preference to the DBMS_AQ package.

The first thing that I said to check was what the retention period for the queue was.  When a queue is defined in Oracle, you can define how long a message can remain visible once it has been dequeued (i.e. the retention period), and Oracle Workflow defines a retention period for the seeded queues as standard.  The retention period can be checked by running the following SQL:

SELECT owner, name, retention FROM all_queues WHERE name LIKE 'WF%';

If the retention time is not zero, then the message will remain visible for that length of time after it has been processed.  Assuming that the message is still visible, we can then check the status of the message by running the following SQL:

SELECT msg_state, count(*) FROM applsys.aq$wf_deferred GROUP BY msg_state;

SELECT msg_state FROM applsys.aq$wf_deferred WHERE msg_id = <your message ID>;

If the message has been removed then the status would show as PROCESSED.  All that the purgeevent API does is to dequeue and discard the message.  If the message has not been processed, then is may well be in a READY state, which indicates that the API has not worked.

As it turned out, the message had been dequeued correctly (the message state was PROCESSED), but the user was still seeing the message because the retention period had not passed completely when they were checking in the queue for the message.

So – that’s why the messages were still visible, despite them being removed from the queue.

This entry was posted on Friday, October 14th, 2011 at 6:09 am and is filed under Oracle, Technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.