Search Results ams_atch_upgrade




Overview
Explore in AI Chat

Overview

AMS_ATCH_UPGRADE is a PL/SQL package owned by the APPS schema in Oracle E-Business Database, classified under the ETRM as an OTHER API. Its header, defined with AUTHID CURRENT_USER, is delivered by script amsvatub.pls (version 115.4, last updated December 2002). The package belongs to the Oracle Advanced Marketing (AMS) product family; the "ATCH" segment of the name corresponds to the attachment-handling component of that module, and "UPGRADE" indicates its role in data migration performed during application upgrades from earlier releases. The package addresses that attachment migration process by copying binary file content from BFILE-based storage into the FND_LOBS LOB repository used by later 11i/12i releases, ensuring that legacy file references remain accessible after an upgrade.

Key Procedures and Functions

The package exposes a single documented procedure:

  • Create_LOB_From_BFILE — The only public program unit in the package. Its stated purpose is to create a LOB (large object) entry from a BFILE source and return the resulting primary key through an OUT parameter so the caller can record the identifier on the upgraded entity. The procedure accepts input parameters identifying the source, including a file name and a directory name, and returns a numeric file identifier. As documented, the procedure returns the primary key to the caller ("Hint: Primary key needs to be returned"). No additional functions, overloads, or private helpers are documented in the ETRM metadata; the parameter list shown above is the complete, documented signature and should not be extended.

Tables Accessed

The documented data access footprint is limited and consists of the following objects, referenced through APPS synonyms:

  • FND_LOBS — The core Oracle EBS table that stores binary large objects (attachments, documents, and images) in the database. The procedure inserts the migrated BFILE content here, generating the LOB record that later replaces the original file-system or BFILE reference.
  • FND_LOBS_S — The sequence associated with FND_LOBS. It supplies the synthetic primary key value for the new LOB row, which is returned to the caller through the OUT parameter.
  • DBMS_LOB — The Oracle-supplied PL/SQL package used to locate the source BFILE, open it, and copy its contents into the destination LOB column (typically via BFILENAME, LOADBLOBFROMFILE, or equivalent primitives).
  • DUAL — The standard single-row dummy table, used for sequence value retrieval and simple expression evaluation.

No other application tables are documented as being read or written, and the ETRM records no package in the E-Business Suite as referencing AMS_ATCH_UPGRADE, indicating it is a terminal utility rather than a shared service.

Usage Notes

AMS_ATCH_UPGRADE is an internal upgrade utility rather than a general-purpose runtime API. It is typically invoked from upgrade driver scripts — SQL*Plus or concurrent-program-based migration routines executed during the transition to a release that centralizes attachment storage in FND_LOBS. In that flow, the deploying DBA or upgrade script must first create a DIRECTORY object pointing at the location of the legacy BFILEs and grant the necessary read privilege, since the procedure resolves its input through that directory alias. The procedure then inserts one LOB record per source file and returns the generated FND_LOBS primary key, which the surrounding migration logic stores in the attachment-relationship tables so existing references resolve to the new content. Because the package is declared AUTHID CURRENT_USER, privileges are evaluated as the invoking user. In 12.1.1 and 12.2.2 the package remains present as delivered legacy code; custom callers should invoke it only in controlled migration contexts and must handle the OUT identifier explicitly.