Using Object Types in Relational Tables, Part 4
Let's explore using object types in relational tables.
Join the DZone community and get the full member experience.Join For Free
So far in my series on object-oriented development in Oracle Database, all manipulation of object type instances have taken place in PL/SQL.
You can find part 1 here, part 2 here, and part 3 here.
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
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
- 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_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?"
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.
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.
- Introduction to Object Types (a.k.a. Classes) Part 1
- Object Types and Inheritance: Part 2
- Object Type Methods, Part 3
- Using Object Types in Relational Tables, Part 4
NoSQL vs Relational Databases: When to Use What
Relational Data Modeling With Foreign Keys in a Distributed SQL Database
Published at DZone with permission of Steven Feuerstein, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Extending Java APIs: Add Missing Features Without the Hassle
Effortlessly Streamlining Test-Driven Development and CI Testing for Kafka Developers
The SPACE Framework for Developer Productivity
Design Patterns for Microservices: Ambassador, Anti-Corruption Layer, and Backends for Frontends