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.
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.