Oracle Workflow Monitoring-

There are some known issues in Workflow notification mailer (11i including 11.5.10) due to which the mailer goes down and all the notification email gets stuck in the WF_NOTIFICATIONS table.

To resolve that the workflow notification mailer needs a bounce by the DBAs.

But the question is how to know when it goes down.

So, to achieve that, a simple concurrent program can be designed based on the below SQL query to identify if the notification mailer is down.
The concurrent program can be scheduled to run every half an hour. Email functionality can be implemented in the concurrent program using an email program as mentioned here,

Google "SendMail.Send". You would find bunch of such programs.



SELECT COUNT (1)
FROM wf_notifications wf
WHERE (SYSDATE - wf.begin_date) * 24 * 60 > = p_time_in_min
AND NVL (mail_status, 'NOTMAIL') = 'MAIL'
AND status = 'OPEN';