13 Replies Latest reply: Mar 25, 2013 5:01 AM by 998928 RSS

    Notification Error when using PL/SQL document

    432580
      I have a notification that calls a procedure that returns some HTML body of the email. It worked once, but now I get the error below. In Oracle Apps on the notification tab the notification shows correctly, it's the actual sending of the email that is failing.

      Thank you for any help you may be able to provide,
      Blake

      [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: sqlerrm wf_notification.GetAttrDoc2(123387, NOTIF_BASIC_INFO_DOC, text/html) Wf_Notification.GetAttrDoc(123387, NOTIF_BASIC_INFO_DOC, text/html) Wf_Notification.GetText(123387, text/html) Wf_Notification.GetBody(123387, text/html) WF_NOTIFICATION.GetFullBody(nid => 123387, disptype => text/html) WF_MAIL.GetLOBMessage3(nid => 123387, 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(123387, WFMAIL, 2020: Error when getting notification content. Caused by: sqlerrm wf_notification.GetAttrDoc2(123387, NOTIF_BASIC_INFO_DOC, text/html) Wf_Notification.GetAttrDoc(123387, NOTIF_BASIC_INFO_DOC, text/html) Wf_Notification.GetText(123387, text/html) Wf_Notification.GetBody(123387, text/html) WF_NOTIFICATION.GetFullBody(nid => 123387, disptype => text/html) WF_MAIL.GetLOBMessage3(nid => 123387, r_ntf_pref => MAILHTML), Step -> Getting text/html body) WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 123387) WF_XML.Generate(oracle.apps.wf.notification.send, 123387) WF_XML.Generate(oracle.apps.wf.notification.send, 123387) Wf_Event.setMessage(oracle.apps.wf.notification.send, 123387, WF_XML.Generate) Wf_Event.dispatch_internal()
        • 1. Re: Notification Error when using PL/SQL document
          432580
          This was occurring in Oracle E-Business Suite's version. I had to stop all workflow service components and then start them.
          • 2. Re: Notification Error when using PL/SQL document
            103438
            We had the situation where the Notification had no problem with the PL/SQL document but the actual email had "ORA-6508" but there was nothing missing or invalid in the database.

            With Oracle's help we found that we needed to restart the application servers' "Service_Component_Containers"

            This problem always comes up for us if we change the PL/SQL package in any way. So now by habit we restart "Service_Component_Containers"
            • 3. Re: Notification Error when using PL/SQL document
              NoLongerActive
              Hi,

              Sadly, this seems to be expected behaviour. I don't mean that it's meant to work like that, just that more often than not, it does ;-)

              There are a number of suggestions on metalink and on my WorkflowFAQ forum, which range from having to bounce the Workflow service components through to having to bounce the database and/or Apache. It depends on the version of Workflow and the release as to what needs to be bounced in order to resolve the issue.

              HTH,

              Matt
              --
              Alpha review chapters from my book "Developing With Oracle Workflow" are available on my website:

              http://www.workflowfaq.com
              http://forum.workflowfaq.com
              • 4. Re: Notification Error when using PL/SQL document
                432580
                The error is now worse... starting and stopping service components did work, but it no longer does.

                Where before the notification tab would still display the message correctly and only the email would error with the error message above, now i get an error on the notification tab as well.
                ---------------------
                Error
                     
                ORA-01403: no data found ORA-01403: no data found ORA-06512: at "APPS.WF_NOTIFICATION", line 5046 ORA-06512: at line 5
                ---------------------

                I tried bouncing the database and restarting the apache server as well as removing and re-installing the workflow, but none of those have fixed the issue. I'm waiting on feedback from an oracle rep (i have a TAR opened), but does anyone have any other suggestions?
                • 5. Re: Notification Error when using PL/SQL document
                  530722
                  We are getting the same error message. How do you stop all the workflow service components?
                  • 6. Re: Notification Error when using PL/SQL document
                    514323
                    This seems to be an issue with the PLSQL Document API code that is attempting to generate content for the notification. You have a SQL within your PLSQL Document that is causing ORA 1403 and thus is propagated to WF_NOTIFICATION code.

                    You can troubleshoot this issue using WF_CORE APIs in your PLSQL Document API code. You could change your PLSQL Document API to include following calls in the procedure's exception region.

                    procedure <your PLSQL Doc procedure>...

                    is
                    begin
                    ......
                    ......
                    exception when others then
                    wf_core.context('<your package name>', '<you procedure name>', document_id);
                    raise;
                    end;


                    This will show if the control was within your PLSQL Document API code when the error occured.
                    o Have you tried writing debug statements into this API and see where it encounters the error?
                    o If you are using a PLSQL CLOB Document API, please make sure you write the content into document out variable using WF_NOTIFICATION.WriteToCLOB API.

                    You need not re-install or bounce Database for these application level errors.

                    Hope this helps
                    Vijay
                    • 7. Re: Notification Error when using PL/SQL document
                      514323
                      user527719 - What error you are getting? ORA 6508 or ORA 1403.

                      Technically when you apply some PLSQL changes to the database that could impact the mailer/notification code, you should shutdown the agent listener service, apply the changes and re-start them. This is same as the process of applying a patch. Compiling a PLSQL package in the Database invalidates the state of that package in other active sessions and the next time the other session accesses that package, it appears invalid though in real-time the package is valid.

                      Hope this helps
                      Vijay
                      • 8. Re: Notification Error when using PL/SQL document
                        437295
                        Dear All,

                        We are also getting the error like this below and we have a TAR opened with oracle and yet to get a solution or the verdict as to why it is happening.

                        We have a WFALERT process gathering all the interface errors from PO and receiving interface tables and sending a notification with list of them to users who are responsible for correcting them. Everything was working until March 2006 and then it stopped. Now whenever the WFLAERT is run, we get the following message. The PLSQL script is valid and we have not changed anything:

                        [WF_ERROR] ERROR_MESSAGE=3835: Error '-6508 - ORA-06508: PL/SQL: could not find
                        program unit being called' encountered during execution of Generate function
                        'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'.
                        ERROR_STACK=
                        WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 135665)
                        WF_XML.Generate(oracle.apps.wf.notification.send, 135665)
                        WF_XML.Generate(oracle.apps.wf.notification.send, 135665)
                        Wf_Event.setMessage(oracle.apps.wf.notification.send, 135665, WF_XML.Generate)
                        Wf_Event.dispatch_internal()

                        Any help will be highly appreciated as this is a production issue and somebody has to babysit round the clock to monitor the interface errors now.

                        Thanks in advance for your help.
                        • 9. Re: Notification Error when using PL/SQL document
                          548716
                          i am getting this error too.Its not very often but this error is comming and the users get the notification in there notification tab in oracle application with the error message that u gave in the thread.

                          did u get any response from oracle or anybody.please advice

                          thanks in advance



                          Error Name = WF_ERROR
                          Error Message = [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: sqlerrm

                          wf_notification.GetAttrDoc2(219693, DETAILS, text/html)
                          Wf_Notification.GetAttrDoc(219693, DETAILS, text/html)
                          Wf_Notification.GetText(219693, text/html)
                          Wf_Notification.GetBody(219693, text/html)
                          WF_NOTIFICATION.GetFullBody(nid => 219693, disptype => text/html)
                          WF_MAIL.GetLOBMessage3(nid => 219693, r_ntf_pref => MAILHTM2)' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'. ERROR_STACK=
                          WF_MAIL.GetLOBMessage3(219693, WFMAILER, 2020: Error when getting notification content. Caused by: sqlerrm

                          wf_notification.GetAttrDoc2(219693, DETAILS, text/html)
                          Wf_Notification.GetAttrDoc(219693, DETAILS, text/html)
                          Wf_Notification.GetText(219693, text/html)
                          Wf_Notification.GetBody(219693, text/html)
                          WF_NOTIFICATION.GetFullBody(nid => 219693, disptype => text/html)
                          WF_MAIL.GetLOBMessage3(nid => 219693, r_ntf_pref => MAILHTM2), Step -> Getting text/html body)
                          WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 219693)
                          WF_XML.GenerateGroupDoc(oracle.apps.wf.notification.send, 219693)
                          WF_XML.Generate(oracle.apps.wf.notification.send, 219693)
                          WF_XML.Generate(oracle.apps.wf.notification.send, 219693)
                          Wf_Event.setMessage(oracle.apps.wf.notification.send, 219693, WF_XML.Generate)
                          Wf_Event.dispatch_internal()
                          • 10. Re: Notification Error when using PL/SQL document
                            user5029
                            I am getting the following error ie


                            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-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SYS.HTP", line 1557 ORA-06512: at "SYS.HTP", line 1735 ORA-06512: at "APPS.HR_UTIL_DISP_WEB", line 3519 ORA-06512: at "APPS.XX_HR_MDA_AUDIT_VIEW_WEB", line 2077 ORA-06502: PL/SQL: numeric or value error: character string buffer too small Wf_Notification.GetAttrClob(2648602, XX_HR_REVIEW_DO' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'. ERROR_STACK= WF_MAIL.GetLOBMessage3(2648602, WFMAIL, 2020: Error when getting notification content. Caused by: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SYS.HTP", line 1557 ORA-06512: at "SYS.HTP", line 1735 ORA-06512: at "APPS.HR_UTIL_DISP_WEB", line 3519 ORA-06512: at "APPS.XX_HR_MDA_AUDIT_VIEW_WEB", line 2077 ORA-06502: PL/SQL: numeric or value error: character string buffer too small Wf_Notification.GetAttrClob(2648602, XX_HR_REVIEW_DOC_ATTR, text/html) Wf_Notification.oldGetAttrClob(2648602, XX_HR_REVIEW_DOC_ATTR, text/html) WF_NOTIFICATION.GetFullBody(nid => 2648602, disptype => text/html) WF_MAIL.GetLOBMessage3(nid => 2648602, r_ntf_pref => MAILHTML), Step -> Getting text/html body) WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 2648602) WF_XML.Generate(oracle.apps.wf.notification.send, 2648602) WF_XML.Generate(oracle.apps.wf.notification.send, 2648602) Wf_Event.setMessage(oracle.apps.wf.notification.send, 2648602, WF_XML.Generate) Wf_Event.dispatch_internal()

                            At line 2077 in "APPS.XX_HR_MDA_AUDIT_VIEW_WEB" the code is :
                            WHEN others THEN
                            hr_util_disp_web.display_fatal_errors
                            (p_message => g_package_name||'.display_detail - '||sqlerrm||' '||sqlcode);

                            At line 3519 in "APPS.HR_UTIL_DISP_WEB" the code is :
                            l_session_id := icx_sec.getid(icx_sec.pv_session_id);
                            htp.p('<HTML>');
                            htp.p('<HEAD>');
                            htp.p('</HEAD>');
                            htp.p('<BODY>');
                            htp.p('<SCRIPT language="JavaScript">');
                            htp.p('window.location="hr_util_disp_web.display_fatal_error_form?'
                            ||'p_message='||icx_call.encrypt2(p_message, l_session_id)
                            ||'"');
                            htp.p('</SCRIPT>');
                            htp.p('</BODY>');
                            htp.p('</HTML>');

                            Any inputs on how to debug it.

                            Edited by: user12010068 on Dec 10, 2009 3:59 AM
                            • 11. error Workflow.
                              906285
                              Dear,Persson

                              Please you can help me.

                              Tipo de Item = POAPPRV
                              Chave do Item = 25544-41669
                              Chave do Usuário =6100

                              Nome do Erro = WF_ERROR
                              Mensagem de Erro = [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-06502: PL/SQL: numeric or value error

                              WF_NOTIFICATION.WriteToClob()
                              Wf_Notification.GetAttrClob(1192586, PO_LINES_DETAILS, text/plain)
                              Wf_Notification.oldGetAttrClob(1192586, PO_LINES_DETAILS, text/plain)
                              WF_NOTIFICATION.GetFullBody(nid => 1192586, disptype => text/plain)
                              WF_MAIL.GetLOBMessage3(nid => 1192586, r_ntf_pref => MAILTEXT)' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'. ERROR_STACK=
                              WF_MAIL.GetLOBMessage3(1192586, WFMAIL, 2020: Error when getting notification content. Caused by: ORA-06502: PL/SQL: numeric or value error


                              My email is joaquimamado@gmail.com

                              Please i'am waiting

                              Thanks so much

                              Hugs

                              Joaquim Amado
                              • 12. Re: error Workflow.
                                Shivdas Tomar -Oracle
                                What is the E-Business Suite code line on the instance where you are encountering this issue? We fixed some issue around this API, so other option to check it, run your PL/SQL API which generates the contents as a standalone and then if that is able to generate full contents without fail.
                                • 13. Re: error Workflow.
                                  998928
                                  Dear all,
                                  I have issue, Error mail when BSA expired notify

                                  The details:
                                  Failed Activity Blanket Expired Notification

                                  Activity Type Notice

                                  Error Name WF_ERROR

                                  Error Message [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-01722: invalid number wf_notification.GetAttrDoc2(926694, BLANKET_DETAILS, text/html) Wf_Notification.GetAttrDoc(926694, BLANKET_DETAILS, text/html) Wf_Notification.GetText(926694, text/html) Wf_Notification.GetBody(926694, text/html) WF_NOTIFICATION.GetFullBody(nid => 926694, disptype => text/html) WF_MAIL.GetLOBMessage3(nid => 926694, 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(926694, WFMAIL, 2020: Error when getting notification content. Caused by: ORA-01722: invalid number wf_notification.GetAttrDoc2(926694, BLANKET_DETAILS, text/html) Wf_Notification.GetAttrDoc(926694, BLANKET_DETAILS, text/html) Wf_Notification.GetText(926694, text/html) Wf_Notification.GetBody(926694, text/html) WF_NOTIFICATION.GetFullBody(nid => 926694, disptype => text/html) WF_MAIL.GetLOBMessage3(nid => 926694, r_ntf_pref => MAILHTML), Step -> Getting text/html body) WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 926694) WF_XML.Generate(oracle.apps.wf.notification.send, 926694) WF_XML.Generate(oracle.apps.wf.notification.send, 926694) Wf_Event.setMessage(oracle.apps.wf.notification.send, 926694, WF_XML.Generate) Wf_Event.dispatch_internal()

                                  Please help me!!!