Using Object Types in Relational Tables

View All Scripts Login to Run Script

Script Name Using Object Types in Relational Tables
Description This script accompanies my blog post of the same title, part of a series on using the object-oriented features of Oracle PL/SQL (object types). The link: https://stevenfeuersteinonplsql.blogspot.com/2019/10/using-object-types-in-relational-tables.html
Area PL/SQL General
Contributor Steven Feuerstein
Created Thursday October 03, 2019

Statement 1
I realize these drop statements are not needed in LiveSQL, but if you download the script, they will come in very handy if and when you run it more than once.
```
DROP TABLE food_table 
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942
Statement 2
```
DROP TABLE meals 
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942
Statement 3
```
DROP TYPE cake_ot 
```
```
ORA-04043: object CAKE_OT does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-04043

Statement 4

DROP TYPE dessert_ot

ORA-04043: object DESSERT_OT does not exist

More Details: https://docs.oracle.com/error-help/db/ora-04043

Statement 5
```
DROP TYPE food_ot 
```
```
ORA-04043: object FOOD_OT does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-04043

Statement 6

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

Type created.

Statement 7

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

Table created.

Statement 8

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;

Statement processed.

Statement 9

SELECT * FROM food_table


NAME FOOD_GROUP GROWN_IN
Mutter Paneer Curry India
Cantaloupe Fruit Backyard

2 rows selected.

NAME	FOOD_GROUP	GROWN_IN
Mutter Paneer	Curry	India
Cantaloupe	Fruit	Backyard

Statement 10

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

Statement processed.

Statement 11

SELECT * FROM food_table


NAME FOOD_GROUP GROWN_IN
Mutter Paneer Curry India
Cantaloupe Fruit Florida

2 rows selected.

NAME	FOOD_GROUP	GROWN_IN
Mutter Paneer	Curry	India
Cantaloupe	Fruit	Florida

Statement 12

SELECT ft.name 
  FROM food_table ft


NAME
Cantaloupe
Mutter Paneer

2 rows selected.

NAME
Cantaloupe
Mutter Paneer

Statement 13

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

Type created.

Statement 14

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

Type created.

Statement 15

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

Table created.

Statement 16

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;

Statement processed.

Statement 17

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;

ORA-06550: line 10, column 17:
PL/SQL: ORA-00932: inconsistent datatypes: expected SQL_LJZKPTSMRCDJSSOVZSCIQIXCQ.FOOD_OT got SQL_LJZKPTSMRCDJSSOVZSCIQIXCQ.DESSERT_OT

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 18

SELECT served_on FROM meals


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

4 rows selected.

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

Statement 19

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

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

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 20

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


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

4 rows selected.

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

Statement 21

SELECT m.contains_chocolate 
  FROM meals m

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

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 22

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

2 rows selected.

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

Statement 23

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

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 24

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 TREAT(MAIN_COURSEASDESSERT_OT).YEAR_CREATED
N 2001
Y 2001

2 rows selected.

CHOCOLATEY	TREAT(MAIN_COURSEASDESSERT_OT).YEAR_CREATED
N	2001
Y	2001

Statement 25

SELECT TREAT (main_course AS dessert_ot) dessert 
  FROM meals 
 WHERE TREAT (main_course AS dessert_ot) IS NOT NULL


DESSERT
[unsupported data type]
[unsupported data type]

2 rows selected.

DESSERT
[unsupported data type]
[unsupported data type]

Statement 26

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


DESSERT.NAME
French Silk Pie
Butter cookie

2 rows selected.

DESSERT.NAME
French Silk Pie
Butter cookie

Statement 27

UPDATE meals 
   SET dessert = TREAT (dessert AS cake_ot)

4 row(s) updated.

Statement 28

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


DESSERT.NAME
French Silk Pie
 - 

2 rows selected.

DESSERT.NAME
French Silk Pie
-

Statement 29

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

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

More Details: https://docs.oracle.com/error-help/db/ora-22950

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database