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 8i 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
