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