Error Message Invalid LOB Locator Specified ORA-22275 ORA-06512 In Approval Notification

Error Message Invalid LOB Locator Specified ORA-22275 ORA-06512 In Approval Notification [ID 743148.1]
  Modified 29-NOV-2011     Type PROBLEM     Status PUBLISHED
In this Document
  Symptoms
  Cause
  Solution
     For Releases 11.5.9 and 11.5.10:
     For Release 12 or 12.1:
  References

Applies to:

Oracle Purchasing - Version: 11.5.10.2 to 12.1.3 - Release: 11.5 to 12.1
Information in this document applies to any platform.
Symptoms

The approval notification does not show the lines or action history of the document. It displays an error but it does not prevent the user from approving the document. The following error is displayed:

ERROR
ORA-06502: numeric value error: invalid LOB locator specified: ORA-22275 ORA-06512 at
"APPS_WF_NOTIFICATION". LINE 5585
ORA-06512 at line 5

Steps To Reproduce:
1. Log in as approver
2. Go to the notification summary
3. Open the approval notification

It displays the error in the notification body.

Cause

The workflow attribute PO_LINES_DETAILS uses the PLSQL:package.procedure causing the error invalid LOB locator specified in the notification when the approval process expects a PLSQLCLOB:package.procedure.

PO_LINES_DETAILS PLSQL:PO_WF_PO_NOTIFICATION.GET_PO_LINES_DETAILS/POAPPRV:274417-680132

This error will affect only open notifications and will happen after applying any of these patches:

11.5.9 : Patch: 5671680
11.5.10: Patch: 6932794
12 : Patch: 7291462



Note: An easy way to check if this issue is being encountered is by executing the following queries:

SELECT count(*)
FROM WF_NOTIFICATION_ATTRIBUTES WNA,
WF_NOTIFICATIONS WFN
WHERE WFN.MESSAGE_TYPE IN ('POAPPRV',
'REQAPPRV')
AND WFN.STATUS <> 'CLOSED'
AND WFN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
AND WNA.NAME IN ('PO_REQ_APPROVE_MSG',
'PO_REQ_APPROVED_MSG',
'PO_REQ_NO_APPROVER_MSG',
'PO_REQ_REJECT_MSG',
'REQ_LINES_DETAILS',
'PO_LINES_DETAILS')
AND (WNA.TEXT_VALUE LIKE 'PLSQL:%POAPPRV%'
OR WNA.TEXT_VALUE LIKE 'PLSQL:%REQAPPRV%')

SELECT count(*)
FROM wf_item_attribute_values wiav,
WF_ITEM_ACTIVITY_STATUSES wias
WHERE wiav.NAME IN ('PO_REQ_APPROVE_MSG',
'PO_REQ_APPROVED_MSG',
'PO_REQ_NO_APPROVER_MSG',
'PO_REQ_REJECT_MSG',
'REQ_LINES_DETAILS',
'PO_LINES_DETAILS')
AND (wiav.text_value LIKE 'PLSQL:%POAPPRV%'
OR wiav.text_value LIKE 'PLSQL:%REQAPPRV%')
AND wiav.item_key = wias.item_key
AND wiav.item_type = wias.item_type
AND wias.notification_id IN (SELECT notification_id FROM wf_notifications
WHERE message_type IN ('POAPPRV','REQAPPRV')
AND status <> 'CLOSED')

If these queries return any rows, please go ahead and apply the datafix script.

Solution

For Releases 11.5.9 and 11.5.10:

1. Download and review the readme and pre-requisites for datafix Patch 6234198. The patch is intended for 11.5.9 but it can be applied in 11.5.10.

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. Retest the issue.

5. Migrate the solution as appropriate to other environments.

Note: Apply root cause patch 5671680 for 11.5.9. Release 11.5.10.x does not require a root cause fix.

For Release 12 or 12.1:

Download the Patch 6234198 from Metalink and extract the two SQL files: POXINVLOBC.sql. and POXINVLOBF.sql

1. In a SQL*Plus session, run the collection script and the fix script separately.

The collection script(POXINVLOBC.sql) will provide the data that has been corrupted or identified to be containing the problem.

The fix script(POXINVLOBF.sql) will provide the fix to the problem being identified.

2. In the same sql session rerun the collection script to see the result as none.

3. Retest the issue

Note: Release 12 does not require a root cause patch.

Note: The scripts will not update closed notifications





References

BUG:5671680 - REP-I:LARGE:ORA-06502 CHAR STRING BUFFER TOO SMALL WHEN VIEWING PO NOTIF
BUG:6932794 - MORE INFO REQUESTED NOTIFICATIONS ERROR WITH ORA-06502: PL/SQL: NUMERIC OR VALUE
BUG:7006113 - UNIT PRICE IN APPROVAL NOTIFICATION IS IN .XXX INSTEAD OF 0.XXX FORMAT
BUG:7204903 - DATAFIX AFTER ROOT CAUSE PATCH 6932794
NOTE:742454.1 - Scripts poxpoort.sql and poxposgt.sql Fail With ORA-00955 During Patching

 Related
Products
Oracle E-Business Suite > Procurement > Procurement > Oracle Purchasing
Keywords
APPROVAL NOTIFICATION; NOTIFICATION; PLSQLCLOB
Errors
ORA-6502; ORA-22275; ORA-6512

Back to top

 Rate this document