AddTable Method

Applies To

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.

Examples

See "Example: Using OraParamArrays with PL/SQL".