Example: Collection with Object Type Elements

The following example illustrates the use of an Oracle collection having elements of object type. 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.

Dim OraSession as OraSession

Dim OraDatabase as OraDatabase

Dim OraDynaset as OraDynaset

Dim CourseList as OraCollection

Dim Course as OraObject

'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 division

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

'retrieve a Courses column from Division.

'Here Value property of OraField object returns CourseList OraCollection

set CourseList = OraDynaset.Fields("Courses").Value

'retrieve the element value of the CourseList at index 1.

'Here element value is returned as Course OraObject

set Course = CourseList(1)

'retrieve course_no and title attribute of the Course

msgbox Course.course_no

msgbox Course.title

'move to next row

OraDynaset.MoveNext

'now CourseList object represents collection value for the second row

'and course OraObject 'represents the element value at index 1.

'retrieve course_no and title attribute of the Course.

msgbox Course.course_no

msgbox Course.title