Search Results dbms_sys_sql

Overview

SYS.DBMS_SYS_SQL is an Oracle-supplied, undocumented PL/SQL package body owned by the SYS schema. It is a foundational utility used internally by Oracle Database and by Oracle E-Business Suite components that must execute dynamically constructed SQL against an arbitrary schema. In EBS 12.1.1 and 12.2.2, the package is a critical enabler of the AD utilities, patching infrastructure, and administrative tooling that require the ability to parse and execute SQL as a user other than the connected session, and to perform DDL operations within the context of the database itself. Because it is owned by SYS and protected, it is not intended for direct invocation by application developers; nonetheless, its presence is validated as part of the standard EBS database object inventory, and its status is reported as VALID in the ETRM dependency tree.

The documented dependency list confirms the package is layered on top of DBMS_SQL, the standard dynamic SQL interface, and relies on DBMS_STANDARD, STANDARD, UTL_IDENT, and SYS_STUB_FOR_PURITY_ANALYSIS for its supporting routines and purity assertions. It is not referenced by any other database object in the documented graph, but it is referenced by 69 other packages, which indicates it functions as a low-level dependency for a broad set of internal Oracle and EBS maintenance routines.

Key Procedures and Functions

The ETRM metadata records zero documented procedures or functions for SYS.DBMS_SYS_SQL. The object is catalogued as an API classified "OTHER," and no published specification is provided in the EBS documentation set. In practice the package body contains internal entry points such as PARSE_AND_EXECUTE and related routines used to compile and run SQL statements under a different schema owner. Because these interfaces are undocumented and unsupported, they are not intended for direct use, and parameter lists are deliberately omitted here. Administrators and developers who require dynamic SQL execution should use the supported DBMS_SQL or EXECUTE IMMEDIATE facilities instead.

Tables Accessed

No tables are documented as accessed through APPS synonyms, which is expected for a package whose purpose is to execute arbitrary statements rather than to read or write a fixed set of application tables. The package operates against the data dictionary and against whatever objects the dynamically submitted SQL references; therefore the effective table footprint is determined at runtime by the caller.

Usage Notes

In EBS, SYS.DBMS_SYS_SQL is invoked indirectly through database utilities that need to execute SQL as another schema. When a session executes dynamic SQL under a different owner, the call is channelled through the calling package, which in turn delegates to DBMS_SQL. Typical consumers include patch drivers and administrative scripts executed during upgrades or maintenance windows. Direct invocation from EBS forms, concurrent programs, or custom code is not supported and is strongly discouraged.

Do not grant EXECUTE on SYS.DBMS_SYS_SQL to application schemas; doing so undermines the security model.
Use DBMS_SQL or EXECUTE IMMEDIATE for dynamic SQL in custom code; these are documented and supported.
If the package becomes INVALID after an upgrade or patch, recompile it as SYS or run utlrp.sql to restore validity.
Its VALID status should be verified as part of standard EBS health checks, since dependent maintenance routines may fail if it is not recompiled.

SYNONYM: SYSTEM.DBMS_SYS_SQL 12.2.2
owner:SYSTEM, object_type:SYNONYM, object_name:DBMS_SYS_SQL, status:VALID,
SYNONYM: SYSTEM.DBMS_SYS_SQL 12.1.1
owner:SYSTEM, object_type:SYNONYM, object_name:DBMS_SYS_SQL, status:VALID,
PACKAGE BODY: SYS.DBMS_SYS_SQL 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SYS_SQL, status:VALID,
PACKAGE BODY: SYS.DBMS_SYS_SQL 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SYS_SQL, status:VALID,
PACKAGE: SYS.UTL_IDENT 12.1.1
owner:SYS, object_type:PACKAGE, object_name:UTL_IDENT, status:VALID,
PACKAGE: SYS.UTL_IDENT 12.2.2
owner:SYS, object_type:PACKAGE, object_name:UTL_IDENT, status:VALID,
12.2.2 DBA Data 12.2.2
PACKAGE: SYS.DBMS_SYS_SQL 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_SYS_SQL, status:VALID,
PACKAGE: SYS.DBMS_SYS_SQL 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_SYS_SQL, status:VALID,
12.1.1 DBA Data 12.1.1
PACKAGE: SYS.DBMS_REPCAT_RQ 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_REPCAT_RQ, status:VALID,
PACKAGE BODY: OWAPUB.OWA_OPT_LOCK 12.1.1
owner:OWAPUB, object_type:PACKAGE BODY, object_name:OWA_OPT_LOCK, status:VALID,
PACKAGE BODY: SYS.DBMS_ITRIGGER_UTL 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_ITRIGGER_UTL, status:VALID,
PACKAGE BODY: SYSTEM.WPG_DOCLOAD 12.2.2
owner:SYSTEM, object_type:PACKAGE BODY, object_name:WPG_DOCLOAD, status:VALID,
PACKAGE BODY: SYSTEM.WPG_DOCLOAD 12.1.1
owner:SYSTEM, object_type:PACKAGE BODY, object_name:WPG_DOCLOAD, status:VALID,
PACKAGE: SYS.DBMSOBJG_DP 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMSOBJG_DP, status:VALID,
PACKAGE: SYS.DBMS_REPCAT_RQ 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_REPCAT_RQ, status:VALID,
PACKAGE BODY: SYS.DBMS_SQL2 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQL2, status:VALID,
PACKAGE BODY: SYS.OWA_OPT_LOCK 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:OWA_OPT_LOCK, status:VALID,
PACKAGE BODY: SYS.WPG_DOCLOAD 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:WPG_DOCLOAD, status:VALID,
PACKAGE BODY: SYS.OWA_OPT_LOCK 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:OWA_OPT_LOCK, status:VALID,
PACKAGE: SYS.DBMSOBJG_DP 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMSOBJG_DP, status:VALID,
PACKAGE: SYS.DBMSOBJG2 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMSOBJG2, status:VALID,
PACKAGE BODY: SYS.DBMS_EXPORT_EXTENSION 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_EXPORT_EXTENSION, status:VALID,
PACKAGE BODY: SYSTEM.AD_APPS_PRIVATE 12.1.1
owner:SYSTEM, object_type:PACKAGE BODY, object_name:AD_APPS_PRIVATE, status:VALID,
PACKAGE BODY: SYS.DBMS_OFFLINE_INTERNAL 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_OFFLINE_INTERNAL, status:VALID,
PACKAGE BODY: SYS.DBMS_ITRIGGER_UTL 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_ITRIGGER_UTL, status:VALID,
PACKAGE BODY: SYS.DBMS_SQL 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQL, status:VALID,
PACKAGE BODY: SYS.DBMS_LOGMNR_LOGREP_DICT 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_LOGMNR_LOGREP_DICT, status:VALID,
PACKAGE: SYS.DBMSOBJG 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMSOBJG, status:VALID,
PACKAGE BODY: SYS.DBMS_DDL 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_DDL, status:VALID,
PACKAGE BODY: SYS.DBMS_LOGMNR_LOGREP_DICT 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_LOGMNR_LOGREP_DICT, status:VALID,
PACKAGE BODY: SYS.OWA_UTIL 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:OWA_UTIL, status:VALID,
PACKAGE: SYS.DBMSOBJG2 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMSOBJG2, status:VALID,
PACKAGE BODY: SYS.DBMS_SQL 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQL, status:VALID,
PACKAGE: SYS.DBMS_OFFLINE_UTL 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_OFFLINE_UTL, status:VALID,
PACKAGE BODY: SYS.DBMS_REPCAT_OUTPUT 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_REPCAT_OUTPUT, status:VALID,
PACKAGE: SYS.DBMS_OFFLINE_UTL 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_OFFLINE_UTL, status:VALID,
PACKAGE BODY: SYS.DBMSOBJGWRAPPER 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMSOBJGWRAPPER, status:VALID,
PACKAGE BODY: SYS.XS_DATA_SECURITY_UTIL 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:XS_DATA_SECURITY_UTIL, status:VALID,
PACKAGE BODY: SYS.PRVT_SYS_TUNE_MVIEW 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:PRVT_SYS_TUNE_MVIEW, status:VALID,
PACKAGE BODY: SYS.DBMS_OFFLINE_UTL 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_OFFLINE_UTL, status:VALID,
PACKAGE BODY: SYS.WPG_DOCLOAD 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:WPG_DOCLOAD, status:VALID,
PACKAGE BODY: OWAPUB.OWA_UTIL 12.1.1
owner:OWAPUB, object_type:PACKAGE BODY, object_name:OWA_UTIL, status:VALID,
PACKAGE BODY: SYS.DBMS_DEFERGEN_UTIL 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_DEFERGEN_UTIL, status:VALID,
PACKAGE BODY: SYS.DBMS_REPCAT_OUTPUT 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_REPCAT_OUTPUT, status:VALID,
PACKAGE BODY: SYS.DBMS_SQL_TRANSLATOR_EXPORT 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQL_TRANSLATOR_EXPORT, status:VALID,
PACKAGE BODY: SYS.DBMS_OFFLINE_INTERNAL 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_OFFLINE_INTERNAL, status:VALID,
PACKAGE BODY: SYS.DBMS_DEFERGEN_UTIL 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_DEFERGEN_UTIL, status:VALID,
PACKAGE BODY: SYS.DBMS_SQL_TRANSLATOR_EXPORT 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQL_TRANSLATOR_EXPORT, status:VALID,