Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Tuesday, February 10, 2009

What's inside FND_LOBS ?

FND_LOBS table is one of the largest objects in an E-Business Suite instance. In our largest instance the size of this table is 160 GB.

FND_LOBS table contains all the attachments which were attached by users in all the modules of E-Business Suite, since the instance was created.

To know which Forms provide Attachment feature
----------------------------------------------------------------------

SQL>select *

from fnd_attachment_functions

where function_name like '%FND_%';

You can see the contents of fnd_lobs through this query:

select file_name, file_id,
to_char(upload_date,'dd-mon-rr hh24:mi:ss'),
to_char(expiration_date ,'dd-mon-rr hh24:mi:ss'),
file_content_type,
dbms_lob.getlength(file_data) size_byte
from applsys.fnd_lobs
--where file_id= ;

Metalink Note 338651.1 has more information about FND_LOBS:

FND_LOBS stores information about all LOBs managed by the Generic File Manager (GFM).
Each row includes the file identifier, name, content-type, and actual data. Each row also includes the dates the file was uploaded and will expire, the associated program name and tag, and the language and Oracle characterset.

The file data, which is a binary LOB, is stored exactly as it is uploaded from a client browser, which means that no translation work is required during a download to make it HTTP compliant.

Therefore uploads from non-browser sources will have to prepare the contents
appropriately (for instance, separating lines with CRLF).

The program_name and program_tag may be used by clients of the GFM for any purpose,
such as striping, partitioning, or purging the table if the program is de-installed.
They are otherwise strictly informative.

These columns and the expiration date are properly set when the
procedure FND_GFM.CONFIRM_UPLOAD is called. If not called, the column
expiration_date remains set, and will eventually be purged by the procedure
FND_GFM.PURGE_EXPIRED.

FND_DOCUMENTS_LONG_RAW stores images and OLE Objects, such as Word Documents and Excel spreadsheets, in the database. If the user elects to link an OLE Object to the document, this table stores the information necessary for Oracle Forms to activate the OLE server, and it saves a bit-mapped image of the OLE server's contents.
If the user does not elect to link an OLE Object, the entire document will be stored in this table.

FND_DOCUMENTS_LONG_TEXT stores information about long text documents.

FND_DOCUMENTS_SHORT_TEXT stores information about short text documents.

9 comments:

Santhosh said...

Hi,

Can you explain some more details abount "attachments which were attached by users in all the modules of E-Business Suite" . What are these attachments ?

Regards
Santhosh

Vikram Das said...

Hi Santhosh,

Please refer to metalink note 338651.1 for more details. I am also updating the post with more information from this note.

- Vikram

Anonymous said...

Vikram, Very helpful information, thank you. I wanted to get the size of each attachment, your query worked perfect for that.

Anonymous said...

Is it safe to use FND_LOBS to store pdf files that are not part of Oracle Forms? I have a web site the generates a pdf document based on shipment data in Oracle and one of the requirements is to store the pdf file in Oracle. Can I use FND_LOBS with a custom program name and file name that I would be able to also retrieve from my web site?

Vikram Das said...

You could use FND_LOBS to store, but since it is custom requirement you could create a custom structure similar to FND_LOBS and store in that. FND_LOBS is already the largest object in E-Business Suite Database, so there is no sense in adding more to it.

Chandu Akkineni said...

Hi Vikram,

I want save attachments in server local directory instead of FND_LOBS. Is it possible? Could you please guide me how to do that?

Thanks,
Chandra

Anonymous said...

Hi Vikram,
Thanks for the blog it is very informative.

I have couple of questions
a) regarding the program_name, program_tag and expirtation date. Though I could upload the attachments, but I never could set these values during upload.

b) The paramenter that are passed to "Purge Obsolete Generic File Manager Data" (FNDGFMPR), the program name parameter, is it necessary, can we run it without any value in our production box ?
Thanks,
Prabhakar Reddy

Vins (Functional Consultant) : R12 | FUSION said...

Is there any query to find the size of all the attachemnet that belongs to particular module, for example service i.e. CS

Ahsan Saeed said...

I want save attachments in server local directory instead of FND_LOBS. Is it possible? Could you please guide me how to do that?