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")
