This chapter provides basic information about working with objects. It explains what object types, methods, and collections are and describes how to create and work with a hierarchy of object types that are derived from a shared root type and are connected by inheritance.

Object-Relational Elements

Object-relational functionality introduces a number of new concepts and resources. These are briefly described in the following sections.

Object Types

An object type is a kind of datatype. You can use it in the same ways that you use more familiar datatypes such as NUMBER or VARCHAR2. For example, you can specify an object type as the datatype of a column in a relational table, and you can declare variables of an object type. You use a variable of an object type to contain a value of that object type. A value of an object type is an instance of that type. An object instance is also called an object.

Object types also have some important differences from the more familiar datatypes that are native to a relational database:

A set of object types does not come ready-made with the database. Instead, you define the object types you want.

Object types are not unitary: they have parts, called attributes and methods.

Attributes hold the data about an object's features of interest. For example, a soldier object type might have the attributes name, rank, and serial number. An attribute has a declared datatype which can in turn be another object type. Taken together, the attributes of an object instance contain that object's data.

Methods are procedures or functions provided to enable applications to perform useful operations on the attributes of the object type. Methods are an optional element of an object type. They define the behavior of objects of that type and determine what (if anything) that type of object can do.

Object types are less generic than native datatypes. In fact, this is one of their major virtues: you can define object types to model the actual structure of the real-world entities--such as customers and purchase orders--that application programs deal with. This can make it easier and more intuitive to manage the data for these entities. In this respect object types are like Java and C++ classes.

You can think of an object type as a structural blueprint or template and an object as an actual thing built according to the template.

You can use object types to model the actual structure of real-world objects. Object types enable you to capture the structural interrelationships of objects and their attributes instead of flattening this structure into a two-dimentional, purely relational schema of tables and columns. With object types you can store related pieces of data in a unit along with the behaviors defined for that data. Application code can then retrieve and manipulate these units as objects.

Type Inheritance

You can specialize an object type by creating subtypes that have some added, differentiating feature, such as an additional attribute or method. You create subtypes by deriving them from a parent object type, which is called a supertype of the derived subtypes.

Subtypes and supertypes are related by inheritance: as specialized versions of their parent, subtypes have all the parent's attributes and methods plus any specializations that are defined in the subtype itself. Subtypes and supertypes connected by inheritance make up a type hierarchy.

Objects

When you create a variable of an object type, you create an instance of the type: the result is an object. An object has the attributes and methods defined for its type. Because an object instance is a concrete thing, you can assign values to its attributes and call its methods.

Methods

Methods are functions or procedures that you can declare in an object type definition to implement behavior that you want objects of that type to perform.

A principal use of methods is to provide access to an object's data. You can define methods for operations that an application is likely to want to perform on the data so that the application does not have to code these operations itself. To perform the operation, an application calls the appropriate method on the appropriate object.

You can also define methods to compare object instances and to perform operations that do not use any particular object's data but instead are global to an object type.

Object Tables

An object table is a special kind of table in which each row represents an object.

For example, the following statements create a person object type and define an object table for person objects:

The first statement inserts a person object into person_table, treating person_table as a multi-column table. The second selects from person_table as a single-column table, using the VALUE function to return rows as object instances.

REF Datatype

A REF is a logical "pointer" to a row object. It is an Oracle built-in datatype. REFs and collections of REFs model associations among objects--particularly many-to-one relationships--thus reducing the need for foreign keys. REFs provide an easy mechanism for navigating between objects. You can use the dot notation to follow the pointers. Oracle does joins for you when needed, and in some cases can avoid doing joins.

You can use a REF to examine or update the object it refers to. You can also use a REF to obtain a copy of the object it refers to. You can change a REF so that it points to a different object of the same object type or assign it a null value.

Scoped REFs

In declaring a column type, collection element, or object type attribute to be a REF, you can constrain it to contain only references to a specified object table. Such a REF is called a scoped REF. Scoped REF types require less storage space and allow more efficient access than unscoped REF types.

The following example shows REF column address_ref scoped to an object table of address_objtyp.

A REF can be scoped to an object table of the declared type (address_objtyp in the example) or of any subtype of the declared type. If scoped to an object table of a subtype, the REF column is effectively constrained to hold references only to instances of the subtype (and its subtypes, if any) in the table.

Dangling REFs

It is possible for the object identified by a REF to become unavailable--through either deletion of the object or a change in privileges. Such a REF is called dangling. Oracle SQL provides a predicate (called IS DANGLING) to allow testing REFs for this condition.

Dereferencing REFs

Accessing the object referred to by a REF is called dereferencing the REF. Oracle provides the DEREF operator to do this.

Dereferencing a dangling REF returns a null object.

Oracle also provides implicit dereferencing of REFs. For example, consider the following:

follows the pointer from the person X to another person, X's manager, and retrieves the manager's name. (Following the REF like this is allowed in SQL, but not in PL/SQL.)

Obtaining REFs

You can obtain a REF to a row object by selecting the object from its object table and applying the REF operator. For example, you can obtain a REF to the purchase order with identification number 1000376 as follows:

Collections

For modeling one-to-many relationships, Oracle supports two collection datatypes: varrays and nested tables. Collection types can be used anywhere other datatypes can be used: you can have object attributes of a collection type, columns of a collection type, and so forth. For example, you might give a purchase order object type a nested table attribute to hold the collection of line items for a given purchase order.

Defining Object and Collection Types

You use the CREATE TYPE statement to define object types and collection types.

The CREATE TYPE statements shown below define the object types person, lineitem, lineitem_table, and purchase_order. lineitem_table is a collection type--a nested table type. The purchase_order object type has an attribute lineitems of this type. Each row in this nested table is an object of type lineitem.

The indented elements name, phone, item_name, and so on in the CREATE TYPE statements are attributes. Each has a datatype declared for it.

This is a simplified example. It does not show how to specify the body of the method get_value, which you do with the CREATE OR REPLACE TYPE BODY statement.

Defining an object type does not allocate any storage.

Once they are defined as types, lineitem, person, and purchase_order can be used in SQL statements in most of the same places you can use types like NUMBER or VARCHAR2.

For example, you might define a relational table to keep track of your contacts:

CREATE TABLE contacts (
contact person
date DATE );

The CONTACTS table is a relational table with an object type as the datatype of one of its columns. Objects that occupy columns of relational tables are called column objects (see "Row Objects and Column Objects").

