Skip to main content

Using Object Types in Relational Tables, Part 4


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

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.
How to Get a Mutating Table ErrorI need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

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 perspective…

Table Functions, Part 1: Introduction and Exploration

Please do feel encouraged to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latter part of this seri…