AddTable Method

Applies To

Description

Adds an array parameter to the OraParameters collection.

Usage

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

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

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 the Oracle Database type that this array parameter will be bound to. 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	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.
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:

IO Type Settings

Constant	Value	Description
ORAPARM_INPUT		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.

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.

Warning:

· ORAPARM_INPUT goes with PLSQL Stored Procedure Type INPUT.

· ORAPARM_OUTPUT goes with PLSQL Stored Procedure Type OUTPUT.

· ORAPARM_BOTH goes with PLSQL Stored Procedure Type IN/OUT.

· Deviation from this, such as using ORAPARAM_BOTH with Type INPUT can cause errors.

These values (the constants defined as 1, 2, and 3) can be found in the file oraconst.txt.

Server Type

See ServerType property for valid types.

Notes:

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

· The maximum positive number is 0.99999999999999999999 E + 38 (decimal followed by 20 nines)

· The minimum positive number is 0.1 E -38

· The minimum negative number is - 0.99999999999999999999 E + 38 (decimal followed by 20 nines)

· The maximum negative number is 0.1 E -38

ElementSize (optional)

Valid for character, string type, and raw table (array) parameters. The valid size for ElementSize depends on the VarType. These ranges are listed below.

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

Remarks

Use parameters to represent SQL bind variables for array insert/update/delete (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. No element (see ElementSize argument) can b greater than 4000 bytes when binding to stored procedures, 2000 bytes against columns of tables. For example code, see the samples in the \oracle_home\OO4O\VB\Raw directory.