VF=>user  ID=>    Login Feedback FAQ Blog
1,064,669 quizzes played | 2,953 active players

Actions


  On Downcast with TREAT: TREAT (Oracle PL/SQL)      Add to Favorites

Summary

Use TREAT to "downcast" a supertype to a subtype, allowing you to reference attributes of the subtype.

Details

Assumes Oracle Database 10g Release 2 or higher
Advanced Quiz, IDs: 15541/1501553/951798

Reviewer(s): Vitaliy Lyanchevskiy, Sailaja Pasupuleti

The Question

I execute these statements:

CREATE TYPE plch_food_t AS OBJECT
(
   name VARCHAR2 (100),
   food_group VARCHAR2 (100),
   grown_in VARCHAR2 (100)
)
   NOT FINAL;
/

CREATE TYPE plch_dessert_t
   UNDER plch_food_t
   (
      contains_chocolate CHAR (1),
      year_created NUMBER (4)
   )
   NOT FINAL;
/

CREATE TYPE plch_cake_t
   UNDER plch_dessert_t
   (
      diameter NUMBER,
      inscription VARCHAR2 (200)
   );
/

CREATE TABLE meals
(
   served_on     DATE,
   appetizer     plch_food_t,
   main_course   plch_food_t,
   dessert       plch_dessert_t
);

BEGIN
   INSERT INTO meals
        VALUES (SYSDATE + 1,
                plch_food_t ('Shrimp cocktail', 'PROTEIN', 'Ocean'),
                plch_food_t ('Stir fry tofu', 'PROTEIN', 'Vat'),
                plch_cake_t ('Apple Pie',
                        'FRUIT',
                        'Baker''s Square',
                        'N',
                        2001,
                        8,
                        NULL));

   INSERT INTO meals
        VALUES (SYSDATE + 1,
                plch_food_t ('Fried Calamari', 'PROTEIN', 'Ocean'),
                plch_dessert_t ('Butter cookie',
                           'CARBOHYDRATE',
                           'Oven',
                           'N',
                           2001),
                plch_cake_t ('French Silk Pie',
                        'CARBOHYDRATE',
                        'Baker''s Square',
                        'Y',
                        2001,
                        6,
                        'To My Favorite Frenchman'));

   INSERT INTO meals
        VALUES (SYSDATE + 1,
                plch_food_t ('Fried Calamari', 'PROTEIN', 'Ocean'),
                plch_cake_t ('French Silk Pie',
                        'CARBOHYDRATE',
                        'Baker''s Square',
                        'Y',
                        2001,
                        6,
                        'To My Favorite Frenchman'),
                plch_dessert_t ('Butter cookie',
                           'CARBOHYDRATE',
                           'Oven',
                           'N',
                           2001));
END;
/

Which of the choices cause the following two lines of text to be displayed (order in which lines are displayed is not significant) after execute?

Butter cookie-N
French Silk Pie-Y

The Choices [↑]

Explanation of Result Icons
Choice 1 (29538)
BEGIN
   FOR rec IN (SELECT *
                 FROM meals
                WHERE TREAT (main_course AS plch_dessert_t) IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (
         rec.main_course.name || '-' || 
         rec.main_course.contains_chocolate);
   END LOOP;
END;
/

I use TREAT to identify all rows in the table for which the main course is a dessert, but the query still returns instances of type plch_food_t. Therefore, I cannot reference the contains_chocolate attribute in the loop, without further use of TREAT.




65%
Choice 2 (29539)
DECLARE
   l_dessert   plch_dessert_t;
BEGIN
   FOR rec IN (SELECT * FROM meals)
   LOOP
      BEGIN
         l_dessert := TREAT (rec.main_course AS plch_dessert_t);
         DBMS_OUTPUT.put_line (
            l_dessert.name || '-' || l_dessert.contains_chocolate);
      EXCEPTION
         WHEN VALUE_ERROR
         THEN
            NULL;
      END;
   END LOOP;
END;
/

I scan through all meals in the table. If I am able to TREAT a main course as a dessert, then I display the desired information.

If the main_course is not of type dessert, PL/SQL throws:

ORA-06502: PL/SQL: numeric or value error: cannot assign supertype instance to subtype



60%
Choice 3 (29540)
BEGIN
   FOR rec IN (SELECT * FROM meals)
   LOOP
      DBMS_OUTPUT.put_line (
         CASE
            WHEN rec.main_course.contains_chocolate IS NOT NULL
            THEN
                  rec.main_course.name
               || '-'
               || rec.main_course.contains_chocolate
            ELSE
               NULL
         END);
   END LOOP;
END;
/

I cannot reference the contains_chocolate attribute (of the dessert type) without the use of TREAT. The main_course column is of type plch_food_t, and that type does not have the contains_chocolate attribute.




71%
Choice 4 (29541)
BEGIN
   FOR rec IN (SELECT *
                 FROM meals
                WHERE TREAT (main_course AS plch_dessert_t) IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (
         rec.main_course.name || '-' || 
         rec.TREAT (rec.main_course AS plch_dessert_t).contains_chocolate);
   END LOOP;
END;
/

I find all the rows in the table for which the main course is a dessert. I then try to apply TREAT within the expression to access the contains_chocolate attribute, but TREAT cannot be used that way. This choice fails with:

PLS-00122: AS as separator is allowed only with specific built-in functions



50%
Choice 5 (29542)
DECLARE
   l_dessert   plch_dessert_t;
BEGIN
   FOR rec IN (SELECT *
                 FROM meals
                WHERE TREAT (main_course AS plch_dessert_t) IS NOT NULL)
   LOOP
      l_dessert := TREAT (rec.main_course AS plch_dessert_t);
      DBMS_OUTPUT.put_line (
         rec.main_course.name || '-' || 
         l_dessert.contains_chocolate);
   END LOOP;
END;
/

I identify all rows for which the main course is a dessert, using TREAT. Then within the loop, I downcast that main course to a dessert instance. I can then reference the contains_chocolate attribute within the call to DBMS_OUTPUT.PUT_LINE.




71%

Answer [↑]

Oracle PL/SQL supports all the usual sorts of datatypes, such as strings, numbers and dates, and then adds a number of its own more specialized or unique datatypes, including records and collections.


An object type, Oracle's object-oriented feature, serves as a blueprint or template that define both structure and behavior; that is, while a relational table contains data, an object type is a datatype that is used to declare instances, which contain data.

You can use an object type in the same ways that you use standard data types such as NUMBER or VARCHAR2. For example, you can specify an object type as the data type of a column in a relational table, and you can declare variables of an object type. The value is a variable or an instance of that type. An object instance is also called an object.

Object types are database schema objects, subject to the same kinds of administrative control as other schema objects. Application code can retrieve and manipulate these objects..


The TREAT function does a runtime check to confirm that an expression can be operated on as if it were of a different specified type in the hierarchy, normally a subtype of the declared type of the expression. In other words, the function attempts to treat a supertype instance as a subtype instance, for example, to treat a person as a student. If the person is a student, then the person is returned as a student, with the additional attributes and methods that a student may have. If the person is not a student, TREAT returns NULL in SQL.


Statistics and Rankings [↑]

Rank Percentile Score Seconds% Correct
Median
79
50
365
254
60
Best
1
100
735
44
100

Click to View Verification Code [↑]

Quiz Link and Tweet [↑]

Pass along a link the quiz or use our text to tweet about this quiz. Thanks for helping to spread the word!

 
About Oracle | Terms of Use | Your Privacy Rights | Copyright 2010-2017, Oracle Corporation