Search Results dbms_sql




Overview
Explore in AI Chat

Overview

AD_ZD_PARALLEL_EXEC is a core Oracle EBS Online Patching (ADOP) infrastructure package body owned by the APPS schema. It forms part of the AD_Z* family of packages introduced with the Online Patching architecture and is central to the execution of the patch driver's parallel workers during an online patching cycle. Its business function is to parallelize and orchestrate the execution of DDL operations and object recompilation across multiple worker processes while a production environment remains online. The package coordinates work distribution, tracks job status, captures errors, and performs cleanup for parallel execution phases described in the patch driver metadata (ad_zd_* tables).

The user query referencing DBMS_SQL is significant: AD_ZD_PARALLEL_EXEC depends on the Oracle-supplied DBMS_SQL package in its dependency chain. DBMS_SQL provides dynamic SQL execution facilities, which this package requires because patching operations must execute DDL and DML statements that are only known at runtime and cannot be bound statically. This dependency distinguishes it from simpler packages that rely solely on native dynamic SQL, and it also references DBMS_LOB and DBMS_APPLICATION_INFO for large-object handling and progress instrumentation respectively.

Key Procedures and Functions

  • LOAD — Loads or initializes the set of parallel jobs to be processed for a given patching phase, establishing the work queue that subsequent procedures consume.
  • EXECUTE — The principal worker entry point. It retrieves assigned work items and performs the actual DDL or DML operations, typically using DBMS_SQL for dynamic statement execution.
  • GET_PHASE_ORDER — Returns the ordering of patching phases, ensuring that parallel workers observe the correct sequence of actions throughout the cycle.
  • GET_ERROR_LEVEL — Determines the current error severity or threshold, enabling the package to decide whether execution should continue or abort.
  • GETFAILED_JOBS — Retrieves jobs that terminated with an error, supporting diagnosis and potential retry logic.
  • GET_NOT_EXECUTED_JOBS — Identifies jobs that were never picked up or completed, which is essential for restartability and completeness checking.
  • RECOMPILE_OBJECTS — Recompiles invalid database objects, a necessary step after DDL changes so that dependent PL/SQL units become valid again.
  • UPDATEJOB_STATUS — Updates the status of an individual job as it transitions through pending, running, succeeded, or failed states.
  • UPDATE_STATUS_R_TO_N — Performs a bulk status update, moving records from a "running" state to a "not executed" state, typically during cleanup or reset after an interrupted run.
  • CLEANUP — Removes transient execution data and releases resources once a phase completes, maintaining the integrity of patching metadata.

Tables Accessed

The package reads and writes AD_ZD_DDL_HANDLER and its sequence AD_ZD_DDL_HANDLER_DDL_S, which hold the DDL operations managed during patching. It queries DBA_INVALID_OBJECTS to identify objects requiring recompilation following DDL application. DUAL is used for basic single-row evaluations. Its dependencies on DBMS_APPLICATION_INFO, DBMS_LOB, and DBMS_SQL represent package-level utilities rather than data tables: DBMS_APPLICATION_INFO provides session progress and module naming, DBMS_LOB handles large metadata values, and DBMS_SQL supplies dynamic SQL parsing and execution.

Usage Notes

AD_ZD_PARALLEL_EXEC is invoked internally by the ADOP patching infrastructure and by the parallel worker concurrent programs spawned during the apply, cutover, and cleanup phases of an online patching cycle. It is not intended for direct invocation from application forms. Because it is referenced by five other database objects, custom code should avoid calling its procedures directly and instead rely on the supported patching entry points. Administrators encountering invalid objects or stalled jobs during patching may inspect the status functions described above, but modifications should be performed only through documented ADOP utilities.