October 14th, 2011 Matt
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.
WF_QUEUE.purgeevent (wf_queue.deferredqueue,'<Msg ID>', TRUE);
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.
October 7th, 2011 Matt
I was asked recently whether there was any way to stop one particular notification going to one particular user via email – the user wanted to receive all their other notifications via email, but not this one.
Since the user wanted to receive all their other notifications via email, it wasn’t a case of being able to change their notification preference, since that would stop all the others going out as well. We could consider flicking the profile before sending the notification and then changing it back immediately afterwards, but I’m not convinced that this would work. Also, it’s a pretty intrusive change to make to process, since you would need new logic before and after the notification activity.
So, there are two other ways that the client could do this.
Firstly, they could write a trigger on WF_NOTIFICATIONS which fires when a new notification is created. The trigger would check whether the recipient is the user we are interested in and the notification type being created, and if so immediately set the MAIL_STATUS to SENT. This would fool the mailer into believing that the notification had already been emailed, so would never go out via email.
The second approach would be to change the Workflow definition and include a post notification function for the one notification which performs the same logic – if the user is the one person who doesn’t want to receive the notification via email, then update the MAIL_STATUS to SENT again to stop the email being sent.
Both of these approaches have pros and cons. Creating a trigger on the table may introduce a performance overhead – every notification which is created would need to be checked to determine whether to suppress the email being sent. Creating a post-notification function means that changes need to be made to the Workflow definition, and the support guidelines would need to be checked before making changes to a seeded notification. Also, if the notification already has a post notification function, then you would need to either change that PNF, or create a wrapper which calls the original PNF and then does the new logic.
There were three questions that I put to the client before recommending an approach to adopt:
- Is it possible / likely that more than one user will want this functionality?
- Is is possible / likely that the users might change their minds about whether they want this enabled or disabled?
- Is it possible / likely that the user(s) might want to include different notifications?
If the answer to the last question is “No”, then I would change the existing Workflow definition and include a post notification function. If the answers to the other questions are “Yes”, but it still only applies to one notification, then I would still create a PNF to perform the logic, but build extra flexibility into the logic. For example, I would also have a profile option set at the right level which indicates whether emails should be suppressed or not, which provides flexibility and ease of use to enable / disable the suppression of emails via the front-end screens rather than needing a coding change.
If, however, the answer to the last question is “Yes”, then I would define a trigger on the table which can then fire under multiple scenarios. I would define a value set which dictates which notifications should be suppressed (either for a whole item type or on an individual notification basis), and have the trigger check the value set first to see whether this notification is one where email might not be needed. If the notification is one which shouldn’t be emailed, then again I would have a profile option for the user / responsibility / org / site which determines whether this particular notification should not be emailed. This may be more processor intensive than a quick and easy check of one thing, but it provides the most flexibility to the solution – no coding changes are required to add / remove notifications or people to the list, which means that changes can be implemented quickly and easily without the need for further testing to be performed.
In the end, the client adopted the post notification function approach and decided not to make it widely known that this was a possibility. It may be that in future years, more notifications / users are required, at which point they should revisit the recommendations and switch to the trigger approach. There is nothing wrong with the way they have gone – it meets their current requirements, but if those requirements change significantly in the future then a review will be required.