O'Reilly Network: Substituting and Converting Object Types in a Hierarchy

Published on O'Reilly Network (http://www.oreillynet.com/)
http://www.oreillynet.com/pub/a/network/2002/11/12/feuerstein.html
See this if you're having trouble printing code examples

Substituting and Converting Object Types in a Hierarchy

by Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition
11/12/2002

Editor's note: In Part 2 in this series on new features in Oracle 9i, Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition, explores the advantages and flexibilities of object-type hierarchies by examining substitutability and type conversion. (For a quick review of object types and how you can build object-type hierarchies by taking advantage of inheritance, see Steven's first article, Inherit the Database: Oracle9i's Support for Object Type Inheritance.) Beware, however, that by the time you've finished reading this article, you may find yourself to be a bit hungry.

In Part 1 in this series, I introduced one of Oracle9i's most significant enhancements to the SQL and PL/SQL language: support for object-type inheritance. With inheritance, a subtype inherits all of the attributes and methods from their supertypes, and not just the immediate subtype, but any subtype or ancestor in the resulting object-type hierarchy. Inheritance allows you to implement business logic at low levels in the hierarchy and then make them automatically available in all object types derived from those supertypes. You don't have to code that business rule multiple times to make it available in the different object types in the hierarchy.

Inheritance also allows developers to take advantage of "dynamic polymorphism," which means that at the time your code is run, Oracle identifies and executes the "nearest" or most specific method in the object hierarchy that corresponds to your method invocation.

What Is Substitutability?

When you define a type hierarchy, you start with a root type, from which all other subtypes are derived. In the Java language, for example, all classes (roughly equivalent to Oracle "object types") derive from the root Object class. In Oracle, where the object model has been layered on top of a relational database, there's no built-in and global hierarchy. So every time you work with object types, you define your own root.

For this article, we'll work with a very simple type hierarchy. In this hierarchy, the food type, food_t, is the root. The dessert type, dessert_t, is a subtype of food, and cake, represented by cake_t, is a further subtype of dessert_t. Here are the definitions of these types (showing attributes only, and no associated PL/SQL methods):

CREATE TYPE food_t AS OBJECT (
   name VARCHAR2(100),
   food_group                    VARCHAR2 (100),
   grown_in                      VARCHAR2 (100)
   )
   NOT FINAL
   ;
/

CREATE TYPE dessert_t UNDER food_t (
   contains_chocolate    CHAR(1),
   year_created          NUMBER(4)
   )
   NOT FINAL
   ;
/

CREATE TYPE cake_t UNDER dessert_t (
   diameter      NUMBER,
   inscription   VARCHAR2(200)
   )
   ;
/

Each type has its own type-specific attributes. Each subtype, don't forget, also inherits the attributes of its supertype(s). So if I want to instantiate an object of type cake, I need to supply a total of seven attributes, as shown here:

Related Reading

Oracle PL/SQL Programming
By Steven Feuerstein

Table of Contents
Index
Sample Chapter

Read Online--Safari Search this book on Safari:

DECLARE
   my_favorite   cake_t
   := cake_t (
         'Marzepan Delight',
         'CARBOHYDRATE',
         'Swedish Bakery',
         'N',
         1634,
         8,
         'Happy Birthday!'
      );
BEGIN
   DBMS_OUTPUT.put_line (my_favorite.NAME);
   DBMS_OUTPUT.put_line (my_favorite.inscription);
END;
/

Notice that I reference and display an attribute from the base food type and an attribute from the cake subtype. They're all equally available to me in an object instantiated from cake.

