The following example shows the number of records inserted into the database
after using an insert statement with OraSQLStmt.
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraDynaset As OraDynaset
Dim OraSqlStmt As OraSQLStmt
Dim OraPArray1 As OraParamArray
Dim OraPArray2 As OraParamArray
Dim I As Integer
On Error GoTo ERR_array_sql
'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
If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then
MsgBox "Error"
End If
Set OraPArray1 = OraDatabase.Parameters("PARTNO")
Set OraPArray2 = OraDatabase.Parameters("DESCRIPTION")
'Initialize arrays
For I = 0 To 9
achar = "Description" + Str(I)
OraPArray1.put_Value 1000 + I, I
OraPArray2.put_Value achar, I
Next I
Set OraSqlStmt = OraDatabase.CreateSql("insert into
part_nos(partno, description) values(:PARTNO,:DESCRIPTION)", 0&)
If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then
MsgBox "Error"
End If
MsgBox "# of records inserted : " & OraSqlStmt.RecordCount
Exit Sub
ERR_array_sql:
MsgBox Err.Description
