TREAT allows you to change the declared type of the expr argument. This function comes in handy when you have a subtype that is more specific to your data and you want to convert the parent type to the more specific one. The optional REF keyword can only be used if the type you are referencing is a REF type.

The following example shows how you could treat a generic type as a specific subtype:

SELECT name, TREAT(VALUE(content) AS legaldocument).lawfirm_name FROM content;

The example assumes that you have created a type content_t, a type legaldocument that inherits from content_t, and a table content of content_t types.

The example selects from the generic table and casts the results to a specific type.

Treat – Oracle SQL Function
Tagged on: