Substituting and Converting Object Types in a Hierarchy
Pages: 1, 2, 3, 4

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, having written ten books on the subject. Steven is a Senior Technology Advisor with Quest Software and has been developing software since 1980.

O'Reilly & Associates recently released (September 2002) Oracle PL/SQL Programming, 3rd Edition.

• Sample Chapter 10, Dates and Timestamps, is available free online.

• You can also look at the Table of Contents, the Index, and the Full Description of the book.

• For more information, or to order the book, click here.

---

Return to the O'Reilly Network.