Null Objects and Attributes

A table column, object, object attribute, collection, or collection element is NULL if it has been initialized to NULL or has not been initialized at all. Usually, a NULL value is replaced by an actual value later on.

An object whose value is NULL is called atomically null. An atomically null object is different from one that simply happens to have null values for all its attributes. When all the attributes of an object are null, these attributes can still be changed, and the object's methods can be called. With an atomically null object, you can do neither of these things.

In both cases, Oracle allocates space in CONTACTS for a new row and sets its DATE column to the value given. But in the first case, Oracle allocates space for an object in the PERSON column and sets each of the object's attributes to NULL. In the second case, Oracle sets the PERSON field itself to NULL and does not allocate space for an object.

In some cases, you can omit checks for null values. A table row or row object cannot be null. A nested table of objects cannot contain an element whose value is NULL.

A nested table or array can be null, so you do need to handle that condition. A null collection is different from an empty one, that is, a collection containing no elements.

Default Values for Objects and Collections

When you declare a table column to be of an object type or collection type, you can include a DEFAULT clause. This provides a value to use in cases where you do not explicitly specify a value for the column. The default clause must contain a literal invocation of the constructor method for that object or collection.

A literal invocation of a constructor method is a call to the constructor method in which any arguments are either literals, or further literal invocations of constructor methods. No variables or functions are allowed.

For example, consider the following statements:

CREATE TYPE person AS OBJECT (
id NUMBER
name VARCHAR2(30),
address VARCHAR2(30) );
CREATE TYPE people AS TABLE OF person;

The following is a literal invocation of the constructor method for the nested table type PEOPLE:

The DEPARTMENT table in the next example has a column whose type is the object type LOCATION defined in the previous example. The example defines constraints on scalar attributes of the LOCATION objects that appear in the DEPT_LOC column of the table.

Rules for REF Columns and Attributes

In Oracle, a REF column or attribute can be unconstrained or constrained using a SCOPE clause or a referential constraint clause. When a REF column is unconstrained, it may store object references to row objects contained in any object table of the corresponding object type.

Oracle does not ensure that the object references stored in such columns point to valid and existing row objects. Therefore, REF columns may contain object references that do not point to any existing row object. Such REF values are referred to as dangling references. Currently, Oracle does not permit storing object references that contain a primary-key based object identifier in unconstrained REF columns.

A REF column may be constrained to be scoped to a specific object table. All the REF values stored in a column with a SCOPE constraint point at row objects of the table specified in the SCOPE clause. The REF values may, however, be dangling.

A REF column may be constrained with a REFERENTIAL constraint similar to the specification for foreign keys. The rules for referential constraints apply to such columns. That is, the object reference stored in these columns must point to a valid and existing row object in the specified object table.

v or PRIMARY KEY constraints cannot be specified for REF columns. However, you can specify NOT NULL constraints for such columns.

Name Resolution

Oracle SQL lets you omit qualifying table names in some relational operations. For example, if ASSIGNMENT is a column in PROJECTS and TASK is a column in DEPTS, you can write:

In some cases, object-relational features require you to specify the table aliases.

When Table Aliases are Required

Using unqualified names can lead to problems. If you add an ASSIGNMENT column to DEPTS and forget to change the query given above, Oracle automatically recompiles the query such that the inner SELECT uses the ASSIGNMENT column from the DEPTS table. This situation is called inner capture.

To avoid inner capture and similar problems resolving references, Oracle requires you to use a table alias to qualify any dot-notational reference to methods or attributes of objects. Use of a table alias is optional when referencing top-level attributes of an object table directly, without using the dot notation.

For example, the following statements define an object type PERSON and two tables. ptab1 is an object table for objects of type PERSON, and ptab2 is a relational table that contains a column of an object type.

In the first SELECT statement, ssno is the name of a column of ptab1. It references this top-level attribute directly, without using the dot notation, so no table alias is required.

In the second SELECT statement, ssno is the name of an attribute of the PERSON object in the column named c1. This reference uses the dot notation and so requires a table alias, as shown in the fourth SELECT statement.

The third SELECT uses the table name itself to qualify this the reference. This is incorrect; a table alias is required.

You must qualify a reference to an object attribute or method with a table alias rather than a table name even if the table name is itself qualified by a schema name.

For example, the following expression tries to refer to the scott schema, projects table, assignment column, and duedate attribute of that column. But the expression is incorrect because projects is a table name, not an alias.

scott.projects.assignment.duedate

The same requirement applies to attribute references that use REFs.

Table aliases should uniquely pick out the same table throughout a query and should not be the same as schema names that could legally appear in the query.

Note:

Oracle recommends that you define table aliases in all UPDATE, DELETE, and SELECT statements and subqueries and use them to qualify column references whether or not the columns contain object types.

Restriction on Using User-Defined Types with a Remote Database

User-defined types (specifically, types declared with a SQL CREATE TYPE statement, as opposed to types declared within a PL/SQL package) are currently useful only within a single database. You cannot use a database link to do any of the following:

Connect to a remote database to query, insert, or update a user-defined type or an object REF on a remote table

Use database links within PL/SQL code to declare a local variable of a remote user-defined type

Methods

Methods are functions or procedures that you can declare in an object type definition to implement behavior that you want objects of that type to perform. An application calls the methods to invoke the behavior.

For example, you might declare a method get_sum() to get a purchase order object to return the total cost of its line items. The following line of code calls such a method for purchase order po and returns the amount into sum_line_items:

sum_line_items = po.get_sum();

The parentheses are required. Unlike with PL/SQL functions and procedures, Oracle requires parentheses with all method calls, even ones that do not have arguments.

Methods can be written in PL/SQL or virtually any other programming language. Methods written in PL/SQL or Java are stored in the database. Methods written in other languages, such as C, are stored externally.

Two general kinds of methods can be declared in a type definition:

Member

Static

There is also a third kind of method, called a constructor method, that the system defines for every object type. You call a type's constructor method to construct or create an object instance of the type.

Member Methods

Member methods are the means by which an application gains access to an object instance's data. You define a member method in the object type for each operation that you want an object of that type to be able to perform. For example, the method get_sum() mentioned above that sums the total cost of a purchase order's line items operates on the data of a particular purchase order and is a member method.

