Retrieving a Collection Type Instance from the Database
Using a Dynaset Object
If a table contains an collection type column and a dynaset's query selects
against that column, then the Value property of OraField object returns an OraCollection.
The following example selects the ENAMES column from the department table and
an 'EnameList' object is retrieved from the OraField object:
set OO4OSession = CreateObject("OracleInProcServer.XOraSession")
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0)
Set Dept = hrDb.CreateDynaset("select * from department", 0&)
Set EnameList = Dept.Fields("ENAMES").Value
Using a Parameter object
If a SQL statement or PL/SQL block has a bind variable of collection type,
then you create a OraParameter object using the OraParameters Add method. The
Value property of the OraParameter object for that bind variable returns an
OraCollection.
The following code uses a collection datatype as a bind variable in a PLSQL
anonymous block. This block selects an collection type from the database.
set OO4OSession = CreateObject("OracleInProcServer.XOraSession")
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0)
hrDb.Parameters.Add "ENAMES", Null, ORAPARM_OUTPUT, ORATYPE_VARRAY,
"ENAMELIST"
hrDb.ExecuteSQL ("BEGIN select enames into :ENAMES from department
where dept_id = 10; END;")
set EnameList = hrDb.Parameters("ENAMES").Value