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