OraParamArray Object

Description

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.

Remarks

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.

Properties

Methods

Example

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)