Member methods have a built-in parameter named SELF that denotes the object instance on which the method is currently being invoked. Member methods can reference the attributes and methods of SELF without a qualifier. This makes it simpler to write member methods. For example, the following code shows a method declaration that takes advantage of SELF to omit qualification of the attributes num and den:

SELF does not need to be explicitly declared, although it can be. It is always the first parameter passed to the method. In member functions, if SELF is not declared, its parameter mode defaults to IN. In member procedures, if SELF is not declared, its parameter mode defaults to IN OUT.

You invoke a member method using the "dot" notation object_variable.method(). The notation specifies first the object on which to invoke the method and then the method to call. Any parameters occur inside the parentheses, which are required.

Methods for Comparing Objects

The values of a scalar datatype such as CHAR or REAL have a predefined order, which allows them to be compared. But an object type, such as a customer_typ, which can have multiple attributes of various datatypes, has no predefined axis of comparison. To be able to compare and order variables of an object type, you must specify a basis for comparing them.

Two special kinds of member methods can be defined for doing this: map methods and order methods.

Map Methods

A map method is an optional kind of method that provides a basis for comparing objects by mapping object instances to one of the scalar types DATE, NUMBER, VARCHAR2 or to an ANSI SQL type such as CHARACTER or REAL. With a map method, you can order any number of objects by calling each object's map method once to map that object to a position on the axis used for the comparison (a number or date, for example).

From the standpoint of writing one, a map method is simply a parameterless member function that uses the MAP keyword and returns one of the datatypes just listed. What makes a map method special is that, if an object type defines one, the method is called automatically to evaluate such comparisons as obj_1 > obj_2 and comparisons implied by the DISTINCT, GROUP BY, and ORDER BY clauses. Where obj_1 and obj_2 are two object variables that can be compared using a map method map(), the comparison:

obj_1 > obj_2

is equivalent to:

obj_1.map() > obj_2.map()

And similarly for other relational operators besides ">".

The following example defines a map method area() that provides a basis for comparing rectangle objects by their area:

An object type can declare at most one map method (or one order method). A subtype can declare a map method only if its root supertype declares one.

Order Methods

Order methods make direct object-to-object comparisons. Unlike map methods, they cannot map any number of objects to an external axis. They simply tell you that the current object is less than, equal to, or greater than the other object that it is being compared to, with respect to the criterion used by the method.

An order method is a function with one declared parameter for another object of the same type. The method must be written to return either a negative number, zero, or a positive number. The return signifies that the object picked out by the SELF parameter is respectively less than, equal to, or greater than the other parameter's object.

As with map methods, an order method, if one is defined, is called automatically whenever two objects of that type need to be compared.

Order methods are useful where comparison semantics may be too complex to use a map method. For example, to compare binary objects such as images, you might create an order method to compare the images by their brightness or number of pixels.

An object type can declare at most one order method (or one map method). Only a type that is not derived from another type can declare an order method: a subtype cannot define one.

The following example shows an order method that compares customers by customer ID:

Guidelines

A map method maps object values into scalar values and can order multiple values by their position on the scalar axis. An order method directly compares values for two particular objects.

You can declare a map method or an order method but not both. If you declare a method of either type, you can compare objects in SQL and procedural statements. However, if you declare neither method, you can compare objects only in SQL statements and only for equality or inequality. (Two objects of the same type count as equal only if the values of their corresponding attributes are equal.)

When sorting or merging a large number of objects, use a map method. One call maps all the objects into scalars, then sorts the scalars. An order method is less efficient because it must be called repeatedly (it can compare only two objects at a time).

Comparison Methods in Type Hierarchies

In a type hierarchy, where definitions of specialized types are derived from definitions of more general types, only the root type--the most basic type, from which all other types are derived--can define an order method. If the root type does not define one, its subtypes cannot define one either.

If the root type specifies a map method, any of its subtypes can define a map method that overrides the map method of the root type. But if the root type does not specify a map method, no subtype can specify one either.

So if the root type does not specify either a map or an order method, none of the subtypes can specify either a map or order method.

Static Methods

Static methods are invoked on the object type, not its instances. You use a static method for operations that are global to the type and do not need to reference the data of a particular object instance. A static method has no SELF parameter.

You invoke a static method by using the "dot" notation to qualify the method call with the name of the object type: type_name.method().

Constructor Methods

Every object type has an implicit, system-defined constructor method, that is, a method that makes a new object and sets up the values of its attributes. The constructor method is a function; it returns the new object as its value. The name of the constructor method is just the name of the object type. Its parameters have the names and types of the object type's attributes.

The following example creates a new object instance of Customer_typ, specifies values for its attributes, and sets the object into a variable:

cust = Customer_typ(103, "Ravi", "1-800-555-1212")

The INSERT statement in the next example inserts a customer object that has an attribute of Address_typ object type. The constructor method Address_typ constructs an object of this type having the attribute values shown in the parentheses:

Collections

Oracle supports two collection datatypes: varrays and nested tables.

A varray is an ordered collection of elements: the position of each element has an index number, and you use this number to access particular elements. When you define a varray, you specify the maximum number of elements it can contain, although you can change this number later. Varrays are stored as opaque objects (that is, RAW or BLOB).

A nested table can have any number of elements: no maximum is specified in the definition of the table; also, the order of the elements is not preserved. You select, insert, delete, and so on, in a nested table just as you do with ordinary tables. Elements of a nested table are actually stored in a separate storage table that contains a column that identifies the parent table row or object to which each element belongs.

If you need to store only a fixed number of items, or to loop through the elements in order, or you will often want to retrieve and manipulate the entire collection as a value, then use a varray.

If you need to run efficient queries on a collection, handle arbitrary numbers of elements, or do mass insert/update/delete operations, then use a nested table.

Varrays

An array is an ordered set of data elements. All elements of a given array are of the same datatype. Each element has an index, which is a number corresponding to the element's position in the array.

The number of elements in an array is the size of the array. Oracle allows arrays to be of variable size, which is why they are called varrays. You must specify a maximum size when you declare the array type.

For example, the following statement declares an array type:

CREATE TYPE prices AS VARRAY(10) OF NUMBER(12,2);

The VARRAYs of type PRICES have no more than ten elements, each of datatype NUMBER(12,2).

Creating an array type does not allocate space. It defines a datatype, which you can use as:

The datatype of a column of a relational table.

An object type attribute.

The type of a PL/SQL variable, parameter, or function return value.

