Use TREAT to Access Attributes of Object Subtypes
The TREAT function comes in very handy when working with an object type hierarchy, and you need to access attributes or methods of a subtype of a row or column's declared type. This topic was covered in a PL/SQL Challenge quiz offered in March 2016.
Suppose I have the following type hierarchy and I use them as column types in my meals table:
CREATE TYPE food_t AS OBJECT ( name VARCHAR2 (100), food_group VARCHAR2 (100), grown_in VARCHAR2 (100) ) NOT FINAL; / CREATE TYPE dessert_t UNDER food_t ( contains_chocolate CHAR (1), year_created NUMBER (4) ) NOT FINAL; / CREATE TYPE cake_t UNDER dessert_t ( diameter NUMBER, inscription VARCHAR2 (200) ); / CREATE TABLE meals ( served_on DATE, appetizer food_t, main_course food_t, dessert dessert_t );
I then insert some rows into the table:
BEGIN INSERT INTO meals VALUES (SYSDATE + 1, food_t ('Shrimp cocktail', 'PROTEIN', 'Ocean…
Suppose I have the following type hierarchy and I use them as column types in my meals table:
CREATE TYPE food_t AS OBJECT ( name VARCHAR2 (100), food_group VARCHAR2 (100), grown_in VARCHAR2 (100) ) NOT FINAL; / CREATE TYPE dessert_t UNDER food_t ( contains_chocolate CHAR (1), year_created NUMBER (4) ) NOT FINAL; / CREATE TYPE cake_t UNDER dessert_t ( diameter NUMBER, inscription VARCHAR2 (200) ); / CREATE TABLE meals ( served_on DATE, appetizer food_t, main_course food_t, dessert dessert_t );
I then insert some rows into the table:
BEGIN INSERT INTO meals VALUES (SYSDATE + 1, food_t ('Shrimp cocktail', 'PROTEIN', 'Ocean…