Search Results applsys_schema

Overview

AD_DDL is a SYSTEM-owned PL/SQL package that provides the dynamic DDL (Data Definition Language) execution engine used throughout Oracle E-Business Suite Release 12.1.1 and 12.2.2. Rather than issuing static DDL at compile time, EBS utilities and product-specific code call AD_DDL to construct, parse, and execute SQL and PL/SQL statements at runtime against the database. This capability is essential for operations that cannot be known until execution, such as generating objects in a specific schema (especially APPS), building packages or triggers on the fly, and executing arrays of DDL statements as part of patching, installation, or runtime configuration.

The package is documented as VALID in both releases. Under the 12.2.2 editioning and online patching architecture, dynamic object creation and DDL execution became more sensitive, because objects may exist as editioning views and are managed per edition. AD_DDL provides the abstraction layer that EBS code uses to handle those mechanics consistently.

Key Procedures and Functions

• DO_DDL — Executes a DDL statement supplied by the caller. This is the core entry point for single-statement dynamic DDL.
• DO_ARRAY_DDL — Executes a collection (array) of DDL statements, allowing multiple objects to be created or altered in a single invocation.
• CREATE_PACKAGE — Creates a PL/SQL package in the target schema, typically used when generating package specifications and bodies dynamically.
• CREATE_PLSQL_OBJECT — Creates a generic PL/SQL object (procedure, function, or related program unit) from supplied source.
• CREATE_TRIGGER_IN_SCHEMA — Creates a database trigger within a named schema, supporting runtime generation of trigger logic.
• BUILD_PACKAGE — Assembles the source text of a package from its component parts prior to creation.
• BUILD_STATEMENT — Constructs a DDL or SQL statement string from the caller's inputs, isolating the string-building logic from execution.
• EXTRACT_OBJECT_NAME — Parses a supplied statement or identifier to derive the object name, which is useful for logging, validation, and dependency tracking.

Tables Accessed

AD_DDL operates primarily through dynamic SQL rather than direct table access. The ETRM metadata records references to DBMS_SQL (the Oracle-supplied dynamic SQL package used to parse, bind, and execute statements), DBA_EDITIONING_VIEWS (queried to identify editioning views during online patching in 12.2.2), PLITBLM (the internal PL/SQL table used for handling large PL/SQL source text and collections), and FND_ORACLE_USERID (the EBS Applications tables that map Oracle database users to application user identities, used to resolve the correct schema for object creation). These references reflect the package's role in resolving schema context, assembling source, and executing dynamic statements rather than persisting application data.

Usage Notes

AD_DDL is an infrastructure package rather than a user-facing API. It is invoked indirectly by EBS utilities, concurrent programs, and product installation or patching logic that must create or alter database objects at runtime. Typical consumers include the AD Administration (adadmin) and AutoPatch processes, which rely on dynamic DDL to build objects in the APPS schema and other designated schemas, and product-specific routines that generate packages or triggers during configuration.

Because AD_DDL issues DDL directly and can create objects in arbitrary schemas, it should not be called casually from custom code. DDL statements execute with implicit commits, cannot be rolled back, and in 12.2.2 may interact with the editioning model. The metadata shows the package is referenced by zero other application packages in the documented dependency set, indicating its use is concentrated in internal EBS utilities and limited direct callers. Customizations requiring dynamic DDL should generally follow the same patterns, validate all identifiers and inputs before execution, and avoid hard-coding schema names. Calling AD_DDL against protected or seed-data objects risks invalidating dependencies and is unsupported unless performed through official AD utilities.