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.
You may also like: From Relational to Graph: A Developer’s Guide
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
CantaloupeNotice:
- 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 definition.
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_OTNow 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 hungrySo now let's see which of my main courses contain chocolate.
SELECT m.contains_chocolate
FROM meals m
/
ORA-00904: "M"."CONTAINS_CHOCOLATE": invalid identifierHuh? 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 PieOK, 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 identifierArgh! 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 2001OK, 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 PieAll 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?"
SELECT m.main_course.name
FROM meals m
ORDER BY main_course
/
ORA-22950: cannot ORDER objects without MAP or ORDER methodOh 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!
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.