The way to think about such a hierarchy is this: A cake is a type of dessert, which in turn is a type of food. But not all desserts are cake, and not all foods are dessert (putting aside the obvious cultural complications here, for instance, something that's not considered a dessert in the United States may well be considered one in, say, Ecuador). Any characteristic of food applies to cakes, but not all characteristics of a cake will necessarily make sense for a food, such as a cucumber.

Once you've defined your hierarchy, you'll want to work with and make changes to the types in that hierarchy. In some cases, you may wish to select and view all types across the entire hierarchy. In other cases, you may only want to update a specific level in the hierarchy, such as all cakes. And then there are situations where you'll want to work with, say, all desserts that are not cakes. And that leads us directly to the concept of substitutability.

A supertype is substitutable if one of its subtypes can substitute for it in some location, such as in a column or in a program variable, in which the declared type is the supertype (and not that particular supertype).

Suppose I create a table of objects of type food_t:

CREATE TABLE sustenance OF food_t;

I can then insert rows into this table as follows:

BEGIN
   INSERT INTO sustenance
        VALUES (food_t (
                  'Brussel Sprouts',
                  'VEGETABLE',
                  'farm'
               )
               );

   INSERT INTO sustenance
        VALUES (dessert_t (
                  'Jello',
                  'PROTEIN',
                  'bowl',
                  'N',
                  1887
               )
               );

   INSERT INTO sustenance
        VALUES (cake_t (
                  'Marzepan Delight',
                  'CARBOHYDRATE',
                  'bakery',
                  'N',
                  1634,
                  8,
                  'Happy Birthday!'
               )
               );
END;
/

After running this code, my table will contain three rows: a food object, a dessert object, and a cake object. In this block of code, I've substituted my subtypes for the supertype in two of the inserts. This doesn't raise an error because cakes and desserts are types of food.

I can execute a query against this table in SQL*Plus and it shows me all of (and only) the food type attributes of the three rows.

SQL> SELECT * FROM sustenance;

NAME                      FOOD_GROUP         GROWN_IN
------------------------- ------------------ -------------
Brussel Sprouts           VEGETABLE          farm
Jello                     PROTEIN            bowl
Marzepan Delight          CARBOHYDRATE       bakery

I can also take advantage of substitutability inside PL/SQL blocks. In the following code, I declare a food, but initialize it with a dessert, a more specific type of food.

DECLARE
   mmm_good food_t :=
      dessert_t (
         'Super Brownie', 
         'CARBOHYDRATE', 
         'my oven', 'Y', 1994);
BEGIN
   DBMS_OUTPUT.PUT_LINE (
      mmm_good.name);
END;
/

And here's an example of substitutability within a PL/SQL collection:

DECLARE
   TYPE foodstuffs_nt IS TABLE OF food_t;
   
   fridge_contents foodstuffs_nt := (
      food_t (
        'Eggs benedict', 'PROTEIN', 'Farm'),
      dessert_t (
        'Strawberries and cream', 
        'FRUIT', 'Backyard', 'N', 2001),
      cake_t (
        'Chocolate Supreme', 'CARBOHYDRATE', 
        'Kitchen', 'Y', 2001, 
         8, 'Happy Birthday, Veva'));

BEGIN
   FOR indx IN 
      fridge_contents.FIRST .. 
      fridge_contents.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE (
         fridge_contents(indx).name);
   END LOOP;
END;
/

Now let's take a look at an INSERT that doesn't work. Suppose I create an object table of desserts:

CREATE TABLE sweet_nothings OF dessert_t;
/

If I then try to insert an object of type food, Oracle will raise an error, as shown here:

BEGIN
   INSERT INTO sweet_nothings
        VALUES (dessert_t (
                  'Jello',
                  'PROTEIN',
                  'bowl',
                  'N',
                  1887
               )
               );

   INSERT INTO sweet_nothings
        VALUES (food_t (
                  'Brussel Sprouts',
                  'VEGETABLE',
                  'farm'
               )
               );
END;
/

PL/SQL: ORA-00932: inconsistent datatypes

I receive this error because while any dessert is a food, not any food is a dessert. I can't insert an object of type food_t into a column of type dessert_t.

Now consider the same scenario, PL/SQL-wise. I declare an object in my program of type food and initialize it with a dessert. Notice that I specify Y or "Yes, it sure does!" for the contains_chocolate attribute. If I try to specify that dessert-specific attribute in my code, however, PL/SQL gives me an error:

SQL> DECLARE
  2  -- Again, I substitute, but this time
  3  -- I also try to access my cake attribute.
  4     mmm_good food_t :=
  5        dessert_t (
  6           'Super Brownie', 
  7           'CARBOHYDRATE', 
  8           'my oven', 'Y', 1994);
  9  BEGIN
 10     DBMS_OUTPUT.PUT_LINE (
 11        mmm_good.contains_chocolate);
 12  END;
 13  /
      mmm_good.contains_chocolate);
               *
