An OraParamArray object represents an array type bind variable in a SQL statement or PL/SQL block, as opposed to a scalar type bind variable represented by the OraParameter object.
OraParamArray objects are created, accessed, and removed indirectly through the OraParameters collection of an OraDatabase object. Each parameter has an identifying name and an associated value.
Implicitly an OraParamArray object contains an OLE automation collection interface for accessing and manipulating individual elements of an array. Individual elements can be accessed using a subscript or the Get_Value method. Individual elements can be modified by using a subscript or the Put_Value method.
Element values are retrieved as Variant types. The Variant type of the element depends on the ServerType of the OraParamArray object. Element values can be null and can be set to Null. For elements of type objects and REFs, element values are returned as corresponding OO4O objects for that type.
You can automatically bind a parameter to SQL and PL/SQL statements of other objects (as noted in the objects descriptions) by using the name of the parameter as a placeholder in the SQL or PL/SQL statement. Using parameters can simplify dynamic queries and increase program performance. Parameters are bound to SQL statements and PL/SQL blocks before execution.
The OraParameters collection is part of the OraDatabase object so that all parameters are available to any SQL statement or PL/SQL block executed within the database (through CreateDynaset, ExecuteSQL, or CreateSQL methods). Before a SQL statement or PL/SQL block is executed, an attempt is made to bind all parameters of the associated OraDatabase object. The bindings that fail (because the parameter does not apply to that particular SQL statement or PL/SQL block) are noted and no attempt is made to bind them again if the SQL statement or PL/SQL block is reexecuted but does not change.
Because neither SQL statements nor PL/SQL blocks are parsed locally (all parsing is done by Oracle Database), any unnecessary binding results in performance degradation. To prevent unnecessary parameter binding, use the AutoBindDisable and AutoBindEnable methods.
Example: Using OraParamArrays with SQL Statements
The following example shows how to use the OraParamArray object with SQL statements:
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraSqlStmt As OraSQLStmt
Dim PartNoArray As OraParamArray
Dim DescArray As OraParamArray
Dim I As Integer
'Test case for inserting/updating/deleting multiple rows using parameter
' arrays with SQL statements
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("exampledb", "scott/tiger", 0&)
'Create table
OraDatabase.ExecuteSQL ("create table part_nos(partno number," & _
"description char(50), primary key(partno))")
OraDatabase.Parameters.AddTable "PARTNO", ORAPARM_INPUT, ORATYPE_NUMBER, 10, 22
OraDatabase.Parameters.AddTable "DESCRIPTION", ORAPARM_INPUT, _
ORATYPE_CHAR, 10, 50
Set PartNoArray = OraDatabase.Parameters("PARTNO")
Set DescArray = OraDatabase.Parameters("DESCRIPTION")
'Initialize arrays
For I = 0 To 9
achar = "Description" + Str(I)
PartNoArray(I) = 1000 + I
DescArray(I) = achar
Next I
Set OraSqlStmt = OraDatabase.CreateSql("insert into
part_nos(partno, description) values(:PARTNO,:DESCRIPTION)", 0&)
'Update the newly created part_nos table
For I = 0 To 9
achar = "Description" + Str(1000 + I)
DescArray(I) = achar
Next I
'Update table
Set OraSqlStmt = OraDatabase.CreateSql("update part_nos set DESCRIPTION" & _
"=:DESCRIPTION where PARTNO = :PARTNO", 0&)
'Deleting rows
Set OraSqlStmt = OraDatabase.CreateSql("delete from part_nos where" & _
"DESCRIPTION=: Description ", 0&)
'Drop the table
OraDatabase.ExecuteSQL ("drop table part_nos")
Example: Using OraParamArrays with PL/SQL
The following is an example using OraParamArray objects with PL/SQL. The Employee PL/SQL package can be set up with the ORAEXAMP.SQL script. See "Demonstration Schema and Code Examples".
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim EmpnoArray As OraParamArray
Dim EnameArray As OraParamArray
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("exampledb", "scott/tiger", 0&)
OraDatabase.Parameters.Add "ArraySize", 3, ORAPARM_INPUT
OraDatabase.Parameters.AddTable "EMPNOS", ORAPARM_INPUT, ORATYPE_NUMBER,3, 22
OraDatabase.Parameters.AddTable "ENAMES", ORAPARM_OUTPUT, _
ORATYPE_VARCHAR2, 3, 10
Set EmpnoArray = OraDatabase.Parameters("EMPNOS")
Set EnameArray = OraDatabase.Parameters("ENAMES")
'Initialize the newly created input parameter table EMPNOS
EmpnoArray(0) = 7698
EmpnoArray(1) = 7782
EmpnoArray(2) = 7654
'Execute the PLSQL package
OraDatabase.ExecuteSQL ("Begin Employee.GetEmpNamesInArray(:ArraySize," & _
":EMPNOS, :ENAMES); End;")
'Print out Enames
MsgBox EnameArray(0)
MsgBox EnameArray(1)
MsgBox EnameArray(2)
See Also: