Search Results do_binds

Overview

FND_DSQL is a foundational Oracle E-Business Suite PL/SQL utility package owned by the APPS schema and classified as an "OTHER" API type within the ETRM repository. Its primary business function is to provide a centralized dynamic SQL construction mechanism that decouples SQL text assembly from bind variable management. In the Oracle EBS architecture, many framework components—flexfields, concurrent managers, and profile utilities—must generate SQL statements at runtime whose text and bind values are assembled across multiple procedure calls or programmatic layers. FND_DSQL solves this by acting as a session-scoped accumulator: callers append literal SQL text fragments and corresponding bind values independently, and the package assembles a complete, bind-aware SQL string with generated bind variable placeholders such as :FND_BIND1, :FND_BIND2, and so on.

This design directly addresses an Oracle performance concern documented in the package's own header comments: when a SQL statement is used frequently and its bind variables change on each execution, binding must be used to enable cursor sharing and avoid hard parses. Because a single SQL statement may be constructed across different procedures that cannot easily pass all bind arguments between them, FND_DSQL maintains the bind state centrally and applies it at execution time through DBMS_SQL.

Key Procedures and Functions

The documented interface consists of nine procedures and functions:

• INIT — Initializes the package state, clearing any previously accumulated SQL text and bind variables so that a new dynamic statement can be constructed. It is called at the start of each construction cycle.
• ADD_TEXT — Appends a literal SQL text fragment (for example, a SELECT clause or a WHERE predicate prefix ending in an equals sign) to the accumulating statement buffer.
• ADD_BIND — Registers a bind value with the package, associating it with a generated placeholder in the assembled SQL text. Called immediately after the corresponding ADD_TEXT fragment.
• SET_CURSOR — Associates the package with an open DBMS_SQL cursor handle so that subsequent binding and parsing operations target the correct cursor context.
• DO_BINDS — Performs the actual DBMS_SQL bind calls, binding all accumulated values to their placeholders on the current cursor before execution.
• GET_TEXT — Returns the assembled SQL statement. A Boolean argument controls whether debug-formatted text (with substituted values or expanded output) or the native bind-placeholder text is returned.
• FND_DSQL_TEST — A self-test or demonstration routine used to validate package behavior, mirroring the example workflow shown in the package header.

Additional internal helper routines are implied by the workflow but only the above are externally documented.

Tables Accessed

FND_DSQL does not read or write application data tables. Its two documented dependencies are DBMS_SQL, the Oracle-supplied dynamic SQL package used to open cursors, parse the assembled statement, bind variables, and execute, and PLITBLM, a PL/SQL indexed table (associative array) type used internally to hold the collection of bind values accumulated during statement construction. Because the package operates entirely against transient in-memory structures and DBMS_SQL handles, it carries no direct data-model footprint; any application tables it ultimately queries are determined by the SQL text supplied by the caller.

Usage Notes

FND_DSQL is a developer-facing utility rather than an end-user feature. It is invoked from PL/SQL code in forms, concurrent programs, and custom extensions whenever a dynamic statement must be built incrementally while retaining bind-variable performance benefits. The canonical usage pattern proceeds as follows: call INIT; alternate ADD_TEXT and ADD_BIND calls to build the statement; open a cursor with DBMS_SQL and register it via SET_CURSOR; retrieve the bind-placeholder SQL through GET_TEXT(FALSE) and parse it; invoke DO_BINDS and then execute. GET_TEXT(TRUE) is used for diagnostic output.

The ETRM metadata records that FND_DSQL is referenced by 23 other packages, confirming its role as shared infrastructure within the EBS technology stack. The package version header (AFUTSQLS.pls 120.1.12010000.1, dated 2008) is common to both Release 12.1.1 and 12.2.2, so the interface and behavior described here are consistent across both releases. Custom code should treat FND_DSQL as a stable but unsupported internal API, and should avoid interfering with its session state between unrelated dynamic SQL constructions.