Results for “fnd_oracle_schema”

31 results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

FND_ORACLE_SCHEMA is a low-level Oracle E-Business Suite utility package owned by the APPS schema. Its purpose is to resolve and validate information about registered Oracle database schemas, particularly those designated as "external" or application-owned schemas that sit outside the standard APPS database account. The package bridges two metadata stores: the FND_LOOKUP_VALUES lookup table, which holds configurable external schema definitions, and FND_ORACLE_USERID, Oracle EBS's central registry of database users and their associated Oracle IDs. In the context of EBS 12.1.1 and 12.2.2, this package supports the EBS security model, where APPS connects to other schemas through a trusted relationship rather than direct credentials. The "external_schema" search term maps to the lookup type EXTERNAL_SCHEMA referenced inside the package body, which is the key that ties caller-supplied lookup codes to schema identifiers. The package is small, containing only two functions, but it plays a supporting role in schema-to-APPS authentication and naming resolution. Because it is an internal, undocumented-support package, Oracle does not publish a formal public API contract for it, and its header indicates a 2005-era file revision (AFSCSHMB.pls 120.1) that has remained stable across the 12.1.1 and 12.2.2 releases.

Key Procedures and Functions

  • GetOuValue — Retrieves the "tag" value from FND_LOOKUP_VALUES for a given lookup code, restricted to the EXTERNAL_SCHEMA lookup type and the caller's current language (userenv('LANG')). It returns an empty string when no matching lookup row exists, and otherwise propagates unexpected errors. This function is the mechanism by which a caller maps a short external-schema lookup code to its configured tag value.
  • GetOpValue — Accepts a schema name and the APPS schema password, validates that the schema name corresponds to an existing Oracle user registered in FND_ORACLE_USERID, and then delegates to FND_WEB_SEC.GET_OP_VALUE to produce the oracle password (OP) value used in APPS-to-schema authentication. If the schema is unknown, it raises the FND message FND_NO_SCHEMA_NAME with the offending schema name as a token. If either argument is null, it returns an empty string.

Tables Accessed

  • FND_LOOKUP_VALUES — Read by GetOuValue to resolve lookup codes under the EXTERNAL_SCHEMA lookup type, filtering on the session language.
  • FND_ORACLE_USERID — Read by GetOpValue to confirm that the supplied schema name exists as a registered Oracle user and to obtain its Oracle ID before authentication proceeds.

Usage Notes

FND_ORACLE_SCHEMA is invoked indirectly rather than by end users. Its functions are typically called from Oracle Forms, concurrent program logic, or other PL/SQL packages that need to resolve external schema lookup codes or validate an APPS-to-schema connection. The documented metadata records that the package is referenced by one other package, confirming it is a dependency of higher-level EBS security or connectivity code. Customizations should treat this package as internal and read-only: parameter lists and return behavior are not guaranteed across patches. Because GetOpValue depends on a valid FND_ORACLE_USERID registration and a correct APPS password, it is used during runtime schema authentication, while GetOuValue is used for configuration lookups. The EXTERNAL_SCHEMA lookup type must be maintained in FND_LOOKUP_VALUES for GetOuValue to return meaningful values.