ERROR at line 11:
ORA-06550: line 11, column 16:
PLS-00302: component 'CONTAINS_CHOCOLATE' must be declared

As one would expect, types are, as a rule, substitutable (that is, you can substitute a subtype for its supertype). You can take advantage of substitutability with object types defined as attributes of object types, columns of tables, or rows in tables and collections.

Oracle doesn't provide any way, within the definition of an object type itself, to turn off substitutability. Any object type is theoretically or potentially substitutable. Oracle does, on the other hand, offer a way to constrain substitutability and even makes it impossible, when you define usages of that object type.

How to Turn Off Substitutability

Why would I want to constrain or limit substitutability? I may want my table to contain objects of a specific type within my hierarchy, not any subtypes. To accommodate this need, Oracle lets you turn off all substitutability on a column or attribute, including embedded attributes and collections nested to any level. You do this by using the following clause:

NOT SUBSTITUTABLE AT ALL LEVELS

Suppose that I create another table to define meals I serve on a given day. Here's the table:

CREATE TABLE meal (
   served_on DATE,
   appetizer food_t,
   main_course food_t,
   dessert dessert_t
   )
   COLUMN appetizer NOT SUBSTITUTABLE AT ALL LEVELS
   ;

I use the NOT SUBSTITUTABLE clause to indicate that you can't use a subtype of food when providing a value for the appetizer column. I don't want anyone sneaking in a dessert for the appetizer.

Now consider the code in Example 1. I try to insert two different meals. In the first INSERT, I supply an object of type food_t for appetizer, which is fine. In my second insert, I try to pass off a dessert as an appetizer. The result, when executed, is the following error:

ERROR at line 1:
ORA-00932: inconsistent datatypes

Example 1. An attempt to define two meals.

BEGIN
   INSERT INTO meal VALUES (
      SYSDATE,
      food_t ('Shrimp cocktail', 'PROTEIN', 'Ocean'),
      food_t ('Eggs benedict', 'PROTEIN', 'Farm'),
      dessert_t ('Strawberries and cream', 'FRUIT', 'Backyard', 'N', 2001));
        
   INSERT INTO meal VALUES (
      SYSDATE + 1,
      dessert_t ('Strawberries and cream', 'FRUIT', 'Backyard', 'N', 2001),
      food_t ('Eggs benedict', 'PROTEIN', 'Farm'),
      cake_t ('Apple Pie', 'FRUIT', 'Baker''s Square', 'N', 2001, 8, NULL));
END;

I can also apply the NOT SUBSITUTABLE clause to an entire object table. Example 2 demonstrates this capability. I create a table of food_t objects called brunches. I can then successfully insert an object of type food_t, but get the same "inconsistent datatypes" error when I try to put a dessert into this table.

Example 2. Constraining substitutability in an object table.

SQL> CREATE TABLE brunches OF food_t NOT SUBSTITUTABLE AT ALL LEVELS; 

Table created.

SQL> 
SQL> INSERT INTO brunches VALUES (
  2     food_t ('Eggs benedict', 'PROTEIN', 'Farm'));

1 row created.

SQL> INSERT INTO brunches VALUES (
  2     dessert_t ('Strawberries and cream', 'FRUIT', 'Backyard', 'N', 2001));  
   dessert_t ('Strawberries and cream', 'FRUIT', 'Backyard', 'N', 2001))
   *
ERROR at line 2:
ORA-00932: inconsistent datatypes

There are two things to remember about constraining substitutability:

There's no mechanism to turn off substitutability for REF columns.
A column must be a top-level column for the clause NOT SUBSTITUTABLE AT ALL LEVELS to be applied to it. You can't apply the clause to an object-type attribute.

Constraining Substitutability to a Specific Subtype

So I can turn off all levels of substitutability, but what if I want to turn off all substitutability except for a particular subtype? Suppose, for example, that I want to create a PL/SQL collection of desserts that can contain only cakes. Or I want to set a rule in my meals table that all desserts must be cakes. Oracle offers the IS OF clause for just this purpose. Here's a re-definition of the meals table that offers two different kinds of substitutability constraints:

