Search Results ast_debug_pub




Overview
Explore in AI Chat

Overview

AST_DEBUG_PUB is a public PL/SQL diagnostic utility package owned by the APPS schema in Oracle E-Business Suite. Despite the "AST" prefix, which ordinarily associates a module with the Oracle Sales and Marketing (AS) product family, the package functions as a generic, reusable debugging facility. Its purpose is to provide a lightweight file-based tracing mechanism that lets developers and support analysts capture runtime diagnostic messages from PL/SQL code without requiring a formal debugger or database tracing tool. Declared with AUTHID CURRENT_USER, it executes under the privileges of the calling user, making it widely callable from other packages and concurrent programs.

The package relies on profile options to govern its behavior. The debug level is initialized from the profile IEX_DEBUG_LEVEL, evaluated through FND_PROFILE.VALUE, and the output directory is drawn from the profile UTL_FILE_DIR. This profile-driven design means the same package code can be shipped to all environments while output verbosity and destination are controlled administratively, without code changes.

Key Procedures and Functions

  • OPENFILE — A function that opens a file handle for diagnostic output. If a filename is supplied, it opens that file; if none is provided, it creates a new file. The returned value provides the caller with a usable file reference.
  • LOGMESSAGE — A procedure that appends a debugging message to the open file. Messages are written conditionally: the message is emitted only when the incoming debug level exceeds the active G_DEBUG_LEVEL threshold set by profile. An optional flag controls whether a date/time stamp accompanies the message.
  • SETDEBUGLEVEL — A procedure that programmatically overrides the internal debug level. This is intended for use when the package executes outside the normal application runtime context, where the IEX_DEBUG_LEVEL profile is not available or not set.

Together these three documented entry points form a minimal open–log–configure triad: establish an output file, conditionally record messages against a threshold, and adjust that threshold when running in an unusual session context.

Tables Accessed

The package does not read or write application business tables. Its documented external references are UTL_FILE, the Oracle-supplied file I/O package used to write the trace file; and the dynamic performance views V$PARAMETER and V$SESSION, consulted from the APPS schema. Access to V$PARAMETER supports determination of the allowed UTL_FILE directory settings, while V$SESSION allows the package to associate diagnostic output with the originating database session. Log records are persisted to the operating-system file referenced by UTL_FILE_DIR rather than to any database table, which keeps the mechanism inexpensive and avoids transactional side effects.

Usage Notes

AST_DEBUG_PUB is typically invoked from custom or extension PL/SQL: a caller opens a debug file via OPENFILE, scatters LOGMESSAGE calls at strategic points, and optionally forces a level through SETDEBUGLEVEL. Because the effective verbosity is determined by the IEX_DEBUG_LEVEL profile, the same code can run silently in production and become verbose in a test instance by adjusting a profile value. The ETRM metadata records that the package is referenced by six other packages, confirming its role as a shared diagnostic service rather than an end-user-facing component. Its use should be confined to troubleshooting scenarios. The file destination must be a directory defined in the database's UTL_FILE_DIR initialization parameter, and administrators should enable output only for the duration of an investigation, since unbounded message logging can produce large files and add overhead to the calling session.