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

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.

Pages: 1, 2, 3, 4

Next Page