CREATE TABLE meal (
   served_on DATE,
   appetizer food_t,
   main_course food_t,
   dessert dessert_t
   )
   COLUMN appetizer NOT SUBSTITUTABLE AT ALL LEVELS,
   COLUMN dessert IS OF (ONLY cake_t)
   ;

And now I'll only be able to add meals in which the dessert is defined as a cake. So the following INSERT is rejected:

SQL> BEGIN
  2    -- This will no longer work.
  3    INSERT INTO meal VALUES (
  4    SYSDATE,
  5    food_t ('Shrimp cocktail', 
  6       'PROTEIN', 'Ocean'),
  7    food_t ('Eggs benedict', 
  8       'PROTEIN', 'Farm'),
  9    dessert_t ('Strawberries and cream', 
 10       'FRUIT', 'Backyard', 'N', 2001));
 11  END;
 12  /
BEGIN
*
ERROR at line 1:
ORA-00932: inconsistent datatypes

You can only use the IS OF type operator to constrain row and column objects to a single subtype, but not several. You must also use the ONLY keyword, even though that's the only choice available now. You can use either IS OF type or NOT SUBSTITUTABLE AT ALL LEVELS to constrain an object column, but you can't use both for that same column. Clearly, as shown previously, you can apply those constraints to different columns.

Widening and Narrowing Object Types

There are two very useful concepts to talk about when working with objects in a type hierarchy: widening and narrowing, particularly in assigning an object of one type to a variable or column declared with a different type in the hierarchy. Here are some definitions:

Widening is an assignment in which the declared type of the source is more specific than the declared type of the target. If I assign an object (or object-type instance, to be specific) of type cake_t to a variable declared with type dessert_t, I've performed widening.
Narrowing is an assignment in which the declared type of the source is more general than the declared type of the target. If I assign an object of type dessert_t to a variable declared with type cake_t, I've performed a narrowing operation.

Widening is actually native to Oracle's object-type hierarchies and its substitutability feature. Any cake is also a dessert is also a food. So unless you specifically constrain substitutability, a subtype can be treated as, stored as, and manipulated as any of its supertypes. You've already seen several examples of this process in this article.

Let's examine how you accomplish narrowing, a more complicated step, in SQL and PL/SQL in Oracle9i.

Narrowing with `TREAT`

Oracle provides a special function called TREAT that allows you to perform narrowing operations. The TREAT function explicitly changes the declared type of the source in an assignment to a more specialized target type or subtype in the hierarchy.

To successfully narrow, you have to use TREAT. Without use of this function, you won't be able to reference subtype-specific attributes and methods.

Here's the general syntax of this function:

TREAT (<object instance> AS <object type>)

where <object instance> is a column or collection row value that's of a particular supertype in an object hierarchy, and <object type> is a subtype in that hierarchy.

Let's look at some examples of how to use TREAT. Suppose that I insert three rows into the meal table as shown in Example 3. Notice that in the third row, I've passed in a dessert for a main course, one of my son's favorite mealtime activities! I'm able to do this because I haven't constrained substitutability on the main_course column.

Example 3. Populating the meal table.

BEGIN
   -- Populate the meal table
   INSERT INTO meal VALUES (
      SYSDATE,
      food_t ('Shrimp cocktail', 'PROTEIN', 'Ocean'),
      food_t ('Eggs benedict', 'PROTEIN', 'Farm'),
      dessert_t ('Strawberries and cream', 'FRUIT', 'Backyard', 'N', 2001));
        
   INSERT INTO meal VALUES (
      SYSDATE + 1,
      food_t ('Shrimp cocktail', 'PROTEIN', 'Ocean'),
      food_t ('Stir fry tofu', 'PROTEIN', 'Vat'),
      cake_t ('Apple Pie', 'FRUIT', 'Baker''s Square', 'N', 2001, 8, NULL));
      
   INSERT INTO meal VALUES (
      SYSDATE + 1,
      food_t ('Fried Calamari', 'PROTEIN', 'Ocean'),
      -- Butter cookies for dinner? Yikes!
      dessert_t ('Butter cookie', 'CARBOHYDRATE', 'Oven', 'N', 2001),
      cake_t ('French Silk Pie', 'CARBOHYDRATE', 
         'Baker''s Square', 'Y', 2001, 6, 'To My Favorite Frenchman'));      
