The TREAT function

If I'm dealing with a PL/SQL variable typed as a supertype and it's populated with some subtype value, how can I gain access to the subtype-specific attributes and methods? In my case, I want to treat a generic catalog item as the more narrowly defined book. This operation is called narrowing or downcasting, and is something the compiler can't, or won't, accept. What I need to use is the special Oracle function called TREAT:

DECLARE book book_t; catalog_item catalog_item_t := NEW book_t( );BEGIN book := TREAT (catalog_item AS book_t); /* Requires 9i R2 */END;/

or, in SQL (Oracle9i Release 1 PL/SQL doesn't directly support TREAT):

DECLARE book book_t; catalog_item catalog_item_t := book_t(NULL, NULL, NULL, NULL);BEGIN SELECT TREAT (catalog_item AS book_t) INTO book FROM DUAL;END;

The general syntax of TREAT is:

TREAT (object_instance AS subtype) [ . { attribute | method( args...) } ]

where object_instance is a value of a particular supertype in an object hierarchy, and subtype is the name of a subtype in the same hierarchy. It won't compile if you attempt to treat one type as another from a different type hierarchy. One nifty feature of TREAT is that if you have supplied an object from the correct type hierarchy, it will return either the downcasted object or NULL—but not an error.

As with VALUE, you can use dot notation with TREAT to specify an attribute or method of the TREATed object. For example:

DBMS_OUTPUT.PUT_LINE(TREAT (VALUE(c) AS serial_t).issn);

If I want to iterate over all the objects in the table in a type-aware fashion, I can do something like this:

DECLARE CURSOR ccur IS SELECT VALUE(c) item FROM catalog_items c; arec ccur%ROWTYPE;BEGIN FOR arec IN ccur LOOP CASE WHEN arec.item IS OF (book_t) THEN DBMS_OUTPUT.PUT_LINE('Found a book with ISBN ' || TREAT(arec.item AS book_t).isbn); WHEN arec.item IS OF (serial_t) THEN DBMS_OUTPUT.PUT_LINE('Found a serial with ISSN ' || TREAT(arec.item AS serial_t).issn); ELSE DBMS_OUTPUT.PUT_LINE('Found unknown catalog item'); END CASE; END LOOP;END;

This block introduces the IS OF predicate to test an object's type. Although the syntax is somewhat brazen:

object IS OF ( [ ONLY ] typename )

the IS OF operator is much more limited than one would hope: it works only on object types, not on any of Oracle's core datatypes like NUMBER or DATE. Also, it will return an error if the object is not in the same type hierarchy as typename.

Notice the ONLY keyword. The default behavior—without ONLY—is to return TRUE if the object is of the given type or any of its subtypes. If you use ONLY, the expression won't check the subtypes, and returns TRUE only if the type is an exact match.

The IS OF predicate, like TREAT itself, became available in Oracle9i SQL, although direct support for it in PL/SQL didn't appear until Oracle9i Release 2. As a Release 1 workaround, I could define one or more additional methods in the type tree, taking advantage of dynamic method dispatch to perform the desired operation at the correct level in the hierarchy. The "correct" solution to the narrowing problem depends not just on the version number, though, but also on what my application is supposed to accomplish.

For the moment, I'd like to move on to another interesting area: exploring the features Oracle offers when (not if!) you have to deal with changes in application design.