Creating a Dynaset from an OraCollection

Example

A SELECT query can be issued against instances of VARRAY and Nested table collection types using SQL THE or TABLE operator and individual elements can be accessed as rows. If those collection types are having object type as its element type, individual attributes of the object type represents fields of a row. If an object type X having attributes of a, b, and c, and the element type of the collection is object type X then the SELECT query on this collection returns with a, b, and c fields.

In OO4O, read-only dynaset objects can be created from select queries on the collection. Individual elements are accessed using row navigation. If the collection type having object type as its element type, then attributes of that object type (element) are accessed using OraField object.

If you have a Course object type and CourseList nested table collection type having Course as its element type as described:

CREATE TYPE Course AS OBJECT (

course_no NUMBER(4),

title VARCHAR2(35),

credits NUMBER(1)

);

CREATE TYPE CourseList AS TABLE OF Course;

In OO4O, CourseList OraCollection represents an instance of CourseList collection type.

Dim CourseList as OraCollection

assume that you have valid CourseList collection instance

set CourseList = ......

SQL THE or TABLE operator needs collection type as bind variable. So create a OraParameter object for CourseList OraCollection

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

create a read only dynaset based on the CourseList using SQL THE operator

Set CourseListDyn = OraDatabase.CreateDynaset("select * from THE(select CAST(:COURSELIST AS COURSELIST) from dual)", ORADYN_READONLY)

or create a read only dynaset based on the CourseList using SQL TABLE operator, which is available only in OO4O with release 9i libraries

Set CourseListDyn = OraDatabase.CreateDynaset("select * from TABLE(CAST(:COURSELIST AS COURSELIST))", ORADYN_READONLY)

'display the course_no field

msgbox CourseListDyn.Fields("course_no").Value

'display the title field

msgbox CourseListDyn.Fields("title").Value

'move to next row

OraDynaset.MoveNext