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

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.

Pages: 1, 2, 3, 4

Next Page