A parameter is an object that enables you to place variables within a SQL
statement. The simplest use would be in the SQL statement that opens a dynaset:
select * from mytable where column = :pvalue
In this example :pvalue is a parameter. (In SQL syntax, a parameter name is prefixed with a colon.)
When the SQL statement is used, the current value of the parameter is
substituted for :pvalue. Such a parameter can be used wherever a literal value can be placed: values
within "update" and "insert" statements and values that are part of "where"
clauses.
Parameters are also used to represent arguments in calls to stored procedures.
A stored procedure is a PL/SQL program that is stored in the Oracle database.
Parameters can be used both as input and as output variables. See the example
in ExecuteSQL for a sample of calling a stored procedure.
Parameters are managed as OParameter objects. OParameter objects are managed by way of an OParameterCollection that exists for every database object. You attach parameter objects to
databases by using the Add method of the OParameterCollection class. OParameter is a subclass of OOracleObject.
The primary benefit of using OParameter is efficiency. The Oracle database knows how its records are to be fetched by
remembering each SQL statement. When a precisely equal SQL statement is handed
to the server, it can be processed quickly, because the database simply reuses
the existing information. By using parameters instead of explicit values, you
do not have to change the SQL statement when the database values change.
Therefore, the SQL statement can be reused. This reuse is also convenient for you,
because you also can modify the SQL statement without having to retain the entire
string.
Parameters are attached to an ODatabase object. By default, whenever an ODynaset is opened or refreshed with a new SQL statement, all parameters that are
attached to the parent ODatabase (the ODatabase on which the ODynaset is being opened) are bound to the ODynaset. A bound OParameter is said to be "enabled". By default OParameters are auto-enabled. This means that they can bind to any ODynasets. By using the AutoEnable method, you can turn this default behavior off and on. This can be useful if
there are a large number of OParameter objects, which could result in many parameters being unnecessarily bound.
Such a condition does not cause errors, but may be inefficient.
When the parameter is created (using the OParameterCollection::Add method) you need to specify whether the parameter is used for input, output
or both. Use one of the following defines:
OPARAMETER_INVAR // input variable
OPARAMETER_OUTVAR // output variable
OPARAMETER_INOUTVAR // input and output variable
You can query the status of a parameter, in which case the parameter returns a
long that contains bits set to indicate the status. The bits are defined as:
OPARAMETER_STATUS_IN // this is an input variable
OPARAMETER_STATUS_OUT // this is an output variable
OPARAMETER_AUTOENABLED // this parameter is bound automatically
OPARAMETER_ENABLED // this parameter is ready to be bound
When the parameter is created you also need to specify the server type of the
parameter. The server type is one of the following Oracle types:
OTYPE_VARCHAR2
OTYPE_NUMBER
OTYPE_LONG
OTYPE_ROWID
OTYPE_DATE
OTYPE_RAW
OTYPE_LONGRAW
OTYPE_CHAR
OTYPE_MSLABEL
OTYPE_CURSOR
Please consult the OracleSQL Language Reference Manual for more information about these types. In general you can use OTYPE_VARCHAR2
for strings and OTYPE_NUMBER for numbers.
The OParameter class supports the following methods:
Construction and destruction:
OParameter
~OParameter
operator=
Attributes: