Orphaned Records In FND_LOBS Table When Uploading Attachments Using FNDATTACH Form
Last updated on JUNE 21, 2017
Applies to:Oracle Application Object Library - Version 220.127.116.11 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 31-AUG-2013***
When doing a file upload through attachment paper clip in any core form, when end user click on (NO) button in the dialog box that appears when the system is asking if the file uploaded successfully or not , the file is still being saved in (FND_LOBS) table and not deleted. Accordingly FND_LOBS table is growing rapidly and consume space and there is no way to purge it for this orphaned records.
Expect if the (End User) clicks on (NO) button, file should not remain in FND_LOBS table anymore.
Steps to reproduce the issue
The issue can be reproduced at will with the following steps as an example:
1- Login to Oracle Applications.
2- Select any form that has attachment enabled.
This is an example from Vision Instance:
- Select (Human Resources, Vision Enterprises) responsibility
- Navigate to (People > Enter and Maintain)
- Query for any Employee.
3- Click on the (Paper Clip) icon.
4- Add new Attachment (Type: File)
5- In the upload page upload any file and you will receive message saying:
Upload a File
File upload completed successfully.
* Please close the web browser.
* Return to the Attachments form and click the Yes button to indicate file upload is complete.
6- Going back to form , you will get a message asking:
"Has the file been uploaded successfully ?"
7- Click on (NO) button.
8- At that time when checking the database table and will find that the file is still there in the FND_LOBS table and no related entries in (FND_DOCUMENTS_TL & FND_ATTACHED_DOCUMENTS) tables.
For 11.5-R12, the following script can be used check if you have any orphaned records in FND_LOBS table that has been inserted through FNDATTACH form:
FROM FND_LOBS FL
WHERE NOT EXISTS
FROM FND_DOCUMENTS_VL FDV
WHERE FDV.MEDIA_ID = FL.FILE_ID
AND FDV.DATATYPE_ID = 6)
AND PROGRAM_NAME like 'FNDATTCH'
AND EXPIRATION_DATE IS NULL;
The MEDIA_ID column is not populated when adding attachment to an Opportunity [<Note 551258.1>] MEDIA_ID is not populated for FND_DOCUMENTS_TL, but the query to identify orphaned records refer to this particular column. The problem is that in old versions (11i), MEDIA_ID was null in FND_DOCUMENTS and present in FND_DOCUMENTS_TL, and in R12 MEDIA_ID had been moved back to FND_DOCUMENTS and null in FND_DOCUMENTS_TL.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms