Oracle® Objects for OLE Developer's Guide 10g Release 1 (10.1) Part Number B10118-01 |
|
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