END;
/

Suppose I want to see a list of all of the meals in which a dessert has been provided for the main course. I use the TREAT operator in the WHERE clause as follows:

SELECT * 
  FROM meal
 WHERE TREAT (main_course AS dessert_t) IS NOT NULL;

The TREAT function, which, by the way, in Oracle9i Release 1 can only be used in SQL statements (and not in PL/SQL blocks), returns a NULL object type for any main courses that are not desserts.

Suppose I'd like to see whether or not any of my main courses contain chocolate. This attribute is specific to desserts, so if I try to directly reference that attribute without TREAT, my query fails, as I show here:

SQL> SELECT main_course.contains_chocolate 
  2    FROM meal
  3   WHERE TREAT (main_course AS dessert_t) 
  4         IS NOT NULL;
SELECT main_course.contains_chocolate
                   *
ERROR at line 1:
ORA-00904: invalid column name

Even though all of the main courses selected are actually desserts, there's no way for Oracle to know that; a main_course column is declared as type food_t. So what should I do? Use the TREAT operator in the SELECT list as well as the query. This query and the results are shown in Example 4.

Example 4. Using the TREAT operator.

SQL> SELECT TREAT (main_course AS dessert_t).contains_chocolate chocolatey
  2    FROM meal
  3   WHERE TREAT (main_course AS dessert_t) IS NOT NULL;

CHOCOLATEY
---------------
N

I can also use TREAT in DML operations, such as INSERTs and UPDATEs.

Suppose, for example, that I don't really want to allow you to add a row to the meal table in which a dessert is offered as the main course. I can add a constraint to the table to stop this from happening, but I can also remove any such definitions by using TREAT with UPDATE.

Just remember that we can't yet use TREAT outside of a SQL statement and directly in native PL/SQL code. Perhaps they'll give us a PL/SQL TREAT in Oracle9i Release 2.

The Programming Trade-Offs of Type Hierarchies

Oracle's implementation of inheritance, without a doubt, greatly improves the usefulness and power of object types in the PL/SQL language. Will this mean that many more PL/SQL developers will now take advantage of object types and, in particular, these great new features? I have my doubts, and for two reasons.

Also In This Series

Inherit the Database: Oracle9i's Support for Object Type Inheritance

New Datatypes, New Possibilities

Native Compilation, CASE, and Dynamic Bulk Binding

Table Functions and Cursor Expressions

Multi-Level Collections in Oracle 9i

HTTP Communication from Within the Oracle Database

Oracle 9i Release 2 Developments for PL/SQL Collections

Using PL/SQL Records in SQL Statements

First, many developers and development groups are perfectly happy with the pure relational model. It gets the job done, is straightforward, and is easily managed by the developers and DBAs. Sure, the object model might offer some advantages, but probably not enough to justify the additional training costs and mental shifts required.

Second, without a doubt, working with the object model involves writing more complex code. You have to deal with constructors, and many special operators like TREAT, FINAL, SUBSTITUTABLE, and the like. It can take quite a while to get proficient with the variations, and then you still end up writing code that's harder to understand and maintain.

So by all means investigate what object-type hierarchies can do for you. You might find object types to be a close fit and have major advantages. In that case, explore these features in depth and make sure to apply them to their fullest. If you don't see a good fit, don't feel bad. Just stick with your relational tables and simpler data structures. It's worked for 25 years, the anniversary that Oracle recently celebrated. It'll probably be good for another dozen.

This article was originally published in the January 2002 issue of Oracle Professional. The material in Feuerstein's articles--and those he cowrote with Bryn Llewellyn--are based on Oracle Corporation white papers originally prepared by Llewellyn for Oracle OpenWorld 2001 in San Francisco and OracleWorld Copenhagen in June 2002 and Oracle PL/SQL Programming, 3rd Edition.

Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language.