Object-Relational Concepts in Oracle 8i
                ---------------------------------------

Purpose and Scope
-----------------

This article describes the object-relational functionality available in Oracle
8i.  It primarily addresses objects from the SQL and PL/SQL perspective and is 
intended to serve as a foundation for better understanding access to and 
manipulation of Oracle objects via programmatic interfaces such as OCI, Pro*C, 
and JDBC/SQLJ.

This article does not represent an exhaustive treatment of objects in Oracle
8i.  For additional information, see the references listed at the end of this 
article.  

User Defined Types in Oracle 8i
-------------------------------

Oracle 8i features an extensible type system in which users can define
their own new data types.  User-defined types in Oracle 8i fall into
one of the following categories:

     Object types - Analogous to a record or structure in
                    a language such as PL/SQL or C, or a class
                    in an object-oriented language such as
                    C++ or Java.  Consists of one or more
                    attributes and a set of methods used
                    to manipulate those attributes.

     Collections  - Analogous to arrays, sets, bags, etc.,
                    from other languages such as C/C++, Java, 
                    or SmallTalk.  Collections contain zero
                    or more elements of a given datatype.

     Note: Unlike some languages which allow heterogeneous
           collections (i.e. one collection containing more
           than one type of element), collections in Oracle
           are strongly typed (i.e. a given collection type
           can contain one and only one type of element).

Collection types fall into one of the following categories:

     VARRAY       - Elements are ordered and the maximum
                    number of elements is fixed when the
                    type is declared.

     Note: Although the maximum number of elements for a VARRAY
           is defined when the type is created, the amount of
           space consumed by a VARRAY depends solely on the
           number of elements it currently contains.

     Nested Table - Elements are unordered and the maximum
                    number of elements is unbounded except
                    by system resources.

     Note: PL/SQL version 2 tables (i.e. index by binary_integer)
           are still available in Oracle8i, but are not a part
           of the object-relational features.  They are provided
           strictly for compatibility with older versions and
           are not interchangeable with nested tables in most
           contexts.  New applications should use nested tables
           wherever possible.

Finally, Oracle supports the REF type which allows objects to be
associated with other objects via reference (as opposed to being
embedded).  As is the case with collection types, REFs in 8i are
strongly typed.

Creating Objects in Oracle 8i
-----------------------------

The following section demonstrates the DDL required to create a
number of typical object types.  Later sections discuss the SQL
syntax used to insert, query, and update objects in the database.

Example 1 - User-Defined Object Type
------------------------------------

CREATE OR REPLACE TYPE Address AS OBJECT
( Street       VARCHAR2(80),
  City         VARCHAR2(80),
  State        CHAR(2),
  Zip          VARCHAR2(10) );

Example 2 - User-Defined Nested Table Type
------------------------------------------

CREATE OR REPLACE TYPE AddressList AS TABLE OF Address;

Example 3 - User-Defined VARRAY Type
------------------------------------

CREATE OR REPLACE TYPE PhoneList AS VARRAY(10) OF VARCHAR2(12);

NOTE: Collection types in Oracle 8i can contain only object types or
      primitive SQL types (i.e. NUMBER, VARCHAR2, DATE, etc), but not
      other collection types.  Further, the 'contained' objects may not
      contain any embedded collections although they may contain a REF
      to another object with an embedded collection.

Example 4 - User-Defined Object Type with Embedded Object
---------------------------------------------------------

CREATE OR REPLACE TYPE Person AS OBJECT
( FirstName    VARCHAR2(10),
  LastName     VARCHAR2(10),
  CurAddr      Address );

In this case, the attribute CurAddr is of object type Address.

Example 5 - User-Defined Object Type with REF
---------------------------------------------

CREATE OR REPLACE TYPE Person;

Note: The above type is referred to as an incomplete type.  Incomplete 
      types are required to support recursive types (i.e. types which 
      contain references to other objects of the same type).

CREATE OR REPLACE TYPE Person AS OBJECT
( FirstName    VARCHAR2(10),
  LastName     VARCHAR2(10),
  CurAddr      Address,
  Mom          REF Person,
  Dad          REF Person );

Note: You cannot create a REF to a collection type directly although
      you can embed the collection type within another object type
      and create a REF to the enclosing object.

Example 6 - User-Defined Object Type with Embedded Collections
--------------------------------------------------------------

CREATE OR REPLACE TYPE Person;

CREATE OR REPLACE TYPE Person AS OBJECT
( FirstName    VARCHAR2(10),
  LastName     VARCHAR2(10),
  CurAddr      Address,
  Mom          REF Person,
  Dad          REF Person,
  PrevAddrs    AddressList,
  PhoneNums    PhoneList );

Example 7 - User-Defined Object Type with Methods
-------------------------------------------------

CREATE OR REPLACE TYPE Person;

CREATE OR REPLACE TYPE Person AS OBJECT
( FirstName    VARCHAR2(10),
  LastName     VARCHAR2(10),
  CurAddr      Address,
  Mom          REF Person,
  Dad          REF Person,
  PrevAddrs    AddressList,
  PhoneNums    PhoneList,
  MEMBER FUNCTION FullName RETURN VARCHAR2,
  MEMBER PROCEDURE MoveTo(newAddr IN Address),
  PRAGMA RESTRICT_REFERENCES(FullName, RNDS, WNDS) );

Note: In this case, the PRAGMA RESTRICT_REFERENCES is only required to
      permit this function to be invoked from SQL.  Since the procedure
      cannot be invoked from SQL anyway, the PRAGMA is not necessary.

CREATE OR REPLACE TYPE BODY Person AS
  MEMBER FUNCTION FullName RETURN VARCHAR2 IS
  BEGIN
     RETURN (SELF.FirstName || ' ' || SELF.LastName);
  END FullName;

  MEMBER PROCEDURE MoveTo(newAddr IN Address) IS
     AddrCnt   NUMBER := SELF.PrevAddrs.Last();
  BEGIN
     -- make room for one more address
SELF.PrevAddrs.Extend();
-- save the current address as a previous address
SELF.PrevAddrs(AddrCnt + 1) := SELF.CurAddr;
-- make the new address the current address
SELF.CurAddr := newAddr;
  END MoveTo;
END;

Note: You cannot add methods to a collection type directly, although
      you can embed the collection type within another object type
      and add methods to the enclosing object type.

Object types are merely templates that describe the logical structure
and behavior of an object.  Instances of Oracle 8i object types can be
created via SQL or any of the programmatic interfaces.  A persistent
instance of an Oracle 8i object type must be stored in a table.

Example 8 - Creating an Object Table
------------------------------------

CREATE TABLE People OF Person
  NESTED TABLE PrevAddrs STORE AS PrevAddrTab;

Note: Although a nested table is logically embedded in an object, it is 
      physically stored as a separate table.  Thus, a separate clause is 
      provided (the NESTED TABLE ... STORE AS clause) to allow the user 
      to specify a table name for this storage table.

      Although the storage table cannot be queried directly, it can be 
      stored in its own tablespace with its own storage parameters and 
      can be indexed like any other database table.

Example 9 - Creating a Table with an Object Column
--------------------------------------------------

CREATE TABLE Corporation
( CorpName     VARCHAR2(80),
  CorpAddr     Address,
  CorpCEO      REF Person SCOPE IS People );

Note: The 'SCOPE IS' clause specifies that all references stored in the 
      CorpCEO column of the table reference objects in the People 
      table.  The resulting REF can then be stored more efficiently.

      Such scoped references are analogous to foreign key constraints 
      in a relational database, but do not guarantee the existence of 
      the referenced objects.  If the referenced object is deleted, a 
      'dangling' REF results.

The 'IS DANGLING' predicate can be used to select rows containing a REF
which does not currently reference an existing object (i.e. a 'dangling'
REF).  For example:

      SELECT CorpName FROM Corporation
      WHERE CorpCEO IS DANGLING;

Example 10 - Creating an Object Table with Constraints and Defaults
-------------------------------------------------------------------

Oracle 8i does not support constraints or defaults on object types,
but you can specify constraints and defaults when creating a table
in which to store instances of an object type.

NOTE: If you are following these examples in order, you will need to
      drop the table 'People' prior to running Examples 10 and 11.

CREATE TABLE People OF Person
( FirstName NOT NULL,
  LastName  NOT NULL,
  CurAddr   DEFAULT Address('500 Oracle Parkway',
                            'Redwood Shores',
                            'CA', '94065') )
  NESTED TABLE PrevAddrs STORE AS PrevAddrTab;

Example 11 - Returning a Nested Table as a Locator
--------------------------------------------------

In many cases, returning the entire nested table at the time the object
is retrieved from a table may be prohibitive.  In such cases, the nested
table can be returned as a locator.  The locator enables the database to
locate the corresponding rows in the correct database table and ensures
that the data returned is consistent with the state of the database at
the time the original query was executed.

CREATE TABLE People OF Person
( FirstName NOT NULL,
  LastName  NOT NULL,
  CurAddr   DEFAULT Address('500 Oracle Parkway',
                            'Redwood Shores',
                            'CA', '94065') )
  NESTED TABLE PrevAddrs STORE AS PrevAddrTab RETURN AS LOCATOR;

Once the locator has been retrieved, it must be cast to the appropriate
table type before it can be queried.  Refer to the section on querying object
tables for an example of this.

Populating Tables with Objects in Oracle 8i
-------------------------------------------

Given the tables created in the previous section, the following section
demonstrates how to populate those tables with data.

Example 12 - Populating the People Table
----------------------------------------

INSERT INTO People 
  VALUES ( Person('Mr.', 'Ellison', 
                   Address('1 First Street',
                           'Orlando', 'FL', '34812'),
                   NULL, NULL,
                   AddressList(Address('2 Second Street',
                                       'Orlando', 'FL', '34812'),
                               Address('3 Third Street',
                                       'Orlando', 'FL', '34812')),
                   PhoneList('407-555-1234', '407-555-4321')) );

INSERT INTO People 
  VALUES ( Person('Mrs.', 'Ellison', 
                   Address('1 First Street',
                           'Orlando', 'FL', '34812'),
                   NULL, NULL,
                   AddressList(Address('2 Second Street',
                                       'Orlando', 'FL', '34812'),
                               Address('3 Third Street',
                                       'Orlando', 'FL', '34812')),
                   PhoneList('407-555-1234', '407-555-4321')) );

INSERT INTO People 
  VALUES ( Person('Larry', 'Ellison', 
                   Address('1 First Street',
                           'Orlando', 'FL', '34812'),
                  (SELECT REF(p) FROM People p
                   WHERE p.FirstName = 'Mrs.' 
                     AND p.LastName = 'Ellison'),
                  (SELECT REF(p) FROM People p
                   WHERE p.FirstName = 'Mr.' 
                     AND p.LastName = 'Ellison'),
                   AddressList(Address('2 Second Street',
                                       'Orlando', 'FL', '34812'),
                               Address('3 Third Street',
                                       'Orlando', 'FL', '34812')),
                   PhoneList('407-555-1234', '407-555-4321')) );

Example 13 - Populating the Corporation Table
---------------------------------------------

INSERT INTO Corporation VALUES ( 'Oracle Corporation', 
                                  Address('500 Oracle Parkway',
                                          'Redwood Shores',
                                          'CA', '94065'),
                                 (SELECT REF(p) FROM People p
                                  WHERE p.FirstName = 'Larry' 
                                    AND p.LastName = 'Ellison') );

Querying Tables with Objects in Oracle 8i
-----------------------------------------

This section demonstrates a number of common types of queries that can 
be executed against object tables and tables with object columns.

Example 14 - Selecting an Embedded Object
-----------------------------------------

SELECT c.CorpAddr FROM Corporation c
WHERE c.CorpName = 'Oracle Corporation';

RETURNS: One or more instances of object type Address

Example 15 - Selecting an Attribute of an Embedded Object
---------------------------------------------------------

SELECT c.CorpAddr.Street FROM Corporation c
WHERE c.CorpName = 'Oracle Corporation';

RETURNS: One or more instances of VARCHAR2

Example 16 - Selecting an Object from an Object Table
-----------------------------------------------------

SELECT VALUE(p) FROM People p
WHERE p.LastName = 'Ellison';

RETURNS: One or more instances of object type Person

Example 17 - Selecting an Object Reference from an Object Table
---------------------------------------------------------------

SELECT REF(p) FROM People p
WHERE p.LastName = 'Ellison';

RETURNS: One or more REF's to objects of type Person

Example 18 - Selecting a Nested Table Column from an Object Table
-----------------------------------------------------------------

SELECT p.PrevAddrs FROM People p
WHERE p.LastName = 'Ellison';

RETURNS: One or more instances of type AddressList (a nested table)

Example 19 - Selecting a VARRAY Column from an Object Table
-----------------------------------------------------------

SELECT p.PhoneNums FROM People p
WHERE p.LastName = 'Ellison';

RETURNS: One or more instances of type PhoneList (a VARRAY)

Example 20 - Selecting Objects from a Nested Table
--------------------------------------------------

SELECT VALUE(a) 
FROM TABLE(SELECT p.PrevAddrs FROM People p
           WHERE p.FirstName = 'Larry'
             AND p.LastName = 'Ellison') a;

RETURNS: One or more instances of type Address

Note: The 'TABLE' operator provides the same functionality as
      that offered by the 'THE' in Oracle8.  The 'THE' operator
      is still supported, but is being deprecated.  Therefore, 
      its use should be discouraged.

Note: You cannot retrieve REFs for the rows in a nested table
      even though the underlying table is of an object type.
      Thus, the following query results in an error:

***WRONG BELOW***

SELECT REF(a) 
FROM TABLE(SELECT p.PrevAddrs FROM People p
           WHERE p.FirstName = 'Larry'
             AND p.LastName = 'Ellison') a;

***WRONG ABOVE***

Example 21 - Collection Unnesting using the TABLE operator
----------------------------------------------------------

SELECT p.FirstName, p.LastName, a.* 
FROM People p, TABLE(p.PrevAddrs) a
WHERE p.LastName = 'Ellison';

RETURNS: One or more rows in which the columns from the People
         table are repeated for each address contained within
         that person's list of previous addresses.

NOTE: You may also hear collection unnesting referred to as flattened
      subqueries.

p.FirstName  p.LastName  a.Street        a.City     a.State   a.Zip
------------ ----------- --------------  ---------- --------- --------
Mr.          Ellison     2 Second Street Orlando    FL        34812
Mr.          Ellison     3 Third Street  Orlando    FL        34812
Mrs.         Ellison     2 Second Street Orlando    FL        34812
Mrs.         Ellison     3 Third Street  Orlando    FL        34812
Larry        Ellison     2 Second Street Orlando    FL        34812
Larry        Ellison     3 Third Street  Orlando    FL        34812

Example 22 - Collection Unnesting using an Outer Join
-----------------------------------------------------

The previous example only returns rows for People with at least
one previous address.  If you would also like to see people with no
previous addresses, you must use an outer join.  In this case, the
Address columns are null for People with no previous addresses.

SELECT p.FirstName, p.LastName, a.*
FROM People p, TABLE(p.PrevAddrs) (+) a
WHERE p.Lastname = 'Ellison';

Example 23 - Collection Unnesting with a VARRAY
-----------------------------------------------

The same collection unnesting syntax demonstrated in the examples above
can be used to flatten queries involving a VARRAY.

SELECT p.FirstName, p.LastName, ph.*
FROM People p, TABLE(p.PhoneNums) ph
WHERE p.LastName = 'Ellison';

Example 24 - Selecting Data from a Referenced Object
----------------------------------------------------

References to objects are followed implicitly when used in a query.  In
the following example, the Mom reference is followed to retrieve the
FirstName attribute of the corresponding Person object.

SELECT p.Mom.FirstName FROM People p
WHERE p.FirstName = 'Larry' and p.LastName = 'Ellison';

RETURNS: One or more instances of VARCHAR2

Example 25 - Selecting a Member Function on an Object Table
-----------------------------------------------------------

SELECT p.FullName() FROM People p
WHERE p.LastName = 'Ellison';

Note: Unlike normal PL/SQL stored functions, calls to member functions
      from within an SQL statement require the empty parentheses, even
      if there are no arguments passed to the function.

Example 26 - Selecting a Member Function on a Referenced Object
---------------------------------------------------------------

SELECT c.CorpCEO.FullName() FROM Corporation c
WHERE c.CorpName = 'Oracle Corporation';

Note: Even though the table alias is not required when selecting an
      object column as a whole, it is required if you wish to refer
      to attributes or member functions of the object.  In any case,
      use of table aliases is good practice and is recommended.

Example 27 - Querying a Nested Table Using a Nested Table Locator
-----------------------------------------------------------------

In the following example, the list of previous addresses is retrieved
for the CEO of Oracle Corporation.  Since it is not necessarily known
at compile time whether the nested table will be returned 'by value'
or as a locator, the PL/SQL code uses UTL_COLL.IS_LOCATOR to make
that determination and processes the table accordingly.

DECLARE
   theCEO   Person;
   theAddr  Address;
