2011年5月17日星期二

PO workflow error "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" in Wf_Notification.runFuncOnBody(XXXXXX, text/html)

The PO encountered error in workflow while generate notifiation PO_REQ_APPROVE :

[
WF_ERROR] ERROR_MESSAGE=3835: Erreur "-20002 - ORA-20002: 2018: Impossible de générer la notification au format XML. Cause : 2020: Erreur d'extraction du contenu de la notification. Cause : ORA-06502: PL/SQL: numeric or value error: character string


The wfstatus output for the PO shows the following error :

[WF_ERROR] ERROR_MESSAGE=3835: Erreur "-20002 - ORA-20002: 2018: Impossible de générer la notification au format XML. Cause : 2020: Erreur d'extraction du contenu de la notification. Cause : ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Wf_Notification.runFuncOnBody(409499, text/html)
Wf_Notification.GetBody(409499, text/html)
WF_NOTIFICATION.GetFullBody(nid => 409499, disptype => text/html)
WF_MAIL.GetLOBMessage3(nid => 409499, r_ntf_pref => MAILHTML)" survenue lors de l'exécution de la fonction de génération "WF_XML.Generate" pour l'événement "oracle.apps.wf.notification.send". ERROR_STACK=
WF_MAIL.GetLOBMessage3(409499, WFMAIL, 2020: Erreur d'extraction du contenu de la notification. Cause : ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Wf_Notification.runFuncOnBody(409499, text/html)
Wf_Notification.GetBody(409499, text/html)
WF_NOTIFICATION.GetFullBody(nid => 409499, disptype => text/html)
WF_MAIL.GetLOBMessage3(nid => 409499, r_ntf_pref => MAILHTML), Step -> Getting text/html body)
WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 409499)
WF_XML.Generate(oracle.apps.wf.notification.send, 409499)
WF_XML.Generate(oracle.apps.wf.notification.send, 409499)
Wf_Event.setMessage(oracle.apps.wf.notification.send, 409499, WF_XML.Generate)
Wf_Event.dispatch_internal()


After search in Oracle metalink, we found that a patch has been delivred to fix this issue : ID 579729.1 :

The issue can be reproced at will with the following steps :
1. Create a PO with more than 20 lines
2. Submit the PO for approval
3. Log in as approver
4. Request for more information from the "Standard Purchase Order 1234 for USD 999,999.99 requires your approval" notification
5. Log in as the buyer
6. Open the "More Information Requested: Standard Purchase Order 1234 for USD 999,999.99 requires your approval" notification.

Cause
When the line details exceed the available limit of the notification it gives the ORA-06512. This is happening because of the 32K size limit.

This is explained in the following bug:
Bug 6932794 MORE INFO REQUESTED NOTIFICATIONS ERROR WITH ORA-06502: PL/SQL: NUMERIC OR VALUE

Solution

To implement the solution, execute the following steps:
For Release 11.5.10:

1. Download and review the readme and pre-requisites for Patch 7370646.

2. Ensure that you have taken a backup of your system before applying
the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions:
POXWPA1B.pls 115.206.11510.62
POXWPA7S.pls 115.6.11510.2
POXWPA7B.pls 115.38.11510.23

Use the following sql:
select text
from dba_source
where line = 2
and name in ('PO_REQAPPROVAL_INIT1','PO_WF_PO_NOTIFICATION');

5. Retest the issue.

6. Migrate the solution as appropriate to other environments.

For Release 12:

1. Download and review the readme and pre-requisites for Patch 7218243 - Procurement R12.0 Update July 2008 or higher
or
One-off Patch 7612490 which indirectly includes the fix for this issue.

2. Ensure that you have taken a backup of your system before applying the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions:
POXWPA1B.pls-120.46.12000000.13 or higher
POXWPA7S.pls-120.0.12000000.2 or higher
POXWPA7B.pls-120.8.12000000.3 or higher

You can use the commands like the following:
strings -a $PO_TOP/patch/115/sql/POXWPA1B.pls | grep -i '$Header:'
strings -a $PO_TOP/patch/115/sql/POXWPA7S.pls | grep -i '$Header:'
strings -a $PO_TOP/patch/115/sql/POXWPA7B.pls | grep -i '$Header:'

5. Retest the issue.

6. Migrate the solution as appropriate to other environments.

没有评论:

发表评论