A varray is normally stored in line, that is, in the same tablespace as the other data in its row. If it is sufficiently large, Oracle stores it as a BLOB.

A varray cannot contain LOBs. This means that a varray also cannot contain elements of a user-defined type that has a LOB attribute.

See Also:

"Storage Considerations for Varrays".

Nested Tables

A nested table is an unordered set of data elements, all of the same datatype. It has a single column, and the type of that column is a built-in type or an object type. If the column in a nested table is an object type, the table can also be viewed as a multi-column table, with a column for each attribute of the object type.

For example, in the purchase order example, the following statement declares the table type used for the nested tables of line items:

CREATE TYPE lineitem_table AS TABLE OF lineitem;

A table type definition does not allocate space. It defines a type, which you can use as

The datatype of a column of a relational table.

An object type attribute.

A PL/SQL variable, parameter, or function return type.

When a column in a relational table is of nested table type, Oracle stores the nested table data for all rows of the relational table in the same storage table. Similarly, with an object table of a type that has a nested table attribute, Oracle stores nested table data for all object instances in a single storage table associated with the object table.

For example, the following statement defines an object table for the object type PURCHASE_ORDER:

Nested table or varray of a user-defined type that has an attribute that is a nested table or varray type

Like ordinary, single-level collection types, multi-level collection types can be used with columns in a relational table or with object attributes in an object table.

The following example creates a multi-level collection type that is a nested table of nested tables. The example models a system of stars in which each star has a nested table collection of the planets revolving around it, and each planet has a nested table collection of its satellites.

Nested Table Storage Tables

A nested table type column or object table attribute requires a storage table where rows for all nested tables in the column are stored. Similarly with a multi-level nested table collection of nested tables: the inner set of nested tables requires a storage table just as the outer set does. You specify one by appending a second nested-table storage clause.

For example, the following code creates a table stars that contains a column planets whose type is a multi-level collection (a nested table of an object type that has a nested table attribute satellites). Separate nested table clauses are provided for the outer planets nested table and for the inner satellites one.

The example above can refer to the inner satellite nested table by name because this nested table is a named attribute of an object. However, if the inner nested table is not an attribute, it has no name. The keyword COLUMN_VALUE is provided for this case: you use it in place of a name for an inner nested table. For example:

Every nested table storage table contains a column, referenceable by NESTED_TABLE_ID, that keys rows in the storage table to the associated row in the parent table. A parent table that is itself a nested table has two system-supplied ID columns: one, referenceable by NESTED_TABLE_ID, that keys its rows back to rows in its own parent table, and one hidden column referenced by the NESTED_TABLE_ID column in its nested table children.

In the example above, nested table planets is made an IOT (index-organized table) by adding the ORGANIZATION INDEX clause and assigning the nested table a primary key in which the first column is NESTED_TABLE_ID. This column contains the ID of the row in the parent table with which a storage table row is associated. Specifying a primary key with NESTED_TABLE_ID as the first column and index-organizing the table cause Oracle to physically cluster all the nested table rows that belong to the same parent row, for more efficient access.

Each nested table needs its own table storage clause, so you must have as many nested table storage clauses as you have levels of nested tables in a collection.

Varray Storage

Multi-level varrays are stored in one of two ways, depending on whether the varray is a varray of varrays or a varray of nested tables.

In a varray of varrays, the entire varray is stored inline (that is, in the row itself) unless it is too large (about 4000 bytes) or LOB storage is explicitly specified.

In a varray of nested tables, the entire varray is stored in a LOB, with only the LOB locator stored in the row. There is no storage table associated with nested table elements of a varray. The entire nested table collection is stored inside the varray.

You can explicitly specify LOB storage for varrays. The following example does this for the varray elements of a nested table. As the example also shows, you can use the COLUMN_VALUE keyword with varrays as well as nested tables.

Creating a VARRAY or Nested Table

You create an instance of a collection type in the same way that you create an instance of any other object type, namely, by calling the type's constructor method. The name of a type's constructor method is simply the name of the type. You specify the elements of the collection as a comma-separated list of arguments to the method.

Calling a constructor method with an empty list creates an empty collection of that type. Note that an empty collection is an actual collection that happens to be empty; it is not the same as a null collection.

Constructors for Multi-Level Collections

Like single-level collection types, multi-level collection types are created by calling the respective type's constructor method. Like the constructor methods for other user-defined types, a constructor for a multi-level collection type is a system-defined function that has the same name as the type and returns a new instance of it--in this case, a new multi-level collection. Constructor parameters have the names and types of the object type's attributes.

The example below calls the constructor for the multi-level collection type nt_pl_t. This type is a nested table of planets, each of which contains a nested table of satellites as an attribute. The constructor for the outer nested table calls the planet_t constructor for each planet to be created; each planet constructor calls the constructor for the satellites nested table type to create its nested table of satellites; and the satellites nested table type constructor calls the satellite_t constructor for each satellite instance to be created.

Querying Collections

There are two general ways to query a table that contains a column or attribute of a collection type. One way returns the collections nested in the result rows that contain them. The other way distributes or unnests collections such that each collection element appears on a row by itself.

Nesting Results of Collection Queries

In the following query, column projects is a nested table collection of projects_list_nt type. The projects collection column appears in the SELECT list like an ordinary, scalar column. Querying a collection column in the SELECT list like this nests the elements of the collection in the result row with which the collection is associated.

For example, the following query gets the name of each employee and the collection of projects for that employee. The collection of projects is nested:

Results are also nested if an object-type column in the SELECT list contains a collection attribute, even if that collection is not explicitly listed in the SELECT list itself. For example, the query SELECT * FROM employees would produce a nested result.

Unnesting Results of Collection Queries

Not all tools or applications are able to deal with results in a nested format. To view Oracle collection data using tools that require a conventional format, you must unnest, or flatten, the collection attribute of a row into one or more relational rows. You can do this by using a TABLE expression with the collection. A TABLE expression enables you to query a collection in the FROM clause like a table. In effect, you join the nested table with the row that contains the nested table.

The TABLE expression can be used to query any collection value expression, including transient values such as variables and parameters.

Note:

The TABLE expression takes the place of THEsubquery expression. THEsubquery will eventually be deprecated.

Like the preceding example, the following query gets the name of each employee and the collection of projects for that employee, but the collection is unnested:

The collection column in the TABLE expression uses a table alias to identify the containing table. In the following example, the containing table is listed in the FROM clause solely to introduce a table alias for use by the collection:

The preceding examples produce rows only for employees who have projects. To get rows for employees with no projects, you can use outer-join syntax:

SELECT e.*, p.*
FROM employees e, TABLE(e.projects)(+) p;

The (+) indicates that the dependentjoin between employees and e.projects should be NULL-augmented. That is, there will be rows of employees in the output for which e.projects is NULL or empty, with NULL values for columns corresponding to e.projects.

Unnesting Queries Containing Table Expression Subqueries

The preceding examples show a TABLE expression that contains the name of a collection. Alternatively, a TABLE expression can contain a subquery of a collection.

The following example returns the collection of projects for the employee whose id is 100.

There are these restrictions on using a subquery in a TABLE expression:

The subquery must return a collection type

The SELECT list of the subquery must contain exactly one item

The subquery must return only a single collection: that is, it cannot return collections for multiple rows. For example, the subquery SELECT projects FROM employees succeeds in a TABLE expression only if table employees contains just a single row. If the table contains more than one row, the subquery produces an error.

Here is an example showing a TABLE expression used in the FROM clause of a SELECT embedded in a CURSOR expression:

Unnesting Queries with Multi-Level Collections

Unnesting queries can be used with multi-level collections, too, for both varrays and nested tables. The following example shows an unnesting query on a multi-level nested table collection of nested tables. From a table stars in which each star has a nested table of planets and each planet has a nested table of satellites, the query returns the names of all satellites from the inner set of nested tables.

Performing DML on Multi-Level Collections

For multi-level nested table collections, DML can be done atomically, on the collection as a whole, or piecewise, on selected elements. For multi-level varray collections, DML operations can be done only atomically.

Collections as Atomic Data Items

The section "Constructors for Multi-Level Collections" above shows an example of inserting an entire multi-level collection with an INSERT statement. Multi-level collections can also be updated atomically with an UPDATE statement. For example, suppose v_planets is a variable declared to be of the planets nested table type nt_pl_t. The following statement updates stars by setting the planets collection as a unit to the value of v_planets.

The next example performs a piecewise insert into an inner nested table to add a satellite for a planet. Like the preceding, this example uses a TABLE expression containing a subquery that selects the inner nested table to specify the target for the insert.

Type Inheritance

Object types enable you to model the real-world entities such as customers and purchase orders that your application works with. But this is just the first step in exploiting the capabilities of objects. With objects, you can not only model an entity such as a customer, you can also define different specialized types of customers in a type hierarchy under the original type. You can then perform operations on a hierarchy and have each type implement and execute the operation in a special way.

A type hierarchy is a sort of family tree of object types. It consists of a parent base type, called a supertype, and one or more levels of child object types, called subtypes, derived from the parent.

Subtypes in a hierarchy are connected to their supertypes by inheritance. This means that subtypes automatically acquire the attributes and methods of their parent type. It also means that subtypes automatically acquire any changes made to these attributes or methods in the parent: any attributes or methods updated in a supertype are updated in subtypes as well.

A subtype becomes a specialized version of the parent type by adding new attributes and methods to the set inherited from the parent or by redefining methods it inherits. Redefining an inherited methods gives a subtype its own way of executing the method. Add to this that an object instance of a subtype can generally be substituted for an object instance of any of its supertypes in code, and you have polymorphism.

Polymorphism is the ability of a slot for a value in code to contain a value of either a certain declared type or any of a range of the declared type's subtypes. A method called on whatever value occupies the slot may execute differently depending on the value's type because the various types might implement the method differently.

Types and Subtypes

A subtype can be derived from a supertype either directly, or indirectly through intervening levels of other subtypes.

A subtype can directly derive only from a single supertype: it cannot derive jointly from more than one. A supertype can have multiple sibling subtypes, but a subtype can have at most one direct parent supertype. In other words, Oracle supports only single inheritance, not multiple inheritance.

A subtype is derived from a supertype by defining a specialized variant of the supertype. For example, from a customer object type you might derive the specialized types govt_customer and corp_customer. Each of these subtypes is still at bottom a customer, but a special kind of customer. What makes a subtype special and distinguishes it from its parent supertype is some change made in the subtype to the attributes or methods that the subtype received from its parent.

An object type's attributes and methods make the type what it is: they are its essential, defining features. If a customer object type has the three attributes customer_id, name, and address and the method get_id(), then any object type that is derived from customer will have these same three attributes and a method get_id(). A subtype is a special case of its parent type, not a totally different kind of thing. As such, it shares with its parent type the features that make the general type what it is.

You can specialize the attributes or methods of a subtype in these ways:

Add new attributes that its parent supertype does not have.

For example, you might specialize corp_customer as a special kind of customer by adding to its definition an attribute for account_mgr_id. A subtype cannot drop or change the type of an attribute it inherited from its parent; it can only add new attributes.

Add entirely new methods that the parent does not have.

Change the implementation of some of the methods a subtype inherits from its parent so that the subtype's version executes different code from the parent's.

For example, a shape object type might define a method calculate_area(). Two subtypes of shape, rectilinear_shape and circular_shape, might each implement this method in a different way.

Attributes and methods that a subtype gets from its parent type are said to be inherited. This means more than just that the attributes and methods are patterned on the parent's when the subtype is defined. With object types, the inheritance link remains live. Any changes made later on to the parent type's attributes or methods are also inherited so that the changes are reflected in the subtype as well. Unless a subtype reimplements an inherited method, it always contains the same core set of attributes and methods that are in the parent type, plus any attributes and methods that it adds.

Remember, a child type is not a different type from its parent: it's a particular kind of that type. If the general definition of customer ever changes, the definition of corp_customer changes too.

The live inheritance relationship that holds between a supertype and its subtypes is the source of both much of the power of objects and much of their complexity. It is a very powerful feature to be able to change a method in a supertype and have the change take effect in all the subtypes downstream just by recompiling. But this same capability means that you have to think about such things as whether you want to allow a type to be specialized or a method to be redefined. Similarly, it is a powerful feature for a table or column to be able to contain any type in a hierarchy, but then you must decide whether to allow this in a particular case, and you may need to constrain DML statements and queries so that they pick out from the type hierarchy just the range of types that you want. The following sections address these aspects of working with objects.

FINAL and NOT FINAL Types and Methods

An object type's definition determines whether subtypes can be derived from that type. To permit subtypes, the object type must be defined as not final. This is done by including the NOT FINAL keyword in its type declaration. For example:

The statement above declares Person_typ to be a not final type such that subtypes of Person_typ can be defined. By default, an object type is final--that is, subtypes cannot be derived from it.

You can change a final type to a not final type and vice versa with an ALTER TYPE statement. For example, the following statement changes Person_typ to a final type:

ALTER TYPE Person_typ FINAL;

You can alter a type from NOT FINAL to FINAL only if the target type has no subtypes.

Methods, too, can be declared to be final or not final. If a method is declared to be final, subtypes cannot override it by providing their own implementation. Unlike types, methods are not final by default and must be explicitly declared to be final.

The following statement creates a not final type containing a final member function:

The statement above creates Student_typ as a subtype of Person_typ. As a subtype of Person_typ, Student_typ inherits all the attributes declared in or inherited by Person_typ and any methods inherited by Person_typ or declared in Person_typ.

The statement that defines Student_typ specializes Person_typ by adding two new attributes. New attributes declared in a subtype must have names that are different from the names of any attributes or methods declared in any of its supertypes, higher up in its type hierarchy.

A type can have multiple child subtypes, and these can also have subtypes. The statement below creates another subtype Employee_typ under Person_typ.

A subtype can be defined under another subtype. Again, the new subtype inherits all the attributes and methods that its parent type has, both declared and inherited. For example, the statement below defines a new subtype PartTimeStudent_typ under Student_typ. The new subtype inherits all the attributes and methods of Student_typ and adds another attribute.

CREATE TYPE PartTimeStudent_typ UNDER Student_typ
( numhours NUMBER);

NOT INSTANTIABLE Types and Methods

A type can be declared to be NOT INSTANTIABLE. If a type is not instantiable, there is no constructor (default or user-defined) for it, and you cannot instantiate instances of that type (objects, in other words). You might use this option with types that you intend to use solely as supertypes of specialized subtypes that you do instantiate. For example:

A method can also be declared to be not instantiable. Use this option when you want to declare a method in a type without implementing the method there. A type that contains a non-instantiable method must itself be declared not instantiable. For example:

CREATE TYPE T AS OBJECT (
x NUMBER,
NOT INSTANTIABLE MEMBER FUNCTION func1() RETURN NUMBER
) NOT INSTANTIABLE NOT FINAL;

A non-instantiable method serves as a placeholder. You might define a non-instantiable method when you expect every subtype to override the method in a different way. In such a case, there is no point in defining the method in the supertype.

If a subtype does not provide an implementation for every inherited non-instantiable method, the subtype itself, like the supertype, must be declared not instantiable.

A non-instantiable subtype can be defined under an instantiable supertype.

You can alter an instantiable type to a non-instantiable type and vice versa with an ALTER TYPE statement. For example, the following statement makes Example_typ instantiable:

ALTER TYPE Example_typ INSTANTIABLE;

You can alter an instantiable type to a non-instantiable type only if the type has no columns, views, tables, or instances that reference that type, either directly, or indirectly through another type or subtype.

You cannot declare a non-instantiable type to be FINAL (which would be pointless anyway).

Inheriting, Overloading, and Overriding Methods

A subtype automatically inherits all methods (both member and static methods) declared in or inherited by its supertype.

A subtype can redefine methods it inherits, and it can also add new methods. It can even add new methods that have the same names as methods it inherits, such that the subtype ends up containing more than one method with the same name.

Giving a type multiple methods with the same name is called method overloading. Redefining an inherited method to customize its behavior for a subtype is called method overriding.

Overloading Methods

Overloading is useful when you want to provide a variety of ways of doing something. For example, a shape object might overload a draw() method with another draw() method that adds a text label to the drawing and contains an argument for the label's text.

When a type has several methods with the same name, the compiler uses the methods' signatures to tell them apart. A method's signature is a sort of structural profile. It consists of the method's name and the number, types, and order of the method's formal parameters (including the implicit self parameter). Methods that have the same name but different signatures are called overloads (when they exist in the same type).

Subtype MySubType_typ in the following example creates an overload of foo():

MySubType_typ contains two versions of foo( ): one inherited version, with a NUMBER parameter, and a new version with a DATE parameter.

Overriding Methods

Overriding redefines an inherited method to make it do something different in the subtype. For example, a subtype circular_shape derived from a shape supertype might override a method calculate_area() to customize it specifically for calculating the area of a circle.

When a subtype overrides a method, the new version is executed instead of the overridden one whenever an instance of the subtype invokes the method. If the subtype itself has subtypes, these inherit the override of the method instead of the original version.

It's possible that a supertype may contain overloads of a method that is overridden in a subtype. Overloads of a method all have the same name, so the compiler uses the signature of the subtype's overriding method to identify the version in the supertype to override. This means that, to override a method, you must preserve its signature.

In the type definition, precede a method declaration with the OVERRIDING keyword to signal that you are overriding the method. For example, in the following code, the subtype signals that it is overriding method Print():

As with new methods, you supply the declaration for an overridden method in a CREATE TYPE BODY statement.

Restrictions on Overriding Methods

You can override only methods that are not declared to be final in the supertype.

Order methods may appear only in the root type of a type hierarchy: they may not be redefined (overridden) in subtypes.

A static method in a subtype may not redefine a member method in the supertype.

A member method in a subtype may not redefine a static method in the supertype.

If a method being overridden provides default values for any parameters, then the overriding method must provide the same default values for the same parameters.

Dynamic Method Dispatch

As a result of method overriding, a type hierarchy can define multiple implementations of the same method. For example, in a hierarchy of the types ellipse_typ, circle_typ, sphere_typ, each type might define a method calculate_area() differently.

When such a method is invoked, the type of the object instance that invokes it is used to determine which implementation of the method to use. The call is then dispatched to that implementation for execution. This process of selecting a method implementation is called "virtual" or "dynamic method dispatch" because it is done at run time, not at compile time.

A method call is dispatched to the nearest implementation, working back up the inheritance hierarchy from the current or specified type. If the call invokes a member method of an object instance, the type of that instance is the current type, and the implementation defined or inherited by that type is used. If the call invokes a static method of a type, the implementation defined or inherited by that specified type is used.

For example, if c1 is an object instance of circle_typ, c1.foo() looks first for an implementation of foo() defined in circle_typ. If none is found, it looks up the supertype chain for an implementation in ellipse_typ. The fact that sphere_typ also defines an implementation is irrelevant because the type hierarchy is searched only upwards, toward the top. Subtypes of the current type are not searched.

