Search Results update_oracle_id

Overview

AD_UTIL is a low-level Oracle E-Business Suite database utility package owned by the APPS schema and shipped as part of the Applications DBA (AD) product family. Its purpose is to perform controlled schema-level maintenance operations against the EBS database itself, specifically the reassignment of Oracle user identifiers (Oracle IDs) and the promotion of a product schema into a shared (multiple-application) configuration. In the 12.1.1 and 12.2.2 releases, Oracle IDs occupy a materially different role than in earlier releases: beginning with Release 12, the APPS schema and the Oracle ID model were decoupled, and product schemas are consolidated under a single Oracle ID. Consequently, AD_UTIL is invoked during upgrade, migration, and environment-cloning activities where legacy Oracle ID references must be rewritten consistently across the data dictionary and the Application Object Library (AOL) metadata tables.

AD_UTIL is declared with AUTHID CURRENT_USER, meaning that all name resolution and privilege checking occurs in the schema of the caller rather than in APPS. This is characteristic of an installation-time utility: it is intended to be executed by a privileged administrative account, not by ordinary application sessions. The package header carries a 2004 revision marker, indicating that the interface has been stable across the Release 12 family.

Key Procedures and Functions

The ETRM metadata documents six procedure signatures, all public and all defined in the package header. Three distinct procedure names are exposed:

• UPDATE_COLUMN — Updates a single named column on a single named table, replacing an old Oracle ID value with a new one. It accepts the old Oracle ID, the new Oracle ID, the target table name, the target column name, and an option control parameter.
• UPDATE_ORACLE_ID — The central routine of the package, overloaded four times to accommodate progressively more explicit caller intent. The two-argument form takes a release indicator and the old Oracle ID only. The three-argument forms add either a new Oracle ID or an option control parameter. The four-argument form supplies the release indicator, the old Oracle ID, the new Oracle ID, and the option parameter together. The release parameter is annotated in the source as reserved for future use. The option parameter governs the scope and behavior of the identifier reassignment.
• SET_PROD_TO_SHARED — Converts a product schema from a dedicated Oracle ID to a shared Oracle ID configuration. It accepts a release indicator and the application short name of the product being converted.

No functions are declared; the package is entirely procedural. UPDATE_ORACLE_ID is the entry point most commonly referenced by administrators searching for this object, as it is the procedure that performs the bulk Oracle ID rewrite across all registered product schemas.

Tables Accessed

AD_UTIL works against a targeted set of AOL and data dictionary views through APPS synonyms. The Oracle ID registry itself is read and written through FND_ORACLE_USERID and its underlying FND_ORACLE_USERID_S table, which hold the mapping between Oracle IDs and the applications assigned to them. FND_APPLICATION supplies application short names and identifiers used during option-driven processing. FND_DATA_GROUPS and FND_DATA_GROUP_UNITS provide the data group definitions that determine which Oracle ID and schema combination a responsibility resolves to. FND_PRODUCT_INSTALLATIONS records which products are installed and their schema associations.

Schema and synonym discovery is performed dynamically through ALL_TABLES, ALL_TAB_COLUMNS, DBA_SYNONYMS, and USER_SYNONYMS. Dynamic SQL execution relies on DBMS_SQL, with DBMS_OUTPUT available for diagnostic tracing and DUAL used for trivial scalar queries. This combination indicates that UPDATE_ORACLE_ID does not operate on a hard-coded column list; it introspects the dictionary to find columns of type NUMBER holding Oracle ID values across APPS synonyms and updates them in place.

Usage Notes

AD_UTIL is an internal utility and is not exposed through a standard EBS form or a shipped concurrent program. It is normally invoked from SQL*Plus or a comparable administrative session connected as a user with sufficient privileges to query DBA views, update AOL tables, and run dynamic SQL against APPS synonyms. Typical invocations occur during Release 12 upgrade preparation, during environment cloning when Oracle IDs are renumbered, and during troubleshooting of mismatches between FND_ORACLE_USERID and the actual database users.

The ETRM metadata records no other packages referencing AD_UTIL, which confirms its status as a leaf-level administrative utility with no application-side dependents. Because it modifies core AOL and dictionary metadata directly, it bypasses application-level validation and should not be executed without a validated backup. Oracle does not document the meaning of the p_option parameter publicly; the overloads and the reserved p_release argument indicate that callers should supply values consistent with Oracle-supplied upgrade driver scripts rather than inventing their own. In 12.2.2 the procedure remains relevant chiefly for legacy maintenance paths, since the online patching architecture assumes the consolidated Oracle ID model established in Release 12.