Skip to main content

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.

Comments

  1. Hello Steven, All,

    It looks to me that Oracle has a bug when issuing one of the errors shown above:

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

    In my opinion, it should say the opposite:
    expected ORACLEFREE.DESSERT_OT got ORACLEFREE.FOOD_OT

    considering that, naturally, "expected" is based on the declared column type and "got"
    comes from the input type supplied for that column.

    I remember to have also encountered some scenarios of the same error where
    one of the two involved expressions appears as "-" (null) in the error message.

    Probably a little revision is required from the Oracle development team
    regarding this error.

    Cheers & Best Regards,
    Iudith Mentzel

    ReplyDelete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel