Error in workflow Notification
Hi guys.
We recently noticed that a few of our notifications were crashing with the following error:
[WF_ERROR] ERROR_MESSAGE=3835: Error '-20002 - ORA-20002: 2018: Unable to generate the notification XML. Caused by: 2020: Error when getting notification content. Caused by: ORA-01403: no data found ORA-01403: no data found Wf_Notification.GetAttrClob(233780, COURSE, text/html) Wf_Notification.oldGetAttrClob(233780, COURSE, text/html) WF_NOTIFICATION.GetFullBody(nid => 233780, disptype => text/html) WF_MAIL.GetLOBMessage3(nid => 233780, r_ntf_pref => MAILHTML)' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'. ERROR_STACK= WF_MAIL.GetLOBMessage3(233780, WFEBSP, 2020: Error when getting notification content. Caused by: ORA-01403: no data found ORA-01403: no data found Wf_Notification.GetAttrClob(233780, COURSE, text/html) Wf_Notification.oldGetAttrClob(233780, COURSE, text/html) WF_NOTIFICATION.GetFullBody(nid => 233780, disptype => text/html) WF_MAIL.GetLOBMessage3(nid => 233780, r_ntf_pref => MAILHTML), Step -> Getting text/html body) WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 233780) WF_XML.Generate(oracle.apps.wf.notification.send, 233780) WF_XML.Generate(oracle.apps.wf.notification.send, 233780) Wf_Event.setMessage(oracle.apps.wf.notification.send, 233780, WF_XML.Generate) Wf_Event.dispatch_internal()
I managed to trace the error the the WF_NOTIFICATIONS package, specifically the read_clob procedure.
Seems that the
dbms_lob.READ(wf_Notification.temp_clob,buff_length,pos,line);
line is the one causing the problems, it returns an error message of:
"argument 2 is null, invalid, or out of range".
I noticed that the buff_length field is hard_coded in the procedure with a value of 16000, and that if I lower it to less than the length of temp_clob, it works fine.
I'm just a bit hesitant to start scratching around in the workflow code without knowing exactly what I'm doing (especially as this only happens in production).
I also can't figure out why only some (a very small amount) of notifications get this error.
Any thoughts on what the impact would be if I change the value from 16000 to something like 4000?
Thanks a lot
Elmar