Using Object Types in Relational Tables

So far in my series on object-oriented development in Oracle Database, all manipulation of object type instances have taken place in PL/SQL.

But as you may have guessed from the fact that you "CREATE OR REPLACE" object types, those types are also available for us in SQL. You can create relational tables of object types, called object tables. You can also define columns of relational tables whose datatypes are object types.

In this post, I will explore both of these approaches. All the code you see below may be found in this LiveSQL script, so you can get to know these features by playing around with them yourself.

Object Tables

It's easy to create object tables and work with the instances in those tables (both selecting and changing rows of data). Here's a simple example:

CREATE TYPE food_ot AS OBJECT (
   name VARCHAR2 (100),
   food_group VARCHAR2 (50),
   grown_in VARCHAR2 (100)
) NOT FINAL
/

CREATE TABLE food_table OF food_ot
   (CONSTRAINT food_table_pk PRIMARY KEY (name))
/

BEGIN
   INSERT INTO food_table
        VALUES (NEW food_ot ('Mutter Paneer', 'Curry', 'India'));

   INSERT INTO food_table
        VALUES (NEW food_ot ('Cantaloupe', 'Fruit', 'Backyard'));

   COMMIT;
END;
/

SELECT *
  FROM food_table
/

NAME             FOOD_GROUP   GROWN_IN      
---------------- ------------ --------------
Mutter Paneer    Curry        India         
Cantaloupe       Fruit        Backyard      

BEGIN
   UPDATE food_table
      SET grown_in = 'Florida'
    WHERE name = 'Cantaloupe';
END;
/

SELECT *
  FROM food_table
/

NAME             FOOD_GROUP   GROWN_IN      
---------------- ------------ --------------
Mutter Paneer    Curry        India         
Cantaloupe       Fruit        Florida      

SELECT ft.name
  FROM food_table ft
/

NAME             
---------------- 
Mutter Paneer    
Cantaloupe       

Notice:

• You use the TABLE OF syntax instead of specifying columns.
• Each attribute in the object type is immediately and directly accessible as columns in each row.

Relational Tables with Object Type Columns

You can also define a relational table that has one or more columns whose datatype is an object type. We like to eat food at meals so let's go with that for our table design.

I will create a type hierarchy of food, desserts and cakes, and then use two of those in the table defintion.

CREATE OR REPLACE TYPE dessert_ot
   UNDER food_ot
   (
      contains_chocolate CHAR (1),
      year_created NUMBER (4)
   )
   NOT FINAL;
/

CREATE OR REPLACE TYPE cake_ot
   UNDER dessert_ot
   (
      diameter NUMBER,
      inscription VARCHAR2 (200)
   );
/

CREATE TABLE meals (
   served_on DATE,
   appetizer food_ot,
   main_course food_ot,
   dessert dessert_ot
   );
/

Now I will insert three rows into the table.

BEGIN
   -- Populate the meal table
   INSERT INTO meals (served_on, appetizer, main_course, dessert)
        VALUES (SYSDATE,
                food_ot ('Shrimp cocktail', 'PROTEIN', 'Ocean'),
                food_ot ('Eggs benedict', 'PROTEIN', 'Farm'),
                dessert_ot ('Strawberries and cream',
                            'FRUIT',
                            'Backyard',
                            'N',
                            2001));

   INSERT INTO meals (served_on, appetizer, main_course, dessert)
        VALUES (SYSDATE + 1,
                food_ot ('House Salad', 'VEGETABLE', 'Farm'),
                food_ot ('Stir fry tofu', 'PROTEIN', 'Vat'),
                cake_ot ('Apple Pie',
                         'FRUIT',
                         'Baker''s Square',
                         'N',
                         2001,
                         8,
                         NULL));

   INSERT INTO meals (served_on, appetizer, main_course, dessert)
        VALUES (SYSDATE + 1,
                food_ot ('Fried Calamari', 'PROTEIN', 'Ocean'),
                dessert_ot ('Butter cookie',
                            'CARBOHYDRATE',
                            'Oven',
                            'N',
                            2001),
                cake_ot ('French Silk Pie',
                         'CARBOHYDRATE',
                         'Baker''s Square',
                         'Y',
                         2001,
                         6,
                         'To My Favorite Frenchman'));

   INSERT INTO meals (served_on, appetizer, main_course, dessert)
        VALUES (SYSDATE + 1,
                NULL,
                cake_ot ('French Silk Pie',
                         'CARBOHYDRATE',
                         'Baker''s Square',
                         'Y',
                         2001,
                         6,
                         'To My Favorite Frenchman'),
                dessert_ot ('Butter cookie',
                            'CARBOHYDRATE',
                            'Oven',
                            'N',
                            2001));
END;
/

Notice that even though the main_course is defined as type food_ot, I can have dessert or cake for the main course! And even though the dessert column is defined with type dessert_ot, my dessert can be a cake.

