OraParamArray Object

Description

An OraParamArray object represents an "array" type bind variable in a SQL statement or PL/SQL block as opposed to a "scalar" type bind variable represented by the OraParameter object.

Remarks

OraParamArray objects are created, accessed, and removed indirectly through the OraParameters collection of an OraDatabase object. Each parameter has an identifying name and an associated value.

Implicitly an OraParamArray object contains an OLE automation collection interface for accessing and manipulating individual elements of an array. Individual elements can be accessed using a subscript or the Get_Value method. Individual elements can be modified by using a subscript or the Put_Value method. Element values are retrieved as Variant types. The Variant type of the element depends on the ServerType of the OraParamArray object. Element values can be Null and can be set to Null. For elements of type objects and REFs, element values are returned as corresponding OO4O objects for that type.

You can automatically bind a parameter to SQL and PL/SQL statements of other objects (as noted in the objects descriptions) by using the parameters name as a placeholder in the SQL or PL/SQL statement. Such use of parameters can simplify dynamic queries and increase program performance. Parameters are bound to SQL statements and PL/SQL blocks before execution.

The OraParameters collection is part of the OraDatabase object so that all parameters are available to any SQL statement or PL/SQL block executed within the database (through CreateDynaset, ExecuteSQL, or CreateSQL). Before a SQL statement or PL/SQL block is executed, an attempt is made to bind all parameters of the associated OraDatabase object. The bindings that fail (because the parameter doesn't apply to that particular SQL statement or PL/SQL block) are noted and no attempt is made to rebind them if the SQL statement or PL/SQL block is re-executed but doesn't change.

Because neither SQL statements nor PL/SQL blocks are parsed locally (all parsing is done by Oracle), any unnecessary binding results in performance degradation. To prevent unnecessary parameter binding, make use of the AutoBindDisable and AutoBindEnable methods.