Search Results resolve_exec_name




Overview
Explore in AI Chat

Overview

SYS.DBMS_SQLTUNE_UTIL2 is an internal Oracle server-side PL/SQL utility package that supports the SQL Tuning Advisor and SQL Tuning Set (STS) infrastructure. It is owned by SYS and is declared with AUTHID CURRENT_USER, meaning that its unqualified references to database objects are resolved in the invoker's schema rather than the definer's schema. Within an Oracle E-Business Suite 12.1.1 or 12.2.2 environment, this package is not an EBS business API; it is a database kernel component shipped with the Oracle RDBMS that EBS inherits through its underlying database tier. Its practical role is to provide low-level helper primitives used by the higher-level DBMS_SQLTUNE package and by SQL Tuning Set pack/unpack operations.

The reference to load_sqlset in the package comments confirms that this utility package exists primarily to prepare and marshal bind data and snapshot metadata for consumption by the SQL Tuning Set load path. When an EBS administrator unpacks an STS from a staging table or moves an STS between databases, the bind variables captured at SQL execution time must be converted from the nested-table representation stored in the staging table into the VARRAY form required by the SQLSET_ROW structure understood by load_sqlset. That conversion is performed here.

Key Procedures and Functions

The ETRM metadata documents seven procedures and functions in this package:

  • RESOLVE_USERNAME — Returns the current schema owner when passed a NULL name, otherwise validates and returns the supplied user name. It supports an optional container-id argument for multitenant resolution.
  • VALIDATE_SNAPSHOT — Validates whether a begin/end snapshot ID interval is well formed, raising an error for an invalid range. It supports a fully-inclusive or half-inclusive boundary mode.
  • SQL_BINDS_NTAB_TO_VARRAY — Converts SQL bind data from the nested-table form held in the staging table into the VARRAY type used by SQLSET_ROW. The package comments explicitly state it is called by unpack_stgtab_sqlset because binds must be passed as a VARRAY to load_sqlset.
  • SQL_BINDS_VARRAY_TO_NTAB — Performs the inverse conversion, taking bind data from the VARRAY representation back into the nested-table form used by the staging table.
  • CHECK_PRIV — Verifies that the calling user possesses the privileges required to perform the requested SQL Tuning Set operation.
  • RESOLVE_EXEC_NAME — Resolves the executable name associated with a captured SQL statement or tuning operation for consistent display and identification.
  • GET_TIMING_INFO — Retrieves timing information associated with a tuning or capture operation, used for diagnostics and reporting.

Tables Accessed

Although the ETRM metadata lists no APPS-synonym tables for this package, the function descriptions make clear that it operates on the SQL Tuning Set staging table used by PACK_STGTAB_SQLSET and UNPACK_STGTAB_SQLSET. That staging table is created by the caller (typically in the EBS APPS schema) and holds serialized SQLSET_ROW data, including the nested-table representation of bind variables that this package converts. The package reads the bind columns from that staging table and writes them into the VARRAY structures passed onward to load_sqlset. Snapshot ID values stored in the Automatic Workload Repository are validated for interval correctness. No EBS application tables are read or written.

Usage Notes

DBMS_SQLTUNE_UTIL2 is not intended for direct invocation from EBS forms, concurrent programs, or custom application code. It is an internal support package, and Oracle does not guarantee its interface across patches or upgrades. In practice it is invoked indirectly: an EBS DBA or performance engineer uses DBMS_SQLTUNE to create, pack, unpack, or load SQL Tuning Sets, and those operations call into this utility package. Common EBS touchpoints include SQL Tuning Advisor runs, SQL plan baseline maintenance, and STS transport between the production and test databases used during EBS upgrade or performance tuning cycles. Because the package is AUTHID CURRENT_USER, grants on any staging table and on the underlying tuning views must be assigned to the invoking schema. Custom code should call the documented DBMS_SQLTUNE entry points rather than the utility routines described here.