CAST converts one built-in data type or collection-typed value into another built-in data type or collection-typed value. For example:

CREATE TYPE person_list_typ AS TABLE OF person_typ;/

SELECT CAST(COLLECT(contact) AS person_list_typ) 
  FROM contacts;

A CURSOR expression returns a nested cursor.

The cursor form of expression is equivalent to the PL/SQL REF CURSOR and can be passed as a REF CURSOR argument to a function.

The DEREF function in a SQL statement returns the object instance corresponding to a REF.

The object instance returned by DEREF may be of the declared type of the REF or any of its subtypes.

For example, the following statement returns person_typ objects from the table contact_ref.

-- Using the DEREF Function example, not sample schema

SELECT DEREF(c.contact_ref), c.contact_date 
  FROM contacts_ref c;

The IS OF type predicate tests object instances for the level of specialization of their type.

For example, the following query retrieves all student instances (including any subtypes of students) stored in the person_obj_table table.

-- Using the IS OF type Operator to query Value of a subtype 

SELECT VALUE(p) 
  FROM person_obj_table p
WHERE VALUE(p) IS OF (student_typ);

For any object that is not of a specified subtype, or a subtype of a specified subtype, IS OF returns FALSE. Subtypes of a specified subtype are just more specialized versions of the specified subtype. If you want to exclude such subtypes, you can use the ONLY keyword. This keyword causes IS OF to return FALSE for all types except the specified types.

In the following example, the statement tests objects in object table person_obj_table, which contains persons, employees, and students, and returns REFs just to objects of the two specified person subtypes employee_typ, student_typ, and their subtypes, if any:

-- Using the IS OF type Operator to query for multiple subtypes

SELECT REF(p) 
  FROM person_obj_table p
WHERE VALUE(p) IS OF (employee_typ, student_typ);

Here is a similar example in PL/SQL. The code does something if the person is an employee or student:

-- Using the IS OF type Operator with PL/SQL 

DECLARE 
  var person_typ; 
BEGIN 
  var := employee_typ(55, 'Jane Smith', '1-650-555-0144', 100, 'Jennifer Nelson');
  IF var IS OF (employee_typ, student_typ) THEN 
     DBMS_OUTPUT.PUT_LINE('Var is an employee_typ or student_typ object.');
  ELSE
     DBMS_OUTPUT.PUT_LINE('Var is not an employee_typ or student_typ object.');
  END IF;
END;
/

The following statement returns only students whose most specific or specialized type is student_typ. If the table or view contains any objects of a subtype of student_typ, such as part_time_student_typ, these are excluded. The example uses the TREAT function to convert objects that are students to student_typ from the declared type of the view, person_typ:

-- Using the IS OF type Operator to query for specific subtype only

SELECT TREAT(VALUE(p) AS student_typ)
  FROM person_obj_table p 
WHERE VALUE(p) IS OF(ONLY student_typ);

To test the type of the object that a REF points to, you can use the DEREF function to dereference the REF before testing with the IS OF type predicate.

For example, if contact_ref is declared to be REF person_typ, you can get just the rows for students as follows:

-- Using the IS OF type Operator with DEREF 

SELECT * 
  FROM contacts_ref
WHERE DEREF(contact_ref) IS OF (student_typ);

The REF function in a SQL statement takes as an argument a correlation name (or table alias) for an object table or view and returns a reference (a REF) to an object instance from that table or view.

The REF function may return references to objects of the declared type of the table, view, or any of its subtypes. For example, the following statement returns the references to all persons, including references to students and employees, whose idno attribute is 12:

-- Using the REF Function example, not sample schema

SELECT REF(p) 
  FROM person_obj_table p
  WHERE p.idno = 12;

The SYS_TYPEID function can be used in a query to return the typeid (a hidden type) of the most specific type of the object instance passed as an argument.

The most specific type of an object instance is the type that the instance belongs to, that is, the farthest removed instance from the root type. For example, if Tim is a part-time student, he is also a student and a person, but his most specific type is part-time student.

The function returns the typeids from the hidden type-discriminant column that is associated with every substitutable column. The function returns a null typeid for a final, root type.

The syntax of the function is:

SYS_TYPEID(object_type_value)

Function SYS_TYPEID may be used only with arguments of an object type. Its primary purpose is to make it possible to build an index on a hidden type-discriminant column.

All types that belong to a type hierarchy are assigned a non-null typeid that is unique within the type hierarchy. Types that do not belong to a type hierarchy have a null typeid.

Every type except a final root type belongs to a type hierarchy. A final root type has no types related to it by inheritance:

• It cannot have subtypes derived from it because it is final.

• It is not itself derived from some other type because it is a root type, so it does not have any supertypes.

For an example of SYS_TYPEID, consider the substitutable object table person_obj_table, of person_typ. person_typ is the root type of a hierarchy that has student_typ as a subtype and part_time_student_typ as a subtype of student_typ. See Example 2-20.

The following query uses SYS_TYPEID. It gets the name attribute and typeid of the object instances in the person_obj_table table. Each of the instances is of a different type:

-- Using the SYS_TYPEID Function example, not sample schema

SELECT name, SYS_TYPEID(VALUE(p)) typeid FROM person_obj_table p;

Table functions are functions that produce a collection of rows, a nested table or a varray, that can be queried like a physical database table or assigned to a PL/SQL collection variable.

You can use a table function like the name of a database table, in the FROM clause of a query, or like a column name in the SELECT list of a query.

A table function can take a collection of rows as input. An input collection parameter can be either a collection type, such as a VARRAY or a PL/SQL table, or a REF CURSOR.

Use PIPELINED to instruct Oracle Database to return the results of a table function iteratively. A table function returns a nested table or varray type. You query table functions by using the TABLE keyword before the function name in the FROM clause of the query.

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 TREAT 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.

The two main uses of TREAT are:

• In narrowing assignments, to modify the type of an expression so that the expression can be assigned to a variable of a more specialized type in the hierarchy: that is, to set a supertype value into a subtype.

• To access attributes or methods of a subtype of the declared type of a row or column.

  A substitutable object table or column of type T has a hidden column for every attribute of every subtype of T. These hidden columns contain subtype attribute data, but you cannot list them with a DESCRIBE statement. TREAT enables you to access these columns.

In a SQL statement, the VALUE function takes as its argument a correlation variable (table alias) for an object table or object view and returns object instances corresponding to rows of the table or view.

The VALUE function may return instances of the declared type of the row or any of its subtypes.

Example 2-40 first creates a part_time_student_typ, and then shows a SELECT query returning all persons, including students and employees, from table person_obj_table of person_typ.

-- Requires Ex. 2-31 and 2-32
CREATE TYPE part_time_student_typ UNDER student_typ (
  number_hours NUMBER);
/
SELECT VALUE(p) FROM person_obj_table p;

To retrieve only part time students, that is, instances whose most specific type is part_time_student_typ, use the ONLY keyword to confine the selection:

SELECT VALUE(p) FROM person_obj_table p 
  WHERE VALUE(p) IS OF (ONLY part_time_student_typ);

In the following example, VALUE is used to update a object instance in an object table:

UPDATE person_obj_table p
   SET VALUE(p) = person_typ(12, 'Bob Jones', '1-650-555-0130')
   WHERE p.idno = 12;