The following is an example using OraParamArrays with PL/SQL. The Employee
PLSQL package can be set up with the ORAEXAMP.SQL script. See Demonstration Database 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)