Similarly, a call to a static method circle_typ.bar() looks first in circle_typ and then, if necessary, in the supertype(s) of circle_typ. The subtype sphere_typ is not searched.

Substituting Types in a Type Hierarchy

In a type hierarchy, the subtypes are variant kinds of the root, base type. For example, a Student_typ type and an Employee_typ are kinds of a Person_typ. The base type includes these other types.

When you work with types in a type hierarchy, sometimes you want to work at the most general level and, for example, select or update all persons. But sometimes you want to select or update only students, or only persons who are not students.

The (polymorphic) ability to select all persons and get back not only objects whose declared type is Person_typ but also objects whose declared (sub)type is Student_typ or Employee_typ is called substitutability. A supertype is substitutable if one of its subtypes can substitute or stand in for it in a slot (a variable, column, etc.) whose declared type is the supertype.

In general, types are substitutable. This is what you would expect, given that a subtype is, after all, just a specialized kind of any of its supertypes. Formally, though, a subtype is a type in its own right: it is not the same type as its supertype. A column that holds all persons, including all persons who are students and all persons who are employees, actually holds data of multiple types.

Substitutability comes into play in attributes, columns, and rows (namely, of an object view or object table) declared to be an object type, a REF to an object type, or a collection type.

In principle, object attributes, collection elements and REFs are always substitutable: there is no syntax at the level of the type definition to constrain their substitutability to some subtype. You can, however, turn off or constrain substitutability at the storage level, for specific tables and columns.

Attributes in general can be accessed using the dot notation. Attributes of a subtype of a row or column's declared type can be accessed with the TREAT function. For example, in an object view Books_v of Book_typ, you can use TREAT to get the employee id of authors of Employee_typ. (The author column is of Person_typ.)

Column and Row Substitutability

Object type columns are substitutable, and so are object-type rows in object tables and views. In other words, a column or row defined to be of type T can contain instances of T and any of its subtypes.

For example, here again is the Person_typ type hierarchy introduced earlier:

Similarly, in a relational table or view, a substitutable column of type Person_typ can contain instances of all three types. The following example inserts a person, a student, and a part-time student in the Person_typ column author:

A newly created subtype can be stored in any substitutable tables and columns of its supertype, including tables and columns that existed before the subtype was created.

Subtypes Having Supertype Attributes

A subtype can have an attribute that is a supertype. For example:

CREATE TYPE Student_typ UNDER Person_typ (..., advisor Person_typ);

However, columns of such types are not substitutable. Similarly, a subtype ST can have a collection attribute whose element type is one of ST's supertypes, but, again, columns of such types are not substitutable. For example, if Student_typ had a nested table or varray of Person_typ, the Student_typ column would not be substitutable.

You can, however, define substitutable columns of subtypes that have REF attributes that reference supertypes.

REF Columns and Attributes

REF columns and attributes are substitutable in both views and tables. For example, in either a view or a table, a column declared to be REF Person_typ can hold references to instances of Person_typ or any of its subtypes.

Collection Elements

Collection elements are substitutable in both views and tables. For example, a nested table of Person_typ can contain object instances of Person_typ or any of its subtypes.

Turning Off Substitutability

You can turn off all substitutability on a column or attribute, including embedded attributes and collections nested to any level, with the clause NOT SUBSTITUTABLE AT ALL LEVELS.

In the following example, the clause confines column book of a relational table to storing only Person_typ instances as authors and disallows any subtype instances:

A column must be a top-level column for the clause NOT SUBSTITUTABLE AT ALL LEVELS to be applied to it: the clause cannot be applied to an object-type attribute.

Constraining Substitutability

You can impose a constraint that limits the range of subtypes permitted in an object column or attribute to a particular subtype in the declared type's hierarchy. You do this using an IS OF type constraint.

For example, the following statement creates a table of Book_typ in which authors are constrained to just those persons who are students:

Although the type Book_typ allows authors to be of type Person_typ, the column declaration imposes a constraint to store only instances of Student_typ.

You can only use the IS OF type operator to constrain row and column objects to a single subtype (not several), and you must use the ONLY keyword, as in the example above.

You can use either IS OF type or NOT SUBSTITUTABLE AT ALL LEVELS to constrain an object column, but you cannot use both.

Assignments Across Types

The assignment rules described in this section apply to INSERT/UPDATE statements, the RETURNING clause, function parameters, and PL/SQL variables.

Objects and REFs to Objects

Substitutability is the ability of a subtype to stand in for one of its supertypes. An attempt to perform a substitution in the other direction--to substitute a supertype for a subtype--raises an error at compile time.

An assignment of a source of type Source_typ to a target of type Target_typ must be of one of the following two patterns:

Case 1: Source_typ and Target_typ are the same type

Case 2: Source_typ is a subtype of Target_typ ("widening")

Case 2 above illustrates widening. Widening is an assignment in which the declared type of the source is more specific than the declared type of the target. For example, assigning an employee instance to a variable of person type.

Intuitively, the idea here is that you are regarding an employee as a person. An employee is a more narrowly defined, specialized kind of person, so you can put an employee in a slot meant for a person if you do not mind ignoring whatever extra specialization makes that person an employee. All employees are persons, so a widening assignment always works.

To illustrate widening, suppose that you have the following table:

TABLE T(perscol Person_typ, empcol Employee_typ, stucol Student_typ)

The assignments below show widening. The assignments are valid unless perscol has been defined to be not substitutable.

UPDATE T set perscol = empcol;

PL/SQL:

declare
var1 Person_typ;
var2 Employee_typ;
begin
var1 := var2;
end;

Besides widening, there is also narrowing. Narrowing is the reverse of widening. It involves regarding a more general, less specialized type of thing, such as a person, as a more narrowly defined type of thing, such as an employee. Not all persons are employees, so a particular assignment like this works only if the person in question actually happens to be an employee.

To do a narrowing assignment, you must use the TREAT function to explicitly change the declared type of the source value to the more specialized target type, or one of its subtypes, in the hierarchy. The TREAT function checks at runtime to verify that the change can be made; then TREAT either makes the change or returns NULL if the source value--the person in question--is not of the target type or one of its subtypes.

