Oracle TREAT Function: A Simple Guide

The TREAT function in Oracle SQL is an advanced function used to change the declared type of an expression. It is most commonly used in two specific scenarios:

1. Object-Oriented SQL: To "treat" a base object type (like a "person") as one of its specific subtypes (like an "employee") so you can access the subtype's unique attributes (like "salary").
2. JSON: To "treat" a text string as JSON data.

This function is a specialized version of CAST, designed for handling object subtypes and JSON data.

What is the TREAT Function in Oracle?

The TREAT(expr AS type) function checks if the expression expr is of the specified type (or a subtype of it).

• If it is: It returns the expression, but now "treated" as the new type, allowing you to access its specific attributes.
• If it is not: It returns NULL.

This is incredibly useful for querying tables that use object-oriented features, where one column can hold objects of different subtypes.

TREAT Function Syntax

The TREAT function has two main forms:

1. Object-Oriented Syntax:

TREAT(expr AS type)

2. JSON Syntax:

TREAT(expr AS JSON)

Let's break that down:

• expr: The expression or column you want to re-type. This is often an object.
• AS type: The user-defined object type (a supertype or subtype) you want to treat the expression as.
• AS JSON: A clause that tells Oracle to interpret the expr (which is often a VARCHAR2 or CLOB) as JSON data.

Oracle TREAT Function Examples

Here are two practical examples of how to use TREAT.

Example 1: Using TREAT with Object Subtypes

This is the classic object-oriented use case. First, let's imagine we have defined a base type person_t and a subtype employee_t that inherits from person_t but adds a salary attribute.

Object Setup (Conceptual):

-- Base type
CREATE TYPE person_t AS OBJECT (
  name VARCHAR2(30)
) NOT FINAL;

-- Subtype
CREATE TYPE employee_t UNDER person_t (
  salary NUMBER
);

-- Table holding objects of the base type (and any subtype)
CREATE TABLE persons OF person_t;

-- Insert one 'person' and one 'employee'
INSERT INTO persons VALUES (person_t('Bob'));
INSERT INTO persons VALUES (employee_t('Joe', 100000));

The Query: Now, we query the persons table. We can only select the salary attribute if we first TREAT the object as an employee_t.

SELECT 
  p.name,
  TREAT(VALUE(p) AS employee_t).salary AS "Salary"
FROM persons p;

Result:

NAME              Salary
---------------   ----------
Bob               (NULL)
Joe               100000

Notice that TREAT returned NULL for 'Bob', because 'Bob' is only a person_t, not an employee_t.

Example 2: Using TREAT ... AS JSON

This is a simpler, more modern example. By default, Oracle sees the string '{}' as just text. If you want to use it in a JSON function, you can use TREAT to tell Oracle to interpret it as an empty JSON object.

Query:

SELECT 
  TREAT('{}' AS JSON) AS "JSON_Object"
FROM DUAL;

Result: (The result is a JSON data type, not a VARCHAR2 string)

JSON_Object
-----------
{}