AddTable Method

Description

Adds an array parameter to the OraParameters collection.

Usage

oraparamarray.AddTable  Name, IOType, ServerType, ArraySize , ElementSize, ObjectName

Arguments

The arguments for the method are:

Arguments Description
Name The name of the parameter to be added to the parameters collection. This name is used both for parameter identification and as the placeholder in associated SQL and PL/SQL statements.
IOType An integer code specifying how the parameter is to be used in SQL statements and PL/SQL blocks.
ServerType Specifies Oracle Database type to which this array parameter is to be bound. For a list of possible values, see the OraParameter ServerType Property.
ArraySize Defines the number of elements in the parameter array. This parameter is used to calculate the maximum buffer length.
ElementSize [optional] Defines the size of the element. Valid for only character and string type table (array) parameters. The valid size for ElementSize depends on the VarType.

ElementSize is optional in all cases except when bound to char and string types.

ObjectName A case-sensitive string containing the name of the Object. This is only required if ServerType is ORATYPE_OBJECT, ORATYPE_VARRAY, or ORATYPE_TABLE. It is required for ORATYPE_REF when the REF is used in PL/SQL.

IO Type Settings

The IOType settings are:

Constant Value Description
ORAPARM_INPUT 1 Used for input variables only.
ORAPARM_OUTPUT 2 Used for output variables only.
ORAPARM_BOTH 3 Used for variables that are both input and output.

Verify that this value is correct. If you set an incorrect option, such as ORAPARM_BOTH for the stored procedure parameter type IN, this can result in errors. ORAPARM_BOTH is for IN and OUT parameters only. It is not used against one stored procedure that has an IN parameter and another that has an OUT parameter. In this case, use two parameters. Errors caused in this way are rare, but if there are parameter-related errors, verify that the IOType is correct.

Server Type

See ServerType Property for valid types and note the following:

Note:

  • External data type ORATYPE_NUMBER allows decimal precision of 1 to 38.

  • The maximum positive number is 0.99999999999999999999 E + 38.

  • The minimum positive number is 0.1 E-38.

  • The minimum negative number is -0.99999999999999999999 E + 38.

  • The maximum negative number is 0.1 E -38.

ElementSize (Optional)

Valid for character, string, and raw types. The valid size for ElementSize depends on the VarType. This represents the length of each individual string or raw array element. These ranges are listed.

VarType Size
ORATYPE_VARCHAR2 Valid range from 1 to 1999
ORATYPE_VARCHAR Valid range from 1 to 1999
ORATYPE_STRING Valid range from 1 to 1999
ORATYPE_CHAR Valid range from 1 to 255
ORATYPE_CHARZ Valid range from 1 to 255
ORATYPE_RAW_BIN Valid range from 1 to 4000 (see remarks)

Remarks

Use parameters to represent SQL bind variables for array insert, update, and delete operations, rather than rebuilding the SQL statement. SQL bind variables are useful because you can change a parameter value without having to parse the query again. 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.

The ServerType value 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: the maximum allowable size is 32 KB when bound to a stored procedure. No element (see ElementSize argument) can be greater than 4000 bytes when binding to stored procedures, 2000 bytes against columns of tables. For example code, see the samples in the ORACLE_BASE\\ORACLE_HOME\OO4O\VB\Raw directory.