Search Results do_apps_ddl

Overview

SYSTEM.AD_INST is a low-level Oracle E-Business Suite utility package whose package body provides the recompilation engine used across the EBS database schema. Its primary business function is to bring invalid database objects — packages, views, and package bodies — back to a valid state after patching, upgrades, or schema changes. Because an EBS instance can contain tens of thousands of interdependent PL/SQL objects, a controlled recompilation mechanism is essential to restore the dependency chain and keep the application operational. The package is owned by SYSTEM and is classified as OTHER within the EBS Technical Reference Manual (ETRM). The header comment, dated 2005, indicates the code has remained stable across the 11i and R12 code lines, including 12.1.1 and 12.2.2.

Key Procedures and Functions

Two documented procedures constitute the public interface of AD_INST.

• COMPILE_SCHEMA — Accepts a target schema name and recompiles all invalid objects belonging to that schema. It queries DBA_OBJECTS for objects whose status is INVALID and processes them in a deliberate order: package specifications first, then views, then package bodies. This ordering reflects the dependency rules of PL/SQL — a view may reference a package header, and a package body depends on its specification — so recompiling in the correct sequence minimizes cascading invalidation. The procedure opens with error-buffer initialization for AD_INST and AD_APPS_PRIVATE, invokes AD_APPS_PRIVATE.CHECK_FOR_APPS_DDL to verify the presence of APPS*DDL helper packages, and routes each ALTER statement through DO_APPS_DDL. It explicitly traps ORA-24344 (success with compilation error) via a declared exception, an Oracle 8-era behavior retained for backward compatibility.
• DO_APPS_DDL — Executes the dynamically constructed DDL statements generated by COMPILE_SCHEMA. As the execution gateway for ALTER PACKAGE and ALTER VIEW commands, it centralizes DDL dispatch, allowing AD_INST to run compilation statements consistently without embedding raw EXECUTE IMMEDIATE logic at every call site.

Tables Accessed

AD_INST reads data dictionary views through APPS synonyms. DBA_OBJECTS is the central source of truth, supplying the object name and object type for every invalid object in the target schema; three separate cursors query it to isolate package specifications, views, and the general invalid-object set. DBMS_SQL, the Oracle-supplied dynamic SQL package, is referenced to parse and execute the generated ALTER statements. The package also depends on the companion objects AD_APPS_PRIVATE (for CHECK_FOR_APPS_DDL and error-buffer sharing) and the AD_INST specification itself.

Usage Notes

AD_INST is an internal administrative utility rather than an end-user-facing API. It is most often invoked indirectly during AD patching cycles, upgrade driver runs (adpatch and related utilities), and post-patch cleanup, where invalid objects must be revalidated before the application resumes normal operation. DBAs may also call it manually from SQL*Plus — typically as SYSTEM.AD_INST.COMPILE_SCHEMA('APPS') — after applying custom code or performing a schema-level change that leaves objects INVALID. Because AD_INST is referenced by two other packages, customizations should avoid modifying it directly; extensions should instead wrap COMPILE_SCHEMA. In 12.1.1 and 12.2.2, the online patching architecture of 12.2.x increases the importance of reliable recompilation, since editioning and crossedition triggers can render objects invalid across multiple editions. Automated batch recompilation through AD_INST remains the preferred route over ad hoc ALTER statements, as it enforces the correct object-ordering discipline and proper error handling.