Example: Accessing Collection Elements

The following example illustrates how to access collection elements. Before running the sample code, make sure that you have the necessary datatypes and tables in the database. See Schema Description used in examples of OraCollection.

OraDynaset Example

Dim OraSession as OraSession

Dim OraDatabase as OraDatabase

Dim OraDynaset as OraDynaset

Dim EnameList as OraCollection

'create the OraSession Object.

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

'create the OraDatabase Object by opening a connection to Oracle.

Set OraDatabase = OraSession.OpenDatabase("ExampleDb",

"scott/tiger", 0&)

'create a dynaset object from department

set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)

'retrieve a Enames column from Department.

'Here Value property of OraField object returns EnameList OraCollection

set EnameList = OraDynaset.Fields("Enames").Value

'access the first element of EnameList

msgbox EnameList(1)

'move to next to row

OraDynaset.MoveNext

'access all the elements of EnameList for the second row

For index = 1 To EnameList.Size

msgbox EnameList(index)

Next Index

OraParameter Example

Dim OraSession as OraSession

Dim OraDatabase as OraDatabase

Dim EnameList as OraCollection

'Create the OraSession Object.

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

'Create the OraDatabase Object by opening a connection to Oracle.

Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

'create an OraParameter object representing EnameList collection bind Variable

OraDatabase.Parameters.Add "ENAMES", Null, ORAPARM_OUTPUT, ORATYPE_VARRAY,"ENAMELIST"

'execute the sql statement which selects ENAMES VARRAY from the department table

OraDatabase.ExecuteSQL ("BEGIN select enames into :ENAMES from department where dept_id = 10; END;")

'get the EnameList collection from OraParameter

set EnameList = OraDatabase.Parameters("ENAMES").Value

'access all the elements of EnameList

For index = 1 To EnameList.Size

msgbox EnameList(index)

Next Index