Search Results fnd_lobs_s




Overview
Explore in AI Chat

Overview

AMS_ATCH_UPGRADE is a PL/SQL package body owned by the APPS schema in Oracle E-Business Suite, classified under the ETRM object registry as an OTHER API type. It forms part of the Oracle Marketing (AMS) application family, whose objects carry the AMS_ prefix. The package is documented with a VALID status and is a standalone utility body: the ETRM dependency report confirms that APPS.AMS_ATCH_UPGRADE is not referenced by any other database object, meaning it exists to be invoked directly rather than being called from a higher-level PL/SQL layer.

Its functional purpose is to support attachment-related upgrade processing within Oracle Marketing. During release upgrades and data migrations, attachment content historically stored as BFILE pointers or legacy LOB structures must be materialized into the FND_LOBS repository so that the standard Oracle EBS attachment framework can serve the files. AMS_ATCH_UPGRADE encapsulates that migration work, reading attachment data from external file system locations and writing it into the central LOB tables.

Key Procedures and Functions

The documented package exposes a single program unit:

  • CREATE_LOB_FROM_BFILE — Creates a FND_LOBS record from a BFILE source. The procedure resolves a BFILE locator and transfers its binary content into the FND_LOBS table so that the attachment becomes accessible through the standard EBS attachment infrastructure rather than through an operating system file reference. Its name indicates the directional flow of the operation: source is a BFILE, target is a LOB row. Consistent with the ETRM metadata, no explicit parameter list is documented for this unit and none is asserted here.

The single-procedure surface area is characteristic of a focused, purpose-built upgrade utility rather than a general-purpose API. This namespace is not part of the AMS public API family and carries no external inbound dependencies.

Tables Accessed

The dependency listing records the following objects referenced by the package body:

  • FND_LOBS — The central Oracle Application Object Library table that stores attachment content as BLOB/CLOB data along with descriptive metadata such as file name and media type. This is the primary target of the package's DML, populated by CREATE_LOB_FROM_BFILE.
  • FND_LOBS_S — The sequence used to generate primary key values for FND_LOBS. The package draws from this sequence when inserting new attachment rows.
  • DBMS_LOB — The Oracle-supplied PL/SQL package used for LOB manipulation. It provides the primitives required to open, read, and load BFILE and BLOB content during the conversion.
  • DUAL — The standard single-row utility table, referenced for scalar evaluations and sequence selection within SQL statements.

The package also depends on the SYS-owned STANDARD package and resolves references through APPS synonyms. The appearance of FND_LOBS_S in the dependency chain is directly relevant to administrators investigating sequence or LOB concurrency behavior, since it confirms that row identity for migrated attachments is sequence-driven.

Usage Notes

AMS_ATCH_UPGRADE is an administrative upgrade artifact rather than a runtime application component. It is typically invoked during upgrade or data-migration windows, after the file system location holding the source BFILEs has been made visible to the database and before the affected marketing attachments are expected to be available to end users. Invocation is normally performed from a privileged session by a DBA or upgrade engineer, either interactively or through a wrapper script, using EXECUTE privileges on the body granted to the APPS schema owner.

Because the package is not called by any other database object, there is no reusable API contract for custom development. Customers extending Oracle Marketing should treat AMS_ATCH_UPGRADE as internal upgrade infrastructure and use the supported FND attachment APIs for ongoing attachment creation. Any direct invocation should be preceded by a backup of FND_LOBS and should account for the FND_LOBS_S sequence, since repeated execution without de-duplication can create duplicate attachment records.