BEGIN
   SELECT VALUE(p)
   INTO theCEO
   FROM People p
   WHERE p.FirstName = 'Larry'
     AND p.LastName = 'Ellison';
   IF (UTL_COLL.IS_LOCATOR(theCEO.PrevAddrs)) THEN
      SELECT VALUE(a)
      INTO theAddr
      FROM TABLE(CAST(theCEO.PrevAddrs AS AddressList)) a
      WHERE a.Street = '2 Second Street';
   ELSE
      FOR ndx IN 1..theCEO.PrevAddrs.COUNT LOOP
         IF (theCEO.PrevAddrs(ndx) = '2 Second Street') THEN
            theAddr := theCEO.PrevAddrs(ndx);
         END IF;
      END LOOP;
   END IF;
END;

In the above example, the returned attribute 'PrevAddrs' is tested to
determine if it represents a locator.  If so, the locator is cast to the 
appropriate table type and queried via SQL.  If not, then it represents 
the actual nested table and can be searched one element at a time.

Example 28 - Invoking a Member Procedure on an Object Type
----------------------------------------------------------

The following example demonstrates the use of a member procedure to 
update the state (i.e. attributes) of an instance of an object type.

DECLARE
   theCEO  Person;
   newAddr Address := Address('500 Oracle Parkway',
                              'Redwood Shores',
                              'CA', '94065');
BEGIN
   -- retrieve Person object from the database
   SELECT VALUE(p)
   INTO theCEO
   FROM People p
   WHERE p.FirstName = 'Larry'
     AND p.LastName = 'Ellison';

   -- change the address of the transient Person object
   theCEO.moveTo(newAddr);

   -- update the corresponding Person object in the database
   UPDATE People p
   SET p = theCEO
   WHERE p.FirstName = 'Larry'
     AND p.LastName = 'Ellison';
END;

NOTE: The above example assumes that the nested table PrevAddrs is 
      returned by value (i.e. not as a locator) since the moveTo method 
      was not written to handle the locator case correctly.  It is left 
      as an exercise to the reader to modify Example 7 to handle this 
      case (see Example 27 for a hint).

Example 29 - Inserting into a Nested Table Using SQL
----------------------------------------------------

An alternative to retrieving the entire nested table, modifying it, and
replacing the original with the modified copy (as in the above example)
is to insert into or update the nested table directly via SQL.

INSERT INTO TABLE(SELECT p.PrevAddrs FROM People p
                  WHERE p.FirstName = 'Larry'
                    AND p.LastName = 'Ellison')
          VALUES ( Address('123 First Avenue',
                           'San Francisco',
                           'CA', '90909') );

Example 30 - Update a Nested Table Using SQL
--------------------------------------------

UPDATE TABLE(SELECT p.PrevAddrs FROM People p
             WHERE p.FirstName = 'Larry'
               AND p.LastName = 'Ellison') pa
SET pa.Zip = '90910'
WHERE pa.City = 'San Francisco'
  AND pa.State = 'CA';

NOTE: Inserts and updates to collections via SQL are only supported for
      nested tables.  VARRAY collections must be retrieved and updated
      as a single unit.  Thus, the approach used in example 28 would
      be required for inserting into or updating a VARRAY column.

Example 31 - Querying Referenced Objects using DEREF
----------------------------------------------------

When querying a REF column, you can dereference the REF implicitly as 
in example 24, or you can explicitly dereference the REF to retrieve 
the entire object by value.

SELECT DEREF(c.CorpCEO) FROM Corporation c
WHERE c.CorpName = 'Oracle Corporation';

Example 32 - Using the CAST Operator to Create a Collection Object
------------------------------------------------------------------

Given the following table of addresses already exists in the database:

CREATE TABLE Addresses
( FirstName  VARCHAR2(80),
  LastName   VARCHAR2(80),
  PrevAddr   Address );

INSERT INTO Addresses 
  VALUES ('Bill','Bailey',
          Address('111 First Street','Orlando','FL','34812));

INSERT INTO Addresses 
  VALUES ('Bill','Bailey',
          Address('222 Second Street','Orlando','FL','34812));

You could insert a list of previous addresses (of type AddressList) using
the following syntax rather than hardcoding the addresses directly into
the INSERT statement:

INSERT INTO People 
  VALUES ( Person('Bill', 'Bailey', 
                   Address('9 Ninth Street',
                           'Orlando', 'FL', '34812'),
                   null, null,
                   CAST(MULTISET(SELECT a.PrevAddr 
                                 FROM Addresses a
                                 WHERE FirstName = 'Bill'
                                   AND LastName = 'Bailey')
                        AS AddressList),
                   PhoneList('407-555-8888', '407-555-9999')) );