For example, the following UPDATE statement sets values of Person_typ in column perscol into column empcol of Employee_typ. For each value in perscol, the assignment succeeds only if that person is also an employee. If person George is not an employee, TREAT returns NULL, and the assignment returns NULL.

UPDATE T set empcol = TREAT(perscol AS Employee_typ);

The following statement attempts to do a narrowing assignment without explicitly changing the declared type of the source value. The statement will return an error:

Collection Assignments

In assignments of expressions of a collection type, the source and target must be of the same declared type. Neither widening nor narrowing is permitted. However, a subtype value can be assigned to a supertype collection.

Comparisons: Objects, REF Variables, and Collections

Comparing Object Instances

Two object instances can be compared if, and only if, they are both of the same declared type, or one is a subtype of the other.

Map methods and order methods provide the mechanism for comparing objects. You optionally define one or the other of these in an object type to specify the basis on which you want objects of that type to be compared. If a method of either sort is defined, it is called automatically whenever objects of that type or one of its subtypes need to be compared.

If a type does not define either a map method or an order method, object variables of that type can be compared only in SQL statements and only for equality or inequality. (Two objects of the same type count as equal only if the values of their corresponding attributes are equal.)

Comparing REF Variables

Two REF variables can be compared if, and only if, the targets that they reference are both of the same declared type, or one is a subtype of the other.

Comparing Collections.

There is no mechanism for comparing collections.

Functions and Predicates Useful with Objects

Several functions and predicates are particularly useful for working with objects and references to objects:

VALUE

REF

DEREF

TREAT

IS OF TYPE

SYS_TYPEID

Examples are given below and throughout this book.

In PL/SQL the VALUE, REF and DEREF functions can appear only in a SQL statement.

VALUE

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. For example, the following statement selects all persons whose name is John Smith:

SELECT VALUE(p) FROM person_table p
WHERE p.name = "John Smith";

The VALUE function may return instances of the declared type of the row or any of its subtypes. For example, the following query returns all persons, including students and employees, from an object view Person_v of persons:

SELECT VALUE(p) FROM Person_v p;

To retrieve only persons--that is, instances whose most specific type is person, use the ONLY keyword to confine the selection to the declared type of the view or subview that you are querying:

SELECT VALUE(p) FROM ONLY(Person_v) p;

The following example shows VALUE used to return object instance rows for updating:

REF

The REF function in a SQL statement takes as an argument a correlation name 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:

SELECT REF(p) FROM Person_v p;

The example below returns a REF to the person (or student or employee) whose id attribute is 0001:

SELECT REF(p)
FROM Person_v p
WHERE p.id = 0001 ;

DEREF

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 objects from the object view Person_v, including persons who are students and persons who are employees.

SELECT DEREF(REF(p)) FROM Person_v p;

TREAT

The TREAT function attempts to modify the declared type of an expression to a specified type--normally, a subtype of the expression's declared type. In other words, the function attempts to treat a supertype instance as a subtype instance--to treat a person as a student, for example. Whether this can be done in a given case depends on whether the person in question actually is a student (or student subtype, such as a part-time 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 happens not to be a student, TREAT returns NULL.

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: in other words, 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

The following example shows TREAT used in an assignment: a column of person type is set into a column of employee type. For each row in perscol, TREAT returns an employee type or NULL, depending on whether the given person happens to be an employee.

UPDATE T set empcol = TREAT(perscol AS Employee_typ);

In the next example, TREAT returns all (and only) Student_typ instances from object view Person_v of type Person_typ, a supertype of Student_typ. The statement uses TREAT to modify the type of p from Person_typ to Student_typ.

SELECT TREAT(VALUE(p) AS Student_typ)
FROM Person_v p;

For each p, The TREAT modification succeeds only if the most specific or specialized type of the value of p is Student_typ or one of its subtypes. If p is a person who is not a student, or if p is NULL, TREAT returns NULL in SQL.

You can also use TREAT to modify the declared type of a REF expression. For example:

SELECT TREAT(REF(p) AS REF Student_typ)
FROM Person_v p;

The example above returns REFs to all Student_typ instances. It returns NULL REFs for all person instances that are not students.

Perhaps the most important use of TREAT is to access attributes or methods of a subtype of a row or column's declared type. For example, the following query retrieves the major attribute of all persons who have this attribute (namely, students and part-time students). NULL is returned for persons who are not students:

SELECT name, TREAT(VALUE(p) AS Student_typ).major major
FROM persons p;
NAME MAJOR
---- ------
Bob null
Joe HISTORY
Tim PHYSICS

The following query will not work because major is an attribute of Student_typ but not of Person_typ, the declared type of table persons:

SELECT name, VALUE(p).major major
FROM persons p;

A substitutable object table or column of type T has a hidden column for every attribute of every subtype of T. These hidden columns are not listed by a DESCRIBE statement, but they contain subtype attribute data. TREAT enables you to access these columns.

The following example shows TREAT used to access a subtype method:

SELECT name, TREAT(VALUE(p) AS Student_typ).major() major
FROM persons p;

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 type(s).

For example, the query below retrieves only books authored by students. It excludes books authored by any student subtype (such as PartTimeStudent_typ).

In the next example, the statement tests objects in object view Person_v, which contains persons, employees, and students, and returns REFs just to objects of the two specified person subtypes Employee_typ and Student_typ (and their subtypes, if any):

SELECT REF(p) FROM Person_v P
WHERE VALUE(p) IS OF (Employee_typ, Student_typ);

The statement below returns only students whose most specific or specialized type is Student_typ. If the view contains any objects of a subtype of Student_typ--for example, PartTimeStudent_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 (namely, Person_typ):

SELECT TREAT(VALUE(p) AS Student_t)
FROM Person_v p
WHERE VALUE(p) IS OF(ONLY Student_t);

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 PersRefCol is declared to be REF Person_typ, you can get just the rows for students as follows:

SELECT * FROM view
WHERE DEREF(PersRefCol) IS OF (Student_typ);

IS OF is currently supported only for SQL, not for PL/SQL.

SYS_TYPEID

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

The most specific type of an object instance is the type to which the instance belongs that is farthest removed 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 do 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's final)

It is not itself derived from some other type (it's a root type), so it does not have any supertypes.

For an example of SYS_TYPEID, consider the substitutable object table persons, of Person_typ. Person_typ is the root type of a hierarchy that has Student_typ as a subtype and PartTimeStudent_typ as a subtype of Student_typ: