Results for “dbms_sql”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

SYS.DBMS_SQL is the Oracle-supplied PL/SQL package body that implements dynamic SQL within the database kernel. It provides a procedural interface through which PL/SQL programs can parse, execute, and fetch from SQL statements that are not known at compile time. In the Oracle E-Business Suite 12.1.1 and 12.2.2 environments, this package is a foundational dependency of the technology stack rather than an application-specific API. Its documented status is VALID, it resides in the SYS schema, and its API classification in the ETRM repository is OTHER, reflecting its role as a database infrastructure component rather than an EBS business API. The package body is referenced by 131 other documented packages across the EBS schema, which underscores its pervasive role in supporting dynamic query construction, ad-hoc reporting, and metadata-driven processing throughout the applications.

Key Procedures and Functions

The ETRM metadata documents 233 procedures and functions in SYS.DBMS_SQL. The core cursor lifecycle is managed by OPEN_CURSOR, which allocates a cursor handle, IS_OPEN, which tests whether a cursor is currently open, and CLOSE_CURSOR, which releases it. Statement preparation is performed by PARSE. Data transfer into and out of dynamic statements is handled by BIND_VARIABLE and its typed variants BIND_VARIABLE_CHAR, BIND_VARIABLE_RAW, and BIND_VARIABLE_ROWID, together with BIND_ARRAY for array-based binding. Result set definition is accomplished through DEFINE_COLUMN and the typed forms DEFINE_COLUMN_CHAR, DEFINE_COLUMN_RAW, and DEFINE_COLUMN_ROWID, with DEFINE_ARRAY supporting array fetches. Execution and retrieval are performed by EXECUTE, FETCH_ROWS, and the combined EXECUTE_AND_FETCH. Retrieved values are extracted using COLUMN_VALUE and its typed counterparts COLUMN_VALUE_CHAR and COLUMN_VALUE_RAW. This taxonomy reflects the classic DBMS_SQL programming model in which callers explicitly open, parse, bind, define, execute, fetch, and close.

Tables Accessed

The ETRM metadata records no direct table references for SYS.DBMS_SQL through APPS synonyms. This is consistent with the package's architecture: DBMS_SQL operates on the SQL layer itself, executing caller-supplied statements against whatever objects those statements name, rather than maintaining its own persistent data store. Any table access attributed to DBMS_SQL is therefore a consequence of the dynamic SQL text passed by the calling program, not an intrinsic dependency of the package body.

Usage Notes

Within Oracle EBS, DBMS_SQL is typically invoked from PL/SQL-based concurrent programs, database triggers, workflow background processes, and custom extensions that must construct SQL dynamically, for example when table or column names are determined at runtime or when DDL must be issued from PL/SQL. It is also used where native dynamic SQL cannot meet requirements, such as when the number or types of bind variables are not known until execution. Because the package is owned by SYS and is not referenced by any database object in the ETRM dependency listing beyond its own internal callers, developers should treat it as a system-provided interface to be called, not extended. In 12.1.1 and 12.2.2 alike, the package is present and VALID as part of the standard database installation, and its internal dependencies include DBMS_STANDARD, DBMS_SYS_SQL, STANDARD, UTL_IDENT, and SYS_STUB_FOR_PURITY_ANALYSIS.