Example: Creating a Dynaset from a Collection

The following example illustrates how to create a dynaset from an Oracle collection. 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

Dim CourseListDyn as OraDynaset

'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

'create a input parameter for CourseList for nested table dynaset

OraDatabase.Parameters.Add "COURSELIST", CourseList, ORAPARM_INPUT,

ORATYPE_TABLE, "COURSELIST"

'create a read only dynaset based on the CourseList.

Set CourseListDyn = OraDatabase.CreateDynaset("select *

from THE(select CAST(:COURSELIST AS COURSELIST) from dual)",

ORADYN_READONLY)

'dynaset can also be created from Oracle8 collection

'using the following statement, which requires OO4O v8.1.x later

Set CourseListDyn = OraDatabase.CreateDynaset("select * from

TABLE(CAST(:COURSELIST AS COURSELIST))", ORADYN_READONLY)

'get the field values of the collection dynaset

msgbox CourseListDyn.Fields("title").Value

msgbox CourseListDyn.Fields("course_no").Value

'move the original dynaset to second row

Oradynaset.MoveNext

'set the new value of CourseList collection from the second row of main dynaset to the "COURSELIST" parameter

OraDatabase.Parameters("COURSELIST").Value = CourseList

'refresh the collection dynaset. Now the collection dynaset values are refreshed with new collection value.

CourseListDyn.Refresh

'get the field values of the collection dynaset

msgbox CourseListDyn.Fields("title").Value

msgbox CourseListDyn.Fields("course_no").Value