Search Results frm_repository_lobs




Overview
Explore in AI Chat

Overview

FRM_REPOSITORY_LOBS is a Report Manager (FRM) repository table in the Oracle E-Business Suite database, owned by the FRM schema. It stores large object (LOB) files generated and managed by Oracle Report Manager, including report output files and third-party files uploaded or attached to reports. In EBS 12.1.1 and 12.2.2, Report Manager serves as the publishing and viewing layer for concurrent program output, and FRM_REPOSITORY_LOBS is the physical store that holds the binary payloads these reports and attachments resolve to.

The object is documented as VALID in the ETRM metadata, with a primary key constraint FRM_REPOSITORY_LOBS_PK on FILE_ID and a documented physical schema of 15 columns. The heuristic Data Vault classification provided in the metadata is standalone. Under that modeling suggestion, the table is best treated as a self-contained hub-like entity keyed by FILE_ID, since no foreign key relationships were mined from the FK structure. Its business content — file attributes plus binary data — makes it more closely resemble a hub with attached descriptive attributes than a classical link table.

Key Information Stored

Only the columns documented in the ETRM metadata are named below. The table holds 15 columns in total; the most operationally significant are:

  • FILE_ID — the surrogate primary key, defined by FRM_REPOSITORY_LOBS_PK and also carried by the unique index FRM_REPOSITORY_LOBS_UK1. This is the join key used by all dependent Report Manager objects.
  • FILE_NAME — the logical name of the stored file as presented to the user.
  • FILE_CONTENT_TYPE — the MIME or content classification of the payload, used to determine how the file is rendered or downloaded.
  • FILE_DATA — the large object column holding the actual binary content. In the documented physical schema, LOB segment indexes SYS_IL0000399302C00004$$ and SYS_IL0000399302C00014$$ are present, indicating that FILE_DATA and FODATA are both LOB-backed columns. Storing the binary here is the core purpose of the table.
  • UPLOAD_DATE, EXPIRATION_DATE — lifecycle timestamps; EXPIRATION_DATE supports purge and retention policies for report output.
  • PROGRAM_NAME, PROGRAM_TAG — identify the concurrent program and program tag context that produced the stored output, allowing retrieval of output per request.
  • FODATA — an additional LOB-backed column, typically associated with report output data sets rather than the rendered file itself.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, ATTRIBUTE1 — standard EBS WHO-column audit trail and a descriptor flex attribute.

The surrogate key (FILE_ID) is distinct from the business-key candidate represented by FRM_REPOSITORY_LOBS_UK1, which is defined on FILE_ID itself in the documented metadata; there is therefore no separately documented natural business key for this table.

Common Use Cases and Queries

Typical use cases centre on retrieving, auditing, and purging Report Manager output. A common pattern is to locate a file by identifier and inspect metadata before downloading the LOB:

  • SELECT FILE_ID, FILE_NAME, FILE_CONTENT_TYPE, UPLOAD_DATE, EXPIRATION_DATE FROM FRM_REPOSITORY_LOBS WHERE FILE_ID = :file_id;
  • Locating files by concurrent program: SELECT FILE_ID, FILE_NAME, PROGRAM_NAME, PROGRAM_TAG, CREATION_DATE FROM FRM_REPOSITORY_LOBS WHERE PROGRAM_NAME = :program_name ORDER BY CREATION_DATE DESC;
  • Reclaiming space by identifying expired output: SELECT COUNT(*), SUM(DBMS_LOB.GETLENGTH(FILE_DATA)) FROM FRM_REPOSITORY_LOBS WHERE EXPIRATION_DATE < SYSDATE;
  • Hashing or length checks on the payload for integrity validation, using DBMS_LOB.GETLENGTH(FILE_DATA).

Because FILE_DATA and FODATA are LOB columns, queries should avoid selecting them indiscriminately. Reporting and DBA scripts typically project only metadata columns, and content extraction is performed only for a specific FILE_ID. Purging should always be driven through supported Report Manager or Oracle-provided purge routines rather than direct deletes, to preserve referential consistency with other FRM objects.

Related Objects

The ETRM metadata classifies the table as standalone with no mined foreign keys, so relationships are functional rather than enforced. The most significant dependent objects are:

  • FRM_REPOSITORY_LOBS_PK — the primary key constraint on FILE_ID; every functional join keyed on FRM_REPOSITORY_LOBS.FILE_ID depends on it.
  • FRM_REPOSITORY_LOBS_UK1 — the unique index on FILE_ID.
  • FRM_REPOSITORY — the parent Report Manager repository metadata table; join on FILE_ID to obtain descriptive and ownership information for the stored file.
  • FRM_REPOSITORY_ITEMS — Report Manager item definitions that reference repository files.
  • FRM_DOCUMENTS — document records associated with published report output.
  • FND_LOBS — the general EBS LOB store used for attachments and file uploads, functionally parallel to this repository.
  • FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROGRAMS — provide the request and program context referenced by PROGRAM_NAME and PROGRAM_TAG on the stored file.

No views or APIs are documented against this object in the supplied metadata; integration is therefore expected to occur through the Report Manager module rather than through direct SQL against the table.