Search Results edw_logfile_dir

Overview

EDW_COMPILE_PACKAGES is a utility package body owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the Oracle Enterprise Data Warehouse (EDW) product family and is classified under the ETRM as an "OTHER" API, meaning it is an internal maintenance utility rather than a business API exposed for external integration. Its purpose is to identify and recompile invalid PL/SQL package specifications and package bodies that exist in the current (APPS) schema, emitting a diagnostic log of the operation.

The package addresses a common operational condition in EBS environments: after patching, upgrades, or dependency changes, compiled PL/SQL units may be left in an INVALID state. This package scans for such units and attempts to recompile them automatically, reducing manual DBA intervention and providing an auditable log of what was recompiled and what failed.

Key Procedures and Functions

The documented package exposes a single procedure, COMPILE_PACKAGES. It accepts an error buffer, a return code, and an optional expression parameter that acts as a name filter for the objects to be processed. When the filter is omitted, the procedure defaults to a wildcard, treating all invalid package units as candidates.

The procedure operates in two sequential passes. First, it queries for invalid PACKAGE specifications and issues an ALTER PACKAGE ... COMPILE statement for each one found. It then repeats the scan for invalid PACKAGE BODY units and compiles those as well. Each success or failure is written to the log; exceptions raised during an individual compile are caught, recorded, and suppressed so that processing continues with the remaining objects rather than aborting the run. The procedure also sets the session's GLOBAL_NAMES parameter to FALSE before proceeding.

Tables Accessed

The package reads a single documented data dictionary view: USER_OBJECTS, accessed through an APPS synonym. The view is queried with a DISTINCT cursor restricted to rows where OBJECT_TYPE matches the requested unit type (PACKAGE or PACKAGE BODY), STATUS equals 'INVALID', and OBJECT_NAME matches the supplied filter expression. This is the mechanism by which the package obtains its work list; no application tables are read or written. The package is not referenced by any other package in the documented dependency set, confirming its role as a standalone utility.

Usage Notes

EDW_COMPILE_PACKAGES is typically invoked during or immediately after the application of EDW-related patches, when the patching process may leave dependent package units invalid. Because it operates against USER_OBJECTS in the APPS schema, it must be executed in a session connected as APPS, or as a user with equivalent visibility into the APPS dictionary objects.

The log destination is resolved at runtime through profile options. The procedure first attempts to read the UTL_FILE_LOG profile; if that is not set, it falls back to EDW_LOGFILE_DIR, and if neither is defined it defaults to the server path /sqlcom/log. This fallback chain is significant for administrators searching on edw_logfile_dir: the profile option name, rather than a fixed directory, controls where validate.log and validate.out are created, and the hard-coded /sqlcom/log path applies only when both profiles are unset. Because UTL_FILE output is written on the database server, the directory must be one registered for UTL_FILE access and writable by the database process.

Typical invocations are direct SQL*Plus calls or small driver scripts rather than concurrent programs or forms initiated by end users. The output log should be reviewed after execution to identify packages that failed recompilation, which generally indicates unresolved dependencies requiring further remediation.