Find workflows not processed by mailer after ORA-29532

I recently ran into a problem where inbound Workflow approvals were being “stuck” in processing. The notification messages were being delivered to the instance’s INBOX and transferred into the PROCESS folder, but were not being processed. The only error message was a notice sent to the sysadmin.

In the error notification was an ORA-29532:

Event Error Name: -29532
Event Error Message: ORA-29532: Java call terminated by uncaught Java exception: java.lang.ArrayIndexOutOfBoundsException
Event Error Stack:
WF_XML.getResponseDetails()
WF_XML.handleReceiveEvent(oracle.apps.wf.notification.receive.message, C0F79E8FE39B266EE0301490C8C41A0C)

The underlying issue turned out to be a regressed XDK after a database upgrade, and a workaround was to bounce the Workflow Mailer Agent Listeners until the patch (5194357) could be reapplied. But that still left a certain number of workflows in limbo, with the respective users unaware that their actions had not “taken.” So, knowing the identification number of the first errored workflow (from the subject of the error notification email sent to the sysadmin), I wrote a small script to identify these workflows:

select
 recipient_role,
 status,
 mail_status,
 to_user,
 subject
from
 wf_notifications
where
 notification_id in (
 select
 substr(subject, -7) wf_notification_id
 from
 wf_notifications
 where
 subject like 'External Event ERROR%'
 and notification_id > &id_number)
order by
 to_user

References

How to Identify the Version of XDK Installed Inside the Database [ID 177411.1]

Response Workflow Emails Not Being Processed by the Mailer and Still Shows in Process Resulting in ORA-29532 [ID 1288090.1]