Add Method

Applies To

Description

Adds a parameter to the OraParameters collection.

Usage

oraparameters.Add Name, Value, IOType, ServerType, 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 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; 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 Oracle Database type to which this parameter is to be bound. 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`, or `ORATYPE_TABLE`. `ServerType` is required for `ORATYPE_REF` when the `REF` is used in PL/SQL.

IOType Settings

The IOType settings are:

Settings	Values	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

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

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.

Verify that this value is correct. If you set an incorrect option, such as ORAPARM_BOTH for the IN stored procedure parameter type, 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. For this case, use two parameters. Errors caused this way are rare, if there is a parameter-related error, verify that the IOType is correct.

The Value argument can be an Oracle Database 10g 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 value of the parameter. 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 needs to be accessed one time after the SQL has been executed to obtain the newly updated output value of the parameter. The object is obtained from the parameter in the same manner as from a dynaset.

The 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 EMPTY value can be used for BLOB and CLOB data types to mean an empty LOB, and the EMPTY value can be used for OBJECT, VARRAY, and NESTED TABLE data types 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 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 variables. You can use PL/SQL bind variables as both input and output variables.

The ORATYPE_RAW_BIN ServerType value is used when binding to Oracle Raw columns. A byte array is used to Put or Get values. The maximum allowable size of an ORATYPE_RAW_BIN bind buffers is 2000 bytes when bound to a column of a table and 32 KB when bound to a stored procedure. For example code, see the samples in the ORACLE_BASE\\ORACLE_HOME\OO4O\VB\Raw directory.

Examples

This example demonstrates using the Add and Remove parameter methods, the ServerType parameter property, and the ExecuteSQL database method to call a stored procedure and function (located in ORAEXAMP.SQL). Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load () 
 
'Declare variables 
Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
 
'Create the OraSession Object. 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
'Create the OraDatabase Object. 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 
 
'Add EMPNO as an Input/Output parameter and set its initial value. 
OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT 
OraDatabase.Parameters("EMPNO").ServerType = ORATYPE_NUMBER 
 
'Add ENAME as an Output parameter and set its initial value. 
OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT 
OraDatabase.Parameters("ENAME").ServerType = ORATYPE_VARCHAR2 
 
'Add SAL as an Output parameter and set its initial value. 
OraDatabase.Parameters.Add "SAL", 0, ORAPARM_OUTPUT 
OraDatabase.Parameters("SAL").ServerType = ORATYPE_NUMBER 
 
'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME. 
' This Stored Procedure can be found in the file ORAEXAMP.SQL. 
OraDatabase.ExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;")
'Display the employee number and name. 
 
'Execute the Stored Function Employee.GetSal to retrieve SAL. 
' This Stored Function can be found in the file ORAEXAMP.SQL. 
OraDatabase.ExecuteSQL ("declare SAL number(7,2); Begin" & _
           ":SAL:=Employee.GetEmpSal (:EMPNO); end;") 
 
'Display the employee name, number and salary. 
MsgBox "Employee " & OraDatabase.Parameters("ENAME").value & ", #" & _
          OraDatabase.Parameters("EMPNO").value & ",Salary=" & _
          OraDatabase.Parameters("SAL").value 
 
'Remove the Parameters. 
OraDatabase.Parameters.Remove "EMPNO" 
OraDatabase.Parameters.Remove "ENAME" 
OraDatabase.Parameters.Remove "SAL" 
 
End Sub