************************************************************* This article is being delivered in Draft form and may contain
errors. Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
*************************************************************
PURPOSE
-------
The purpose of this bulletin is to describe support for multilevel collections
in Oracle9i and to provide examples of these new features. A secondary purpose
is to provide a short survey and examples of how this new functionality can be
very roughly approximated in versions prior to Oracle9i.
SCOPE & APPLICATION
-------------------
This document is intended to provide an introduction to multilevel collections
in SQL and PL/SQL. It is not necessarily comprehensive in its treatment though
it strives to be as complete as possible. In particular, no attention is given
to accessing multilevel collections via programmatic interfaces other than the
PL/SQL interface. This bulletin addresses issues that apply to all collections
(e.g. generic syntax not unique to a multilevel collection) only as background
and where it contributes to a better understanding of multilevel collections.
Finally, this document does not address Oracle9i collection enhancements which
are independent of whether the collections are nested (e.g. substitutability,
also known as heterogeneous collections).
Interested readers should consult the official Oracle9i documentation for any
additional information on this and related topics. See the References section
below for a list of relevant documentation.
SUPPORT FOR MULTILEVEL COLLECTIONS IN ORACLE9i
----------------------------------------------
Introduction
------------
In both Oracle8 and Oracle8i, it is possible to create collection types (nested
tables and VARRAYs) as full-fledged datatypes within the database. These types
can be used in many of the same contexts as built-in types; for example, they
can be used to declare columns in a table and PL/SQL variables can be declared
of a collection type. However, Oracle8 and Oracle8i supported only one level of
nesting for collections. For example, the following would not be permitted.
create or replace type TableType1 as table of number;
/
create or replace type TableType2 as table of TableType1;
/
The above example demonstrates an instance of direct nesting of collections, but
indirect nesting was also forbidden in Oracle8 and Oracle8i. For example, it was
not possible to create collections of objects having collection attributes.
create or replace type ObjWithCollection as object
(
keyval number,
atable TableType1
);
/
create or replace type TableType3 as table of ObjWithCollection;
/
The above example demonstrates indirect nesting of collections. This would also
fail in both Oracle8 and Oracle8i. A later section of this document will suggest
a workaround for this shortcoming and provide a short example of how this might
be implemented in pre-Oracle9i versions, but the workaround is far from ideal as
will become evident.
Therefore, Oracle9i was enhanced to provide direct support for both of the above
scenarios. Both SQL and PL/SQL were enhanced to support multilevel collections.
The remainder of this bulletin discusses those enhancements and provides a number
of examples that demonstrate the available functionality.
Why Multilevel Collections?
---------------------------
Although relational structures can be used to model virtually all requirements of
modern applications, there are many cases where the mapping from the application
model to a relational structure is not straightforward and can obscure the true
nature of the problem. This so-called 'impedence mismatch' or 'paradigm mismatch'
has implications on productivity since much of a developer's or DBA's time must
be spent mapping from one system to the other. Clearly, there are applications
that could benefit from the ability to directly model these 1:N relationships.
For example, an invoice may bill for multiple orders, each order may consist of
multiple items, and each orderable item could be composed of several components.
Multilevel collection semantics in particular have traditionally been modelled
using a series of parent and child tables linked via primary and foreign keys,
with the target object being constructed on-demand via a multitable join. With
multilevel collection support, storage of the nested table or VARRAY elements
happens transparently and access to elements occurs via implicit rather than
explicit joins. In many cases, this can simplify application development.
Creating Multilevel Collection Types
------------------------------------
The basic syntax for creating collection types (nested tables and VARRAYs) has
not changed; it remains essentially the same as in Oracle8 and Oracle8i.
However, the list of element types supported has been extended; in particular,
it is now possible to use other collection types or object types having one or
more collection attributes as the element type.
A number of examples that demonstrate some of the possible combinations follow.
Traditional Oracle8/Oracle8i Collections
----------------------------------------
create or replace type TableType as table of number;
/
create or replace type ArrayType as varray(5) of number;
/
Nested Table of Nested Table
----------------------------
create or replace type TableOfTableType as table of TableType;
/
Nested Table of VARRAY
----------------------
create or replace type TableOfArrayType as table of ArrayType;
/
VARRAY of Nested Table
----------------------
create or replace type ArrayOfTableType as varray(5) of TableType;
/
VARRAY of VARRAY
----------------
create or replace type ArrayOfArrayType as varray(5) of ArrayType;
/
This capability extends to an arbitrary number of levels of nesting with any
combination of nested tables and VARRAYs. For example, one might create yet
another nested table of the following form.
Nested Table of VARRAY of Nested Table
--------------------------------------
create or replace type TableOfArrayOfTableType as table of ArrayOfTableType;
/
It is also possible to create collections of objects containing one or more
collection attributes (which could also be multilevel collections).
For example,
create or replace type ObjectWithCollections as object
(
keyval number,
atable TableType,
anarray ArrayType,
multilevel TableOfArrayType
);
/
create or replace type TableOfObjects as table of ObjectWithCollections;
/
create or replace type ArrayOfObjects as varray(5) of ObjectWithCollections;
/
Creating Tables with Multilevel Collection Type Columns
-------------------------------------------------------
If multilevel collections are to be supported as full-fledged types, then
some mechanism must be provided to store them in the database. Furthermore,
manipulation of the collection should assume value semantics; that is, the
collection is treated as a single entity. Oracle9i fully supports storage
of multilevel collections in the database and manipulation via traditional
SQL mechanisms. This section will discuss the syntax used to create tables
containing multilevel collection columns. We also briefly discuss storage
models for nested tables and VARRAYs in this section.
First, consider the basic syntax used to create a table with a non-nested
collection type; we then expand this to include multilevel collections. A
collection type can be used to declare a column in a relational table and
can appear as an attribute in an object type which appears in a column in
a relational table or in an object table.
For example, consider the following scenarios involving a nested table.
Relational Table with Nested Table Column
-----------------------------------------
create table DBTableWithNestedTable
(
keyval number,
atable TableType
)
nested table atable store as DBTWNT_nt;
Object Table with Nested Table Attribute
----------------------------------------
create or replace type ObjectWithNestedTable as object
(
keyval number,
atable TableType
);
/
create table DBObjTabWithNestedTable of ObjectWithNestedTable
nested table atable store as DBOTWNT_nt;
The nested table storage clause determines the name of the table used to
store the elements of the nested table column or attribute. This storage
table is a normal database table except that it is implicitly associated
with the parent table via a system generated NESTED_TABLE_ID.
The nested storage table is implicitly created when the parent table is
created and is automatically dropped when the parent table is dropped.
The following example demonstrates the same basic concept using a VARRAY
rather than a nested table.
Relational Table with VARRAY Column
-----------------------------------
create table DBTableWithVarray
(
keyval number,
anarray ArrayType
)
varray anarray store as lob DBTWV_lob;
Object Table with VARRAY Attribute
----------------------------------
create or replace type ObjectWithVarray as object
(
keyval number,
anarray ArrayType
);
/
-- inline storage
create table DBObjTabWithVarray of ObjectWithVarray;
OR
-- LOB storage
create table DBObjTabWithVarray of ObjectWithVarray
varray anarray store as lob DBOTWV_lob;
If no LOB storage clause is specified, then the VARRAY is stored inline for
small values (<= 4000 bytes) and in a LOB for larger values. If a LOB storage
clause is specified, the VARRAY is always stored in a LOB, but it may be an
inline LOB unless 'storage in row' is disabled. Furthermore, a VARRAY stored
in a LOB will be stored in the same tablespace as the parent table unless a
different tablespace is specified via the LOB storage clause.
The syntax need only be modified slightly to support multilevel collections.
Since each element of a collection can also be another collection, storage
clauses (of the nested table or LOB variety) may also be nested.
Relational Table with Multilevel Collection Column
--------------------------------------------------
create table DBTableWithTableOfArray
(
keyval number,
multilevel TableOfArrayType
)
nested table multilevel store as DBTWTOA_nt
( varray column_value store as LOB DBTWOA_lob );
Note the nesting of the VARRAY storage clause for the nested VARRAY column.
Object Table with Multilevel Collection Attribute
-------------------------------------------------
create or replace type ObjectWithArrayOfTable as object
(
keyval number,
multilevel ArrayOfTableType
);
/
create table DBObjTabWithArrayOfTable of ObjectWithArrayOfTable
varray multilevel store as LOB DBOTWAOT_lob;
Note that in this latter case, no storage clause for the nested table was
required. The reason for this will become clearer when we discuss storage
models for nested tables and VARRAYs in the upcoming paragraphs.
Elements of nested tables that are either top-level or are also elements of
another nested table are stored out-of-line in a database table. Elements of
VARRAYs are always stored either inline or in a LOB, depending on the size of
the VARRAY and whether or not a LOB storage clause was specified when creating
the database table; this is true even for nested table elements of VARRAYs.
The below table provides an overview, for example, of how some of the types we
have created to this point would be stored in the database.
|----------------------------------------------------------------------------|
| Type | How is it stored? |
| | |
|------------------|---------------------------------------------------------|
| TableType | Stored in nested storage table. |
|------------------|---------------------------------------------------------|
| ArrayType | W/ LOB storage clause - Stored in inline LOB. |
| | wo/ LOB storage clause - Stored inline. |
| | |
| | *note: ArrayType can never be > 4000 bytes. |
| | |
| | *note: For arrays that can be > 4000 bytes, |
| | they would be stored in a LOB. |
|------------------|---------------------------------------------------------|
| TableOfTableType | Both table stored in nested storage tables. |
|------------------|---------------------------------------------------------|
| TableOfArrayType | Table stored in nested storage table. |
| | Array elements stored inline in the storage table. |
|------------------|---------------------------------------------------------|
| ArrayOfTableType | Array and all nested table elements in a single LOB. |
|------------------|---------------------------------------------------------|
| ArrayOfArrayType | w/ LOB storage clause - Stored in inline LOB. |
| | wo/ LOB storage clause - Stored inline. |
| | |
| | *note: ArrayOfArrayType can never be > 4000 bytes. |
| | |
| | *note: For arrays of arrays that can be > 4000 bytes, |
| | they would be stored in a LOB. |
|------------------|---------------------------------------------------------|
Obviously, there is the potential for any collection or nested collection to
become so large that it is impractical to return it to a client as a single
entity. For this reason, previous versions of Oracle supported the concept
of a locator; that is, rather than returning all of the elements of the
collection at once, a locator is returned and elements of the underlying
collection can be accessed iteratively via this locator.
By default, value semantics are used when returning collections. Therefore,
you must ask that a collection be returned by locator. There are two ways
to accomplish this; the RETURN AS LOCATOR clause to CREATE TABLE and the
/*+ NESTED_TABLE_GET_REFS +*/ hint. For example, one of the tables from
a previous section of this bulletin could have been created as follows.
Relational Table with Nested Table Column Returned as Locator
-------------------------------------------------------------
create table DBTableWithNestedTable
(
keyval number,
atable TableType
)
nested table atable store as DBTWNT_nt
return as locator;
Or the table can be created as normal and a hint can be provided when the
table is queried as follows.
Relational Table with Nested Table Column
-----------------------------------------
create table DBTableWithNestedTable
(
keyval number,
atable TableType
)
nested table atable store as DBTWNT_nt;
-- query the nested table as a locator
select /*+ NESTED_TABLE_GET_REFS +*/ atable
from DBTableWithNestedTable
where keyval = 1;
In Oracle9i, this concept has been extended to allow returning collections
at any level of nesting using a locator. For example,
Relational Table with Top-Level Collection Returned as Locator
--------------------------------------------------------------
create table DBTableWithTableOfTable
(
keyval number,
multilevel TableOfTableType
)
nested table multilevel store as DBTWTOT_nt
( nested table column_value store as DBTWTOT_nt2 )
return as locator;
In this case, only the top-level table is returned as locator, but it is
possible to return nested tables as locators from any or all levels. For
example, both levels could be returned as locator using the following.
Relational Table with Collections Returned as Locators
------------------------------------------------------
create table DBTableWithTableOfTable
(
keyval number,
multilevel TableOfTableType
)
nested table multilevel store as DBTWTOT_nt
( nested table column_value store as DBTWTOT_nt2
return as locator )
return as locator;
See the below section on PL/SQL for information about using these returned
locators to retrieve the underlying collection elements from the database.
NOTE: Returning locators for a VARRAY is not supported in the initial
release of Oracle9i, but may be added in a future release.
Finally, each storage table (for nested tables) or LOB (for VARRAYs) can be
assigned its own storage parameters when the table is created. This can be
useful for specifying that a table or LOB should be stored in a different
tablespace or modifying physical storage attributes for a table or LOB.
In addition, it is possible to add constraints to and/or create indexes on
the columns appearing in a nested storage table. Most storage parameters,
constraints, and indexes can also be modified, added, dropped, or rebuilt
via the appropriate ALTER command after the creation of the storage table,
subject to the same limitations that apply to other database tables.
Manipulation of Multilevel Collection Types in SQL
--------------------------------------------------
Multilevel collection columns (or attributes of object in an object table)
can be manipulated via SQL using extensions to traditional SQL syntax. The
following paragraphs will discuss the required syntax and provide a number
of examples based on the types and tables created in previous sections.
A default constructor is created for every object type and this constructor
can be used in SQL to create an object of the appropriate type. The default
constructor has the same name as the type and has input parameters of the
same number and type as the attributes of the object type. Constructors for
collection types also have the same name as the type, but accept a variable
argument list, one argument for each element of the collection. Furthermore,
for multilevel collections, each element can be another collection.
The first obvious scenario in which a constructor might be needed is during
the insertion of a row into a table (relational table with object column or
object table). For example,
Insert into Relational Table with Object Column
-----------------------------------------------
insert into DBTableWithTableOfArray
values ( 1, TableOfArrayType
(
ArrayType( 1, 2, 3 ),
ArrayType( 4, 5, 6 )
)
);
Insert into Object Table
------------------------
insert into DBObjTabWithArrayOfTable
values ( ObjectWithArrayOfTable
( 1, ArrayOfTableType
(
TableType( 1, 2 ),
TableType( 3, 4 ),
TableType( 5, 6 )
)
)
);
The above examples both insert a collection as an atomic entity into some
top-level database table. For nested table columns and attributes, it is
also possible to perform piecewise inserts into a nested collection.
NOTE: VARRAYS do not support piecewise operations. You must insert and/or
update VARRAYS as a single, atomic unit.
For example,
Insert into Nested Collection - Example 1
-----------------------------------------
insert into DBTableWithTableOfTable
values ( 1, TableOfTableType ( TableType ( 1, 2 ) ) );
insert into TABLE( select dbt.multilevel
from DBTableWithTableOfTable dbt
where dbt.keyval = 1 )
values ( TableType ( 3, 4 ) );
Note that the TABLE operator is used to convert the nested table returned
by the nested SQL statement into a form usable for inserts. This concept
can be extended to an arbitrary number of levels of nesting. For example,
Insert into Nested Collection - Example 2
-----------------------------------------
create or replace type KeyedObject as object
(
keyval number,
mycoll TableType
);
/
create or replace type TableOfKeyedObject as table of KeyedObject;
/
create or replace type KeyedObjectWithMulti as object
(
keyval number,
mycoll TableOfKeyedObject
);
/
create table DBTableWithTableOfKeyedObject of KeyedObjectWithMulti
nested table mycoll store as DBTWTOKO_nt (
nested table mycoll store as DBTWTOKO_nt2 );
insert into DBTableWithTableOfKeyedObject
values ( KeyedObjectWithMulti
( 1, TableOfKeyedObject
(
KeyedObject( 1, TableType( 1, 2 ) ),
KeyedObject( 2, TableType( 3, 4 ) )
)
)
);
insert into TABLE( select dbt2.mycoll
from TABLE( select dbt1.mycoll
from DBTableWithTableOfKeyedObject dbt1
where dbt1.keyval = 1 ) dbt2
where dbt2.keyval = 2 )
values ( 5 );
The above examples also illustrate how a select can be used to access a
nested table, but to further clarify, consider the following SQL which
selects the values from the nested table into which we inserted above.
Select from a Nested Collection
-------------------------------
select dbt3.column_value
from TABLE( select dbt2.mycoll
from TABLE( select dbt1.mycoll
from DBTableWithTableOfKeyedObject dbt1
where dbt1.keyval = 1 ) dbt2
where dbt2.keyval = 2 ) dbt3;
NOTE: The reserved keyword COLUMN_VALUE is used when accessing a table
of a scalar type since elements in this case do not have a name.
Although the above query permits one to view the contents of the nested
table, it might be nice to see how this data corresponds to data stored
in the parent table or collection.
In Oracle8i, collection unnesting (also known as flattened subquery) was
introduced to meet this need. In Oracle9i, this functionality has been
extended to support multilevel collections. For example,
Unnesting of Multilevel Collections
-----------------------------------
select dbt1.keyval, dbt2.keyval, dbt3.column_value
from DBTableWithTableOfKeyedObject dbt1,
TABLE(dbt1.mycoll) dbt2,
TABLE(dbt2.mycoll) dbt3;
NOTE: Although piecewise updates, deletes and inserts are only permitted
on nested tables, basic selects and collection unnesting are both
supported for VARRAYs as well.
Updates and deletes are performed in a manner analogous to the above, but
for completeness, several examples follow that demonstrate the syntax for
performing updates and deletes against nested table columns or attributes.
Updating a Multilevel Collection
--------------------------------
update TABLE ( select dbt2.mycoll
from TABLE( select dbt1.mycoll
from DBTableWithTableOfKeyedObject dbt1
where dbt1.keyval = 1 ) dbt2
where dbt2.keyval = 2 ) dbt3
set dbt3.column_value = 7
where dbt3.column_value = 3;
Deleting from a Multilevel Collection
-------------------------------------
delete from TABLE ( select dbt2.mycoll
from TABLE( select dbt1.mycoll
from DBTableWithTableOfKeyedObject dbt1
where dbt1.keyval = 1 ) dbt2
where dbt2.keyval = 2 ) dbt3
where dbt3.column_value = 4;
Manipulation of Multilevel Collection Types in PL/SQL
-----------------------------------------------------
Support for multilevel collections has also been added to Oracle9i PL/SQL.
Multilevel collections can be constructed and accessed in the same manner
as non-nested collections, with only a few minor extensions to the syntax.
Constructing a multilevel collection in PL/SQL is essentially the same as
in SQL. For example, the following code snippet declares and initializes
a multilevel collection of type TableOfArrayOfTableType.
declare
multi TableOfArrayOfTableType;
begin
multi := TableOfArrayOfTableType
(
ArrayOfTableType(
TableType( 1, 2 ),
TableType( 3, 4 )
),
ArrayOfTableType(
TableType( 5, 6 ),
TableType( 7, 8 ),
TableType( 9, 0 )
)
);
end;
/
The basic rules for assignment compatibility and comparison are unchanged
from Oracle8 and Oracle8i. In particular, direct omparison of collections
or objects with collection attributes is not supported and the assignment
of collections and collection elements requires only that both the source
and destination be of the same type. Implicit casting is not supported.
Elements of a multilevel collection are accessed using subscript notation
similar to that used for non-nested collections. The existing syntax has
been extended to allow for multiple subscripts and to support insertion
of attribute names when dealing with objects with collection attributes.
Accessing Elements of a Multilevel Collection - Scalar Tables Only
------------------------------------------------------------------
declare
mynum number;
mytab TableType;
multi TableOfTableType;
begin
multi := TableOfTableType (
TableType( 1, 2, 3),
TableType( 4, 5, 6),
TableType( 7, 8, 9)
);
mytab := multi(2); -- TableType( 4, 5, 6)
mynum := mytab(1); -- 4
mynum := multi(2, 1); -- 4
end;
/
Accessing Elements of a Multilevel Collection - With Object Tables
------------------------------------------------------------------
declare
mynum number;
myarr ArrayType;
mytab TableType;
multi TableOfObjects;
begin
multi := TableOfObjects
(
ObjectWithCollections
(
1,
TableType( 1, 2, 3),
ArrayType( 4, 5, 6),
TableOfArrayType
(
ArrayType( 7, 8, 9)
)
),
ObjectWithCollections
(
2,
TableType( 9, 8, 7),
ArrayType( 6, 5, 4),
TableOfArrayType
(
ArrayType( 3, 2, 1)
)
)
);
mytab := multi(1).atable; -- TableType( 1, 2, 3)
myarr := multi(2).anarray; -- ArrayType( 6, 5, 4)
mynum := mytab(3); -- 3
mynum := multi(1).atable(3); -- 3
mynum := myarr(1); -- 6
mynum := multi(2).anarray(1); -- 6
mynum := multi(1).multilevel(1)(2); -- 8
end;
/
The above access methods are intended for use with collection values, not
collection locators. A different method is required for accessing elements
of a collection returned as a locator.
First, it must be possible to determine whether a returned value is in fact
a locator or a value. This becomes necessary because the decision to return
a locator is made based on factors which may be outside the control of the
PL/SQL application (e.g. the table was created with RETURN AS LOCATOR).
As was the case for Oracle8 and Oracle8i, the UTL_COLL provided package is
used to determine whether a returned collection represents a locator or an
actual collection by-value.
Second, it must be possible to retrieve the elements using the locator. This
is also accomplished in the same manner as for non-nested collections under
Oracle8 and Oracle8i. However, for clarity a complete example follows.
declare
anum number;
tab TableType;
taboftab TableOfTableType;
-- retrieve nested table using a locator for parent
cursor nestedtabs is
select tot.column_value
from TABLE(CAST(taboftab AS TableOfTableType)) tot;
-- retrieve numbers using a locator for the table
cursor numbers is
select t.column_value
from TABLE(CAST(tab AS TableType)) t;
procedure process_tab is
begin
-- if this is a locator process as such
if ( UTL_COLL.IS_LOCATOR(tab) ) then
open numbers;
loop
fetch numbers into anum;
exit when numbers%NOTFOUND;
dbms_output.put_line('anum = ' || anum);
end loop;
close numbers;
else -- otherwise, use normal subscripts
for i in 1..tab.COUNT loop
anum := tab(i);
dbms_output.put_line('anum = ' || anum);
end loop;
end if;
end process_tab;
begin
-- retrieve the top-level collection object
select dbt.multilevel into taboftab
from DBTableWithTableOfTable dbt
where dbt.keyval = 1;
-- if top-level table is locator process as such
if ( UTL_COLL.IS_LOCATOR(taboftab) ) then
open nestedtabs;
loop
fetch nestedtabs into tab;
exit when nestedtabs%NOTFOUND;
process_tab;
end loop;
close nestedtabs;
else -- otherwise, use normal subscripts
for i in 1..taboftab.COUNT loop
tab := taboftab(i);
process_tab;
end loop;
end if;
end;
/
You will note from the above example that locators must be accessed via
SQL (i.e. the collection must be cast appropriately and elements must be
fetched rather than assigned). Obviously, writing code that can deal with
any combination of locators and values in a multilevel collection can be
tedious. Therefore, it is probably wise to agree in advance on when and
where locators will be used. In any event, it is possible to make code
smart enough to deal with any situation (as demonstrated by the above).
Support for Multilevel Collections in Object Views
--------------------------------------------------
The following DDL demonstrates how to create an object view equivalent to
the DBTableWithTableOfKeyedObject object table created above. Note that
this example is not particularly realistic, but does demonstrate much
of the syntax used in creating object views with multilevel collections.
Relational Tables Assumed to Already Exist
------------------------------------------
create table DBRelTable1 ( pk number, fk number );
create table DBRelTable2 ( pk number, fk number );
create table DBRelTable3 ( pk number, val number );
Creating the Object View
------------------------
create view DBViewWithTableOfKeyedObject
of KeyedObjectWithMulti
with object oid ( keyval ) as
select KeyedObjectWithMulti
( rt1.pk,
CAST( MULTISET
(select rt2.pk,
CAST( MULTISET
(select rt3.val
from DBRelTable3 rt3
where rt3.pk = rt2.fk )
AS TableType )
from DBRelTable2 rt2
where rt2.pk = rt1.fk )
AS TableOfKeyedObject )
)
from DBRelTable1 rt1;
Although object views of this type, like many other views, may not be
directly updatable, it is possible to support updates via an 'INSTEAD
OF' trigger. However, the details of doing so are not covered here.
Emulating Multilevel Collection in Oracle8 and Oracle8i
-------------------------------------------------------
Because multilevel collections are not directly supported in Oracle8 and
Oracle8i, object models requiring this capability must use object REF's
to nest collections inside other collections. This section will briefly
outline how this can be achieved and provide a short example, including
type and table DDL as well as representative SQL that demonstrates how
one might access information stored in nested collections.
First, the required object types must be created. Because we need to use
REF's to a collection everywhere a collection would be used in Oracle9i,
and because it is not possible to create a REF directly on a collection,
it is necessary to 'wrap' nested collections in an object type.
Create the Object and Collection Types
--------------------------------------
create or replace type WorkaroundObject as object
(
attr1 number,
attr2 varchar2(10)
);
/
create or replace type InnerNestedTable as table of WorkaroundObject;
/
create or replace type WrapperObject as object
(
pk number,
nt InnerNestedTable
);
/
NOTE: The addition of the 'pk' attribute is a matter of convenience as it
simplifies formulation of the DML for populating the tables. Such
'key' columns may or may not be required depending on the nature
of your application and data.
create or replace type OuterNestedTable as table of REF WrapperObject;
/
Create the Database Tables
--------------------------
create table DBWrapperTable of WrapperObject
nested table nt store as DBWT_nt;
create table DBTableWithWorkaround
(
pk number,
nt OuterNestedTable
)
nested table nt store as DBTWW_nt;
Populate the Database Tables with Data
--------------------------------------
insert into DBWrapperTable
values ( WrapperObject
( 1,
InnerNestedTable( WorkaroundObject( 1, 'one' ),
WorkaroundObject( 2, 'two' ),
WorkaroundObject( 3, 'three' ) )
)
);
insert into DBWrapperTable
values ( WrapperObject
( 2,
InnerNestedTable( WorkaroundObject( 4, 'four'),
WorkaroundObject( 5, 'five' ),
WorkaroundObject( 6, 'six' ) )
)
);
insert into DBTableWithWorkaround
values ( 1,
CAST( MULTISET( select ref(dbw)
from DBWrapperTable dbw
where dbw.pk between 1 and 2 )
AS OuterNestedTable )
);
Select Data from a Nested Collection Element
--------------------------------------------
select dbw3.attr2
from TABLE( select dbw2.column_value.nt
from TABLE( select dbw.nt
from dbtablewithworkaround dbw
where dbw.pk = 1 ) dbw2
where dbw2.column_value.pk = 1 ) dbw3
where dbw3.attr1 = 1
References
----------
For an overview of object-relational features in Oracle8 and up
Note: 69467.1
For information on support for objects and collections in Oracle9i
Oracle9i SQL Reference
PL/SQL User's Guide and Reference
Oracle9i Supplied PL/SQL Packages Reference
Oracle9i Application Developer's Guide - Object-Relational Features
