OraParameter Object


An OraParameter object represents a bind variable in a SQL statement or PL/SQL block.


OraParameter 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. You can automatically bind a parameter to SQL and PL/SQL statements of other objects (as noted in the object descriptions), by using the parameter name as a placeholder in the SQL or PL/SQL statement. Using parameters can simplify dynamic queries and increase program performance.

Parameters are bound to SQL statements and PL/SQL blocks before execution. In the case of a SQL SELECT statement, binding occurs before dynaset creation.

The OraParameters collection is part of the OraDatabase object. Therefore, all parameters are available to any SQL statement or PL/SQL block executed within the database (through the CreateDynaset or ExecuteSQL methods).

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 does not apply to that particular SQL statement or PL/SQL block), are noted and no attempt is made to bind them again if the SQL statement or PL/SQL block is reexecuted but does not change.

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

By default, the maximum size of the ORAPARM_OUTPUT variable for ServerType CHAR and VARCHAR2 is set to 127 bytes. Use the MinimumSize property to change this value. The minimum size of an ORAPARM_OUTPUT variable for CHAR, VARCHAR2, and ORATYPE_RAW_BIN must always be greater than the size of the expected data from the database column.

ServerType ORATYPE_RAW_BIN is used when binding to Oracle Raw columns. A byte array is used to put or get values. The maximum allowable size of ORATYPE_RAW_BIN bind buffers is 2000 bytes when bound to a column of a table, 32 KB when bound to a stored procedure. For example code, see the samples in the ORACLE_BASE\\ORACLE_HOME\OO4O\VB\Raw directory.