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]
Recent Comments