Orphaned Records In FND_LOBS Table When Uploading Attachments Using FNDATTACH Form (Doc ID 963222.1)

Last updated on JUNE 21, 2017

Applies to:

Oracle Application Object Library - Version 11.5.10.2 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***

Symptoms


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.

Expected Behavior

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:

  SELECT COUNT(*)
   FROM FND_LOBS FL
   WHERE NOT EXISTS
    (SELECT '1'
     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;

For R12:
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.

Cause

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