Search Results exec_sql_with_binds




Overview
Explore in AI Chat

Overview

QA_CORE_PKG is a core PL/SQL package in the Oracle E-Business Suite Quality (QA) module, owned by the APPS schema and classified as an OTHER API. Its header identifies it as part of the Quality product library (as indicated by the qltcoreb.pls source file and the $Header revision marker). The package provides the foundational programmatic services required by the Quality collection plan and specification functionality, including resolving identifiers and names for collection elements and plans, validating mandatory elements, determining data types, and executing dynamic SQL without the constraints imposed by Oracle Forms built-ins.

A significant portion of the package is dedicated to dynamic SQL execution. The change history embedded in the source references Bug 3777530, described as a performance fix for literals. According to that commentary, two tables in the package were introduced to support the exec_sql_with_binds() procedure, which executes all client-side dynamic queries rather than routing them through FORMS_DDL(), since FORMS_DDL() restricts the use of bind variables. This design allows the package to issue dynamic statements with proper bind variable handling, improving performance and reducing hard parsing on the database server.

Key Procedures and Functions

  • GET_RESULT_COLUMN_NAME — Returns the unique column name in the QA_RESULTS table for a given element_id and plan_id combination.
  • GET_ELEMENT_ID — Returns the element id (char_id) corresponding to a supplied element name.
  • GET_PLAN_ID — Returns the plan id corresponding to a supplied plan name.
  • GET_PLAN_NAME — Returns the plan name corresponding to a supplied plan id.
  • IS_MANDATORY — Determines whether a specified element is mandatory for a specified plan; the caller must supply both the plan_id and element_id.
  • GET_ELEMENT_DATA_TYPE — Determines the data type of a collection element. The package documents an overloaded form of this function. Recognized data types are 1 for Character, 2 for Number, and 3 for Date.
  • EXEC_SQL — Executes a dynamically constructed SQL statement.
  • DEQUOTE — Removes quoting characters from a supplied value, typically used to normalize literals prior to dynamic execution.
  • EXEC_SQL_WITH_BINDS — Executes dynamic SQL using bind variables, implemented to avoid the bind-variable restrictions of FORMS_DDL().
  • DSQL_INIT — Initializes the dynamic SQL construct/context prior to adding text or bind values.
  • DSQL_ADD_TEXT — Appends SQL text to the dynamic SQL statement being constructed.
  • DSQL_ADD_BIND — Registers a bind value to be associated with the dynamic statement.
  • DSQL_EXECUTE — Executes the dynamic SQL statement that has been assembled.

Tables Accessed

The package accesses QA_CHARS, which stores collection element definitions, and QA_PLANS, which stores the collection plan definitions, in order to resolve element and plan identifiers, names, data types, and mandatory status. It also references DBMS_SQL, the Oracle-supplied dynamic SQL package used to parse, bind, and execute statements at runtime, and PLITBLM, which provides the PL/SQL table (associative array) infrastructure supporting the bind and text collections. The var_in_tab (VARCHAR2(100)) and value_in_tab (VARCHAR2(1000)) types declared in the header, indexed by BINARY_INTEGER, are the structures used by the dynamic SQL routines.

Usage Notes

QA_CORE_PKG is invoked from Quality module forms and from other PL/SQL packages; the ETRM metadata records that it is referenced by nine other packages. It is not intended as an end-user-facing interface but as an internal utility layer. Customizations that need to resolve element or plan identifiers, test mandatory status, or execute dynamic SQL with bind variables against Quality tables can call these public functions and procedures directly. Because the package is owned by APPS and marked as an OTHER classification rather than a formal public API, callers should validate behavior against their specific EBS release (12.1.1 or 12.2.2) and avoid relying on undocumented internal behavior.