That works because of type substitutability, which can be summarized as follows: every cake is a dessert, but not every dessert is a cake. So wherever I use a food_ot type, dessert_ot and cake_ot also are accepted.

But if I use a subtype, then an instance of a supertype will not work, as you can see with the following attempt to add a row:

BEGIN
    INSERT INTO meals (served_on, appetizer, main_course, dessert)
        VALUES (SYSDATE,
                food_ot ('Shrimp cocktail', 'PROTEIN', 'Ocean'),
                dessert_ot ('Strawberries and cream',
                            'FRUIT',
                            'Backyard',
                            'N',
                            2001),
                food_ot ('Lollipop', 'SUGAR', 'Factory'));
END;
/

PL/SQL: ORA-00932: inconsistent datatypes: 
   expected ORACLEFREE.FOOD_OT got ORACLEFREE.DESSERT_OT

Now let's query data from the meals table. I can, of course, select non-type column values in the usual way. But if I want to get the value of an attribute from one of the type columns, I must use a table alias.

SELECT served_on FROM meals
/

SERVED_ON
---------
03-OCT-19
04-OCT-19
04-OCT-19
04-OCT-19

/* Try to access an attribue of an object type instance... */

SELECT served_on, NVL (appetizer.name, 'Not that hungry') appetizer
  FROM meals 
/

ORA-00904: "APPETIZER"."NAME": invalid identifier

/* MUST use a table alias! */

SELECT served_on, NVL (m.appetizer.name, 'Not that hungry') appetizer
  FROM meals m
/

SERVED_ON APPETIZER.NAME                                                                                      
--------- ------------------------
03-OCT-19 Shrimp cocktail                                                                                     
04-OCT-19 House Salad                                                                                         
04-OCT-19 Fried Calamari                                                                                      
04-OCT-19 Not that hungry                                                                                                    

So now let's see which of my main courses contain chocolate.

SELECT m.contains_chocolate
  FROM meals m
/

ORA-00904: "M"."CONTAINS_CHOCOLATE": invalid identifier

Huh? Why didn't that work? Because not every instance in the main_course column is of type dessert or cake. Many are of type food_ot, and that does not have a contains_chocolate attribute.

We need to be able to identify which columns contain an instance of the dessert type. For that we will use the TREAT function. With TREAT, you can change the declared type of the expression. I use it below to find all the meals whose main course is actually a dessert.

SELECT m.served_on,
       m.main_course.name
  FROM meals m
 WHERE TREAT (main_course AS dessert_ot) IS NOT NULL
/

SERVED_ON  MAIN_COURSE.NAME
---------  ----------------
04-OCT-19  Butter cookie
04-OCT-19  French Silk Pie

OK, so now let's show which of those silly dessert-as-main-courses contains chocolate.

SELECT main_course.contains_chocolate chocolatey
  FROM meals
 WHERE TREAT (main_course AS dessert_ot) IS NOT NULL
/

ORA-00904: "MAIN_COURSE"."CONTAINS_CHOCOLATE": invalid identifier

Argh! Still not cooperating. Well, that's because I also need to use TREAT in my SELECT clause. This works:

SELECT TREAT (main_course AS dessert_ot).contains_chocolate chocolatey,
       TREAT (main_course AS dessert_ot).year_created
  FROM meals
 WHERE TREAT (main_course AS dessert_ot) IS NOT NULL
/

CHOCOLATEY  YEAR_CREATED
----------  ------------
N           2001
Y           2001

OK, not exactly concise, but it gets the job done.

Here's another use of TREAT: suppose I don't want any desserts in my table that are not really and truly cakes. TREAT(dessert AS cake_ot)will return NULL if the instance in the dessert column is not a cake, so this update will set the columns to NULL for non-cakes and the existing cake instance if a cake.

SELECT m.dessert.name
  FROM meals m
 WHERE TREAT (main_course AS dessert_ot) IS NOT NULL
/

DESSERT.NAME
----------------
French Silk Pie
Butter cookie

UPDATE meals
   SET dessert = TREAT (dessert AS cake_ot)
/

4 rows updated.

SELECT m.dessert.name
  FROM meals m
 WHERE TREAT (main_course AS dessert_ot) IS NOT NULL
/

DESSERT.NAME
----------------
French Silk Pie

All righty. That should be enough to get you started on your exploration of using object types inside relational tables. But wait....one more thing. What if I want to use an ORDER BY clause with an object type column? What if I need to answers questions like "Is this instance greater or less than another instance?"

Let's try it.

SELECT m.main_course.name
  FROM meals m
 ORDER BY main_course
/

ORA-22950: cannot ORDER objects without MAP or ORDER method

Oh my. An ORDER method? A MAP method? What are those?

I will answer those questions, dear reader, in my very next post in this series. In the meantime, have a look at the doc.

Happy coding!

Check Out the Entire Series

Visit this post that gives you quick access to all the articles in the series.

P.S. Don't forget that if you'd like to try out all this code for yourself, all you have to do is run this LiveSQL script.