Using OraParamArrays With PL/SQL

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)