The OraCollection interface represents Oracle collection types, such as variable-length arrays (VARRAYs) and nested tables.
A collection is an ordered group of elements, all of the same type. For example, the students in a class or the grades for each student in a class. Each element has a unique subscript, called an index, that determines its position in the collection.
The collection type nested table is viewed as a table stored in the column of database tables. When retrieved, rows of a nested table are given consecutive subscripts that start at 1. Individual rows are accessed using an array-like access.
The collection type VARRAY is viewed as an array stored in the column of database tables. To reference an element in a VARRAY data type, standard subscripting syntax can be used. For example, Grade(3) references the third element in the VARRAY data type named Grades.
The OraCollection provides methods for accessing and manipulating an Oracle collection. Implicitly an OraCollection object contains an OLE Automation collection interface for accessing and manipulating (updating and inserting) individual elements of an Oracle collection. Individual elements can be accessed by using a subscript. An OraCollection element index starts at 1.
Element values are retrieved as Variant types. The Variant type of the element depends on the element type of the collection. Element values can be Null and can be set to Null. For elements of type objects and REFs, element values are returned as corresponding OO4O objects for that type. VARRAYs and nested tables do not support the elements of LOBs, VARRAYs, and Nested tables.
Table 9-1 lists the element type and return value of the elements.
Table 9-1 Element Type and Return Value of Elements
| Element Type | Element Value |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Element values are converted into a Variant SAFEARRAY format using the SafeArray property. Only elements of primitive types are supported. A Variant SAFEARRAY index starts at 0.
The CreateOraObject method on the OraDatabase object returns the OraCollection object. The Oracle collection associated with this OraCollection object is created in the client-side object cache.
For information about creating a dynaset from a collection, see to "Creating a Dynaset from an OraCollection Object".
Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in OraCollection Examples" for schema objects that are used in the OraCollection examples.
Example: Accessing Collection Elements
The following example illustrates how to access collection elements.
OraDynaset Example
Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim EnameList as OraCollection
'create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb","scott/tiger", 0&)
'create a dynaset object from department
set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)
'retrieve a Enames column from Department.
'Here Value property of OraField object returns EnameList OraCollection
set EnameList = OraDynaset.Fields("Enames").Value
'access the first element of EnameList
msgbox EnameList(1)
'move to next to row
OraDynaset.MoveNext
'access all the elements of EnameList for the second row
For index = 1 To EnameList.Size
msgbox EnameList(index)
Next Index
OraParameter Example
Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim EnameList as OraCollection
'Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
'create an OraParameter object representing EnameList collection bind Variable
OraDatabase.Parameters.Add "ENAMES", Null, ORAPARM_OUTPUT, _
ORATYPE_VARRAY,"ENAMELIST"
'execute the sql statement which selects ENAMES VARRAY from the department table
OraDatabase.ExecuteSQL ("BEGIN select enames into :ENAMES from department " & _
"where dept_id = 10; END;")
'get the EnameList collection from OraParameter
set EnameList = OraDatabase.Parameters("ENAMES").Value
'access all the elements of EnameList
For index = 1 To EnameList.Size
msgbox EnameList(index)
Next Index
Example: Modifying Collection Elements
The following example illustrates how to modify collection elements.
Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim EnameList as OraCollection
'create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
'create a dynaset object from department
set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)
'retrieve a Enames column from Department. Here Value property of OraField object
'returns EnameList OraCollection
set EnameList = OraDynaset.Fields("Enames").Value
'lock the row for editing and set the 2nd element of the EnameList to new value
OraDynaset.Edit
EnameList(2) = "Eric"
OraDynaset.Update
Example: Inserting in a Collection
The following example illustrates how to insert elements into an Oracle collection.
OraDynaset Example
Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim EnameListNew as OraCollection
'create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
'create a new OraCollection object from the database
set EnameListNew = OraDatabase.CreateOraObject("ENAMELIST")
'set EnameListNew's element values
EnameListNew(1) = "Nasser"
EnameListNew(2) = "Chris"
EnameListNew(3) = "Gopal"
'create a dynaset object from department
set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)
'start the AddNew operation and insert the EnameListNew collection
OraDynaset.AddNew
OraDynaset.Fields("dept_id") = 40
OraDynaset.Fields("name") = "DEVELOPMENT"
'set the EnameListNew to enames column
OraDynaset.Fields("enames") = EnameListNew
OraDynaset.Update
OraParameter Example
Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim EnameListNew as OraCollection
'Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
'create a new OraCollection object from the database
set EnameListNew = OraDatabase.CreateOraObject("ENAMELIST")
'set EnameListNew's element values
EnameListNew(1) = "Nasser"
EnameListNew(2) = "Chris"
EnameListNew(3) = "Gopal"
'create an input OraParameter object representing EnameList collection bind 'Variable
OraDatabase.Parameters.Add "ENAMES", Null, ORAPARM_INPUT, ORATYPE_VARRAY, _
"ENAMELIST"
'set the ENAMES parameter value to EnameListNew
OraDatabase.Parameters("ENAMES").Value = EnameListNew
'execute the insert sql statement
OraDatabase.ExecuteSQL ("insert into department values (40,'DEVELOPMENT', " & _
":ENAMES)")
Example: Collection with Object Type Elements
The following example illustrates the use of an Oracle collection having elements of object type.
Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim CourseList as OraCollection
Dim Course as OraObject
'create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
'create a dynaset object from division
set OraDynaset = OraDatabase.CreateDynaset("select * from division", 0&)
'retrieve a Courses column from Division.
'Here Value property of OraField object returns CourseList OraCollection
set CourseList = OraDynaset.Fields("Courses").Value
'retrieve the element value of the CourseList at index 1.
'Here element value is returned as Course OraObject
set Course = CourseList(1)
'retrieve course_no and title attribute of the Course
msgbox Course.course_no
msgbox Course.title
'move to next row
OraDynaset.MoveNext
'now CourseList object represents collection value for the second row
'and course OraObject 'represents the element value at index 1.
'retrieve course_no and title attribute of the Course.
msgbox Course.course_no
msgbox Course.title
Example: Creating a SAFEARRAY Variant from a Collection
The following example illustrates how to get and set a SAFEARRAY Variant with an Oracle collection.
Creating SAFEARRAY Variant from a Collection
Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim EnameList as OraCollection
Dim EnameArray as Variant
'create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
'create a dynaset object from department
set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)
'retrieve a Enames column from Department.
'Here Value property of OraField objectreturns EnameList OraCollection
set EnameList = OraDynaset.Fields("Enames").Value
'get the Variant SAFEARRAY from the collection.
EnameArray = EnameList.SafeArray
'display the individual elements of EnameArray
msgbox EnameArray(0)
msgbox EnameArray(1)
msgbox EnameArray(2)
Setting SAFEARRAY Variant to the Collection
Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim EnameList as OraCollection
Dim EnameArray() As String
ReDim EnameArray(3)
'Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
'create an Empty uninitialized input OraParameter object
'represent EnameList collection bind Variable
OraDatabase.Parameters.Add "ENAMES", Empty, ORAPARM_INPUT, _
ORATYPE_VARRAY,"ENAMELIST"
'get the Empty uninitialized ENAMES parameter value
set EnameList = OraDatabase.Parameters("ENAMES").Value
'initialize the EnameArray
EnameArray(0) = "Nasser"
EnameArray(1) = "Chris"
EnameArray(2) = "Gopal"
'set the EnameArray to EnameList's SafeArray
EnameList.SafeArray = EnameArray
'execute the insert sql statement
OraDatabase.ExecuteSQL ("insert into department " & _
"values (40,'DEVELOPMENT', :ENAMES)")
Example: Creating a Dynaset from a Collection
The following example illustrates how to create a dynaset from an Oracle collection.
Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim CourseList as OraCollection
Dim Course as OraObject
Dim CourseListDyn as OraDynaset
'create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
'create a dynaset object from division
set OraDynaset = OraDatabase.CreateDynaset("select * from division", 0&)
'retrieve a Courses column from Division. Here Value
'property of OraField object returns CourseList OraCollection
set CourseList = OraDynaset.Fields("Courses").Value
'create a input parameter for CourseList for nested table dynaset
OraDatabase.Parameters.Add "COURSELIST", CourseList, ORAPARM_INPUT, _
ORATYPE_TABLE, "COURSELIST"
'create a read only dynaset based on the CourseList.
Set CourseListDyn = OraDatabase.CreateDynaset("select * from THE" & _
"(select CAST(:COURSELIST AS COURSELIST) from dual)", ORADYN_READONLY)
'dynaset can also be created from Oracle8 collection
'using the following statement, which requires OO4O v8.1.x later
Set CourseListDyn = OraDatabase.CreateDynaset("select * from " & _
"TABLE(CAST(:COURSELIST AS COURSELIST))", ORADYN_READONLY)
'get the field values of the collection dynaset
msgbox CourseListDyn.Fields("title").Value
msgbox CourseListDyn.Fields("course_no").Value
'move the original dynaset to second row
Oradynaset.MoveNext
'set the new value of CourseList collection from the second row of main dynaset
'to the "COURSELIST" parameter
OraDatabase.Parameters("COURSELIST").Value = CourseList
'refresh the collection dynaset. Now the collection dynaset values are refreshed
' with new collection value.
CourseListDyn.Refresh
'get the field values of the collection dynaset
msgbox CourseListDyn.Fields("title").Value
msgbox CourseListDyn.Fields("course_no").Value