Add Method

Applies To

Description

Adds a parameter to the OraParameters collection.

Usage

oraparameters.Add Name, Value, IOType, ServerType, ObjectName

Arguments	Description
Name	The name of the parameter to be added to the parameters collection. This name issued both for parameter identification and as the placeholder in associated SQL and PL/SQL statements.
Value	A variant specifying the initial value of the parameter. The initial value of the parameter is significant, as it defines the data type of the parameter.
IOType	An integer code specifying how the parameter is to be used in SQL statements and PL/SQL blocks.
ServerType	Specifies the Oracle Database type that this parameter will be bound to. This is required when binding to BLOB, CLOB, BFILE, OBJECT, REF, NESTED TABLE, or VARRAY. For a list of possible values, see the OraParameter ServerType property.
ObjectName	A case-sensitive string containing the name of the Object. This is only required if ServerType is ORATYPE_OBJECT, ORATYPE_VARRAY, and ORATYPE_TABLE. It is required for ORATYPE_REF when the REF will be used in PL/SQL.

The IOType settings are:

Settings	Values	Description
ORAPARM_INPUT	1	This should be used for input variables only.
ORAPARM_OUTPUT	2	This should be used for output variables only.
ORAPARM_BOTH	3	This should be used for variables that are both input and output.

These values can be found in the file oraconst.txt.

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

You should be careful to make sure that this value is correct. If you set an incorrect option, such as ORAPARAM_BOTH for a stored procedure parameter type IN, this can result in errors. In other words ORAPARAM_BOTH means "for IN OUT parameters only". It does not mean that you should use the parameter against one stored procedure that has an IN parameter and then use it in another that has an OUT parameter. In such a case you should use two parameters. Errors caused in this way are rare, but in the case of parameter-related errors, you should verify that the IOTYPE is correct.

The Value argument can be an Oracle9i object, such as an OraBLOB. Note that a copy of the object is made at that point in time and the Value property must be accessed to obtain a new object that refers to the Parameter's value. For example, if iotype is ORATYPE_BOTH and an OraBlob obtained from a dynaset is passed in as the input value, the Parameter Value property will need to be accessed one time after the SQL has been executed so that the newly updated output value of the parameter can be obtained. Similar to a dynaset, the object obtained from parameter Value property always refers to the latest value of the Parameter. The Visual Basic value NULL can also be passed as a value. The Visual Basic value EMPTY can be used for BLOB and CLOB to mean an empty LOB, and for OBJECT, VARRAY and NESTED TABLE to mean an object whose attributes are all NULL.

Remarks

Use parameters to represent SQL bind variables (as opposed to rebuilding the SQL statement). SQL bind variables are especially useful because you can change a parameter value without having to reparse the query. Use SQL bind variables only as input variables.

You can also use parameters to represent PL/SQL bind (in/out) variables. You can use PL/SQL bind variables as both input and output variables.

ORATYPE_RAW_BIN ServerType 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, 32k bytes when bound to a stored procedure. For example code, see the samples in the \oracle_home\OO4O\VB\Raw directory.