2
Basic Components of Oracle Objects

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.

This chapter contains these topics:

• Object-Relational Elements
• Defining Object and Collection Types
• Object Types and References
• Methods
• Collections
• Type Inheritance
• Functions and Predicates Useful with Objects

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.

Object types are database schema objects, subject to the same kinds of administrative control as other schema objects (see Chapter 4, "Managing Oracle Objects").

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:

CREATE TYPE person AS OBJECT (
  name        VARCHAR2(30),
  phone       VARCHAR2(20) );
  
CREATE TABLE person_table OF person;

You can view this table in two ways:

• As a single-column table in which each row is a person object, allowing you to perform object-oriented operations
• As a multi-column table in which each attribute of the object type person, namely name and phone, occupies a column, allowing you to perform relational operations

For example, you can execute the following instructions:

INSERT INTO person_table VALUES (
       "John Smith",
       "1-800-555-1212" );

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

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.

Row Objects and Column Objects

Objects that occupy complete rows in object tables are called row objects. Objects that occupy table columns in a larger row, or are attributes of other objects, are called column objects.

Object Views

An object view (see Chapter 5, "Applying an Object Model to Relational Data") is a way to access relational data using object-relational features. It lets you develop object-oriented applications without changing the underlying relational schema.

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.

CREATE TABLE people (
  id            NUMBER(4)
  name_obj      name_objtyp,
  address_ref   REF address_objtyp SCOPE IS address_objtab,
  phones_ntab   phone_ntabtyp)
  NESTED TABLE  phones_ntab STORE AS phone_store_ntab2 ;

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.

Subtypes are a feature of type inheritance.

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:

CREATE TYPE person AS OBJECT (
  name    VARCHAR2(30),
  manager REF person );

If X represents an object of type PERSON, then the SQL expression:

x.manager.name;

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:

DECLARE OrderRef REF to purchase_order;

SELECT REF(po) INTO OrderRef
               FROM purchase_order_table po
               WHERE po.id = 1000376;

The query must return exactly one row.

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 following CREATE TYPE statements 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.

CREATE TYPE person AS OBJECT (
  name        VARCHAR2(30),
  phone       VARCHAR2(20) );
  
CREATE TYPE lineitem AS OBJECT (
  item_name   VARCHAR2(30),
  quantity    NUMBER,
  unit_price  NUMBER(12,2) );

CREATE TYPE lineitem_table AS TABLE OF lineitem;
  
CREATE TYPE purchase_order AS OBJECT (
  id          NUMBER,
  contact     person,
  lineitems   lineitem_table,
  
  MEMBER FUNCTION
  get_value   RETURN NUMBER );

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").

Object Types and References

This section describes object types and references, including:

• Null Objects and Attributes
• Default Values for Objects and Collections
• Constraints for Object Tables
• Indexes for Object Tables and Nested Tables
• Triggers for Object Tables
• Rules for REF Columns and Attributes
• Name Resolution

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.

For example, consider the CONTACTS table defined as follows:

CREATE TYPE person AS OBJECT (
  name        VARCHAR2(30),
  phone       VARCHAR2(20) );

CREATE TABLE contacts (
  contact     person
  date        DATE );

The statement

INSERT INTO contacts VALUES (
  person (NULL, NULL),
 '24 Jun 1997' );

gives a different result from

INSERT INTO contacts VALUES (
  NULL,
 '24 Jun 1997' ); 

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:

people ( person(1, 'John Smith', '5 Cherry Lane'), 
         person(2, 'Diane Smith', NULL) )

The following example shows how to use literal invocations of constructor methods to specify defaults:

CREATE TABLE department (
  d_no    CHAR(5) PRIMARY KEY,
  d_name  CHAR(20),
  d_mgr   person DEFAULT person(1,'John Doe',NULL),
  d_emps  people DEFAULT people() )
  NESTED TABLE d_emps STORE AS d_emps_tab;

Note that the term PEOPLE( ) is a literal invocation of the constructor method for an empty PEOPLE table.

Constraints for Object Tables

You can define constraints on an object table just as you can on other tables.

You can define constraints on the leaf-level scalar attributes of a column object, with the exception of REFs that are not scoped.

The following examples illustrate the possibilities.

The first example places a primary key constraint on the SSNO column of the object table PERSON_EXTENT:

CREATE TYPE location (
  building_no NUMBER,
  city        VARCHAR2(40) );

CREATE TYPE person (
  ssno        NUMBER,
  name        VARCHAR2(100),
  address     VARCHAR2(100),
  office      location );

CREATE TABLE person_extent OF person (
  ssno        PRIMARY KEY );

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.

CREATE TABLE department (
  deptno      CHAR(5) PRIMARY KEY, 
  dept_name   CHAR(20),
  dept_mgr    person,
  dept_loc    location,
  CONSTRAINT  dept_loc_cons1
      UNIQUE (dept_loc.building_no, dept_loc.city),
  CONSTRAINT  dept_loc_cons2
       CHECK (dept_loc.city IS NOT NULL) );

Indexes for Object Tables and Nested Tables

You can define indexes on an object table or on the storage table for a nested table column or attribute just as you can on other tables.

You can define indexes on leaf-level scalar attributes of column objects, as shown in the following example. You can only define indexes on REF attributes or columns if the REF is scoped.

Here, DEPT_ADDR is a column object, and CITY is a leaf-level scalar attribute of DEPT_ADDR that we want to index:

CREATE TABLE department (
  deptno      CHAR(5) PRIMARY KEY, 
  dept_name   CHAR(20),
  dept_addr   address );

CREATE INDEX  i_dept_addr1
          ON  department (dept_addr.city);

Wherever Oracle expects a column name in an index definition, you can also specify a scalar attribute of an object column.

Triggers for Object Tables

You can define triggers on an object table just as you can on other tables. You cannot define a trigger on the storage table for a nested table column or attribute.

You cannot modify LOB values in a trigger body. Otherwise, there are no special restrictions on using object types with triggers.

The following example defines a trigger on the PERSON_EXTENT table defined in an earlier section:

CREATE TABLE movement (
     ssno        NUMBER,
     old_office  location,
     new_office  location );

CREATE TRIGGER trig1
  BEFORE UPDATE
             OF  office
             ON  person_extent
   FOR EACH ROW
           WHEN  new.office.city = 'REDWOOD SHORES'
   BEGIN
     IF :new.office.building_no = 600 THEN
      INSERT INTO movement (ssno, old_office, new_office)
       VALUES (:old.ssno, :old.office, :new.office);
     END IF;
   END;

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.

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:

SELECT * 
FROM projects 
WHERE EXISTS
  (SELECT * FROM  depts
            WHERE assignment = task); 

Oracle determines which table each column belongs to.

Using the dot notation, you can qualify the column names with table names or table aliases to make things more maintainable:

SELECT * FROM projects WHERE EXISTS
  (SELECT * FROM  depts WHERE projects.assignment = depts.task);

SELECT * FROM projects pj WHERE EXISTS
  (SELECT * FROM  depts dp WHERE pj.assignment = dp.task);

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

CREATE TYPE person AS OBJECT (ssno VARCHAR(20));
CREATE TABLE ptab1 OF person;
CREATE TABLE ptab2 (c1 person);

The following queries show some correct and incorrect ways to reference attribute ssno:

SELECT            ssno FROM ptab1     ;  --Correct
SELECT         c1.ssno FROM ptab2     ;  --Illegal
SELECT   ptab2.c1.ssno FROM ptab2     ;  --Illegal
SELECT       p.c1.ssno FROM ptab2 p   ;  --Correct

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

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
• Convey a user-defined type argument or return value in a PL/SQL remote procedure call.

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()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:

CREATE TYPE Rational AS OBJECT (
  num INTEGER,
  den INTEGER,
  MEMBER PROCEDURE normalize,
  ...
);

CREATE TYPE BODY Rational AS 
  MEMBER PROCEDURE normalize IS
    g INTEGER;
  BEGIN
    g := gcd(SELF.num, SELF.den);
    g := gcd(num, den);           -- equivalent to previous line
    num := num / g;
    den := den / g;
  END normalize;
  ...
END;

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:

CREATE TYPE Rectangle_typ AS OBJECT ( 
  len NUMBER,
  wid NUMBER,
  MAP MEMBER FUNCTION area RETURN NUMBER,
  ...
);

CREATE TYPE BODY Rectangle_typ AS 
  MAP MEMBER FUNCTION area RETURN NUMBER IS
  BEGIN
     RETURN len * wid;
  END area;
  ...
END;

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:

CREATE TYPE Customer_typ AS OBJECT (  
  id   NUMBER, 
  name VARCHAR2(20), 
  addr VARCHAR2(30), 
  ORDER MEMBER FUNCTION match (c Customer_typ) RETURN INTEGER
); 

CREATE TYPE BODY Customer_typ AS 
  ORDER MEMBER FUNCTION match (c Customer_typ) RETURN INTEGER IS 
  BEGIN 
    IF id < c.id THEN
      RETURN -1;               -- any negative number will do
    ELSIF id > c.id THEN 
      RETURN 1;                -- any positive number will do
    ELSE 
      RETURN 0;
    END IF;
  END;
END;

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 a constructor method implicitly defined for it by the system. A constructor method is a function that returns a new instance of the user-defined type and sets up the values of its attributes. You can also explicitly define your own constructors. The present section describes constructor methods in general and system-defined constructors in particular.

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

For example, suppose we have a type Customer_typ:

CREATE TYPE Customer_typ AS OBJECT (  
  id    NUMBER, 
  name  VARCHAR2(20), 
  phone VARCHAR2(30), 
); 

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:

INSERT INTO Customer_objtab
  VALUES (
    1, 'Jean Nance',
    Address_typ('2 Avocet Drive', 'Redwood Shores', 'CA', '95054'),
    ...
    ) ;

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.

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:

CREATE TABLE purchase_order_table OF purchase_order
   NESTED TABLE lineitems STORE AS lineitems_table;

The second line specifies LINEITEMS_TABLE as the storage table for the LINEITEMS attributes of all of the PURCHASE_ORDER objects in PURCHASE_ORDER_TABLE.

A convenient way to access the elements of a nested table individually is to use a nested cursor.

Multilevel Collection Types

Multilevel collection types are collection types whose elements are themselves directly or indirectly another collection type. Possible multilevel collection types are:

• Nested table of nested table type
• Nested table of varray type
• Varray of nested table type
• Varray of varray type
• 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, multilevel collection types can be used with columns in a relational table or with object attributes in an object table.

The following example creates a multilevel 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.

CREATE TYPE satellite_t AS OBJECT (
  name        VARCHAR2(20),
  diameter    NUMBER);

CREATE TYPE nt_sat_t AS TABLE OF satellite_t;

CREATE TYPE planet_t AS OBJECT (
  name        VARCHAR2(20),
  mass        NUMBER,
  satellites  nt_sat_t);

CREATE TYPE nt_pl_t AS TABLE OF planet_t;

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

CREATE TABLE stars (
  name     VARCHAR2(20),
  age      NUMBER,
  planets  nt_pl_t)
NESTED TABLE planets STORE AS planets_tab 
  (NESTED TABLE satellites STORE AS satellites_tab);

The preceding example 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:

CREATE TYPE inner_table AS TABLE OF NUMBER;

CREATE TYPE outer_table AS TABLE OF inner_table;

CREATE TABLE tab1 (
  col1 NUMBER,
  col2 outer_table)
NESTED TABLE col2 STORE AS col2_ntab
  (NESTED TABLE COLUMN_VALUE STORE AS cv_ntab);

Physical attributes for the storage tables can be specified in the nested table clause. For example:

CREATE TABLE stars (
  name     VARCHAR2(20),
  age      NUMBER,
  planets  nt_pl_t)
NESTED TABLE planets STORE AS planets_tab 
  ( PRIMARY KEY (NESTED_TABLE_ID, name) 
    ORGANIZATION INDEX COMPRESS
    NESTED TABLE satellites STORE AS satellites_tab );

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 preceding example, 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

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

CREATE TYPE va1 AS VARRAY(10) OF NUMBER;

CREATE TYPE nt3 AS TABLE OF va1;

CREATE TABLE tab2 (c1 NUMBER, c2 nt3)
NESTED TABLE c2 STORE AS c2_tab2_nt 
  ( VARRAY column_value STORE AS LOB tab2_lob );

The following example shows explicit LOB storage specified for a varray of varray type:

CREATE TYPE t2 AS OBJECT (a NUMBER, b va1);

CREATE TYPE va2 AS VARRAY(2) OF t2;

CREATE TABLE tab5 (c1 NUMBER, c2 va2)
VARRAY c2 STORE AS tab5_lob;

Assignment and Comparison of Multilevel Collections

As with single-level collections, both the source and the target must be of the same declared data type in assignments of multilevel collections.

Items whose data types are collection types, including multilevel collection types, cannot be compared.

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-delimited 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 Multilevel Collections

Like single-level collection types, multilevel 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 multilevel 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 multilevel collection. Constructor parameters have the names and types of the object type's attributes.

The following example calls the constructor for the multilevel 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.

INSERT INTO stars 
VALUES('Sun',23,
  nt_pl_t(
    planet_t(
      'Neptune',
      10,
      nt_sat_t(
        satellite_t('Proteus',67),
        satellite_t('Triton',82)
      )
    ),
    planet_t(
      'Jupiter',
      189,
      nt_sat_t(
        satellite_t('Callisto',97),
        satellite_t('Ganymede', 22)
      ) 
    ) 
  ) 
);

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:

SELECT e.empname, e.projects
  FROM employees e;
  
  EMPNAME   PROJECTS
  -------   --------
  'Bob'     PROJECTS_LIST_NT(14, 23, 144)
  'Daphne'  PROJECTS_LIST_NT(14, 35)

If project values or instances are a user-defined type--for example, Proj_t, with two attributes, id and name--a result row looks something like this:

            EMPNAME   PROJECTS
            -------   --------
            'Bob'     PROJECTS_LIST_NT(PROJ_T(14, 'White Horse'), PROJ_T(23, 'Excalibur'), ...)

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.

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:

SELECT e.empname, p.*
  FROM employees e, TABLE(e.projects) p;
  
  EMPNAME   PROJECTS
  -------   --------
  'Bob'     14
  'Bob'     23
  'Bob'     144
  'Daphne'  14
  'Daphne'  35

As the preceding example shows, a TABLE expression can have its own table alias. In the example, a table alias for the TABLE expression appears in the SELECT list to select columns returned by the TABLE expression.

The TABLE expression uses another table alias to specify the table that contains the collection column that the TABLE expression references. Thus the expression TABLE(e.projects) specifies the employees table as containing the projects collection column. A TABLE expression can use the table alias of any table appearing to the left of it in a FROM clause to reference a column of that table. This way of referencing collection columns is called left correlation.

In the following example, the employees table is listed in the FROM clause solely to provide a table alias for the TABLE expression to use. No columns from the employees table other than the column referenced by the TABLE expression appear in the result:

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

PROJECTS
--------
14
23
144
14
35

Or:

SELECT p.*
  FROM employees e, TABLE(e.projects) p
  WHERE e.empid = 100;

PROJECTS
--------
14
23
144

The following example produces rows only for employees who have projects.

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

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 dependent join 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.

SELECT *
  FROM TABLE(SELECT e.projects 
               FROM employees e
               WHERE e.empid = 100);

PROJECTS
--------
14
23
144

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:

SELECT e.empid, CURSOR(SELECT * FROM TABLE(e.projects)) 
  FROM employees e;

Unnesting Queries with Multilevel Collections

Unnesting queries can be used with multilevel collections, too, for both varrays and nested tables. The following example shows an unnesting query on a multilevel 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.

SELECT t.name 
  FROM stars s, TABLE(s.planets) p, TABLE(p.satellites) t;

Performing DML Operations on Collections

Oracle supports the following DML operations on nested table columns:

• Inserts and updates that provide a new value for the entire collection
• Piecewise Updates
  • Inserting new elements into the collection
  • Deleting elements from the collection
  • Updating elements of the collection.

Oracle does not support piecewise updates on VARRAY columns. However, VARRAY columns can be inserted into or updated as an atomic unit.

For piecewise updates of nested table columns, the DML statement identifies the nested table value to be operated on by using the TABLE expression.

The following DML statements demonstrate piecewise operations on nested table columns.

INSERT INTO TABLE(SELECT e.projects
                  FROM       employees e
                  WHERE      e.eno = 100)
   VALUES (1, 'Project Neptune');

UPDATE TABLE(SELECT e.projects
                 FROM        employees e
                 WHERE       e.eno = 100) p
   SET VALUE(p) = project_typ(1, 'Project Pluto')
   WHERE p.pno = 1;

DELETE FROM TABLE(SELECT e.projects
                  FROM        employee e
                  WHERE       e.eno = 100) p
   WHERE p.pno = 1;

Performing DML on Multilevel Collections

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

Collections as Atomic Data Items

The section "Constructors for Multilevel Collections" shows an example of inserting an entire multilevel collection with an INSERT statement. Multilevel 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.

UPDATE stars  s
SET s.planets = :v_planets
WHERE s.name = 'Aurora Borealis';

Piecewise Operations on Nested Tables

Piecewise DML is possible only on nested tables, not on varrays.

The following example shows a piecewise insert operation on the planets nested table of nested tables: the example inserts a new planet, complete with its own nested table of satellite_t:

INSERT INTO TABLE( SELECT planets FROM stars WHERE name = 'Sun') 
VALUES ('Saturn', 56, 
  nt_sat_t( 
    satellite_t('Rhea', 83)
  )
);

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.

INSERT INTO TABLE( SELECT p.satellites 
  FROM TABLE( SELECT s.planets 
    FROM stars s
    WHERE s.name = 'Sun') p
  WHERE p.name = 'Uranus')
VALUES ('Miranda', 31);

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

Text description of the illustration adobj027.gif

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:

CREATE TYPE Person_typ AS OBJECT
( ssn NUMBER,
  name VARCHAR2(30),
  address VARCHAR2(100)) NOT FINAL;

The preceding statement 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:

CREATE TYPE T AS OBJECT (..., 
  MEMBER PROCEDURE Print(),
  FINAL MEMBER FUNCTION foo(x NUMBER)...
) NOT FINAL;

Creating Subtypes

You create a subtype using a CREATE TYPE statement that specifies the immediate parent of the subtype with an UNDER parameter:

CREATE TYPE Student_typ UNDER Person_typ 
( deptid NUMBER,
   major VARCHAR2(30)) NOT FINAL;

The preceding statement 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 following statement creates another subtype Employee_typ under Person_typ.

CREATE TYPE Employee_typ UNDER Person_typ
( empid NUMBER, 
  mgr VARCHAR2(30));

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 following statement 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:

CREATE TYPE Address_typ AS OBJECT(...) NOT INSTANTIABLE NOT FINAL;
CREATE TYPE USAddress_typ UNDER Address_typ(...);
CREATE TYPE IntlAddress_typ UNDER Address_typ(...);

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():

CREATE TYPE MyType_typ AS OBJECT (..., 
  MEMBER PROCEDURE foo(x NUMBER), ...) NOT FINAL;

CREATE TYPE MySubType_typ UNDER MyType_typ (..., 
  MEMBER PROCEDURE foo(x DATE), 
  STATIC FUNCTION bar(...)...
  ...);

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():

CREATE TYPE MyType_typ AS OBJECT (..., 
  MEMBER PROCEDURE Print(),
  FINAL MEMBER FUNCTION foo(x NUMBER)...
) NOT FINAL;

CREATE TYPE MySubType_typ UNDER MyType_typ (..., 
  OVERRIDING MEMBER PROCEDURE Print(), 
...);

As with new methods, you supply the declaration for an overridng 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.

Text description of the illustration adobj025.gif

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, and so forth) 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.

Attribute Substitutability

Object attributes, collection elements and REFs are substitutable. Where MyType is an object type:

• REF type attributes: An attribute defined as REF MyType can hold a REF to an instance of MyType or to an instance of any subtype of MyType.
• Object type attributes: An attribute defined to be of type MyType can hold an instance of MyType or of any subtype of MyType.
• Collection type elements: A collection of elements of type MyType can hold instances of MyType and instances of any subtype of MyType.

For instance, the author attribute is substitutable in the Book_typ defined in the following example:

CREATE TYPE Book_typ AS OBJECT 
( title VARCHAR2(30),
  author Person_typ     /* substitutable */);

An instance of Book_typ can be created by specifying a title string and an author of Person_typ or of any subtype of Person_typ. The following example specifies an author of type Employee_typ:

Book_typ(`My Oracle Experience',
       Employee_typ(12345, `Joe', `SF', 1111, NULL))

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

   SELECT TREAT(author AS Employee_typ).empid FROM Books_v;

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:

CREATE TYPE Person_typ AS OBJECT
( ssn NUMBER,
  name VARCHAR2(30),
  address VARCHAR2(100)) NOT FINAL;

CREATE TYPE Student_typ UNDER Person_typ 
( deptid NUMBER,
   major VARCHAR2(30)) NOT FINAL;

CREATE TYPE PartTimeStudent_typ UNDER Student_typ
( numhours NUMBER);

An object table of Person_typ can contain rows of all three types. You insert an instance of a given type using the constructor for that type in the VALUES clause of the INSERT statement:

CREATE TABLE persons OF Person_typ;

INSERT INTO persons 
  VALUES (Person_typ(1243, 'Bob', '121 Front St'));

INSERT INTO persons 
  VALUES (Student_typ(3456, 'Joe', '34 View', 12, 'HISTORY'));
  
INSERT INTO persons 
  VALUES (PartTimeStudent_typ(5678, 'Tim', 13, 'PHYSICS', 20));

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:

CREATE TABLE books (title varchar2(100), author Person_typ);

INSERT INTO books 
 VALUES('An Autobiography', Person_typ(1243, 'Bob'));

INSERT INTO books 
 VALUES('Business Rules', Student_typ(3456, 'Joe', 12, 'HISTORY'));

INSERT INTO books
 VALUES('Mixing School and Work', 
         PartTimeStudent_typ(5678, 'Tim', 13, 'PHYSICS', 20));

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.

Creating Subtypes After Creating Substitutable Columns

If you create a subtype, any table that already has substitutable columns of the supertype is automatically enabled to store the new subtype as well. This means that your options for creating subtypes are affected by the existence of such tables: if such a table exists, you can only create subtypes that are substitutable, that is, subtypes that Oracle can enable that table to store.

The following example shows an attempt to create a subtype Student_typ. The attempt fails because Student_typ has a supertype attribute, and table persons has a substitutable column p of the supertype.

CREATE TYPE Person_typ AS OBJECT 
( ssn NUMBER, 
  name VARCHAR2(30), 
  address VARCHAR2(100)) NOT FINAL; 

CREATE TYPE Employee_typ UNDER Person_typ 
( salary NUMBER) NOT FINAL; 

CREATE TABLE persons (p person_typ); 
-- Table persons can store Person_typ and Employee_typ 

INSERT INTO persons 
  VALUES (Person_typ(1243, 'Bob', '121 Front St')); 

-- This statement fails because there exists a substitutable
-- column of the supertype.
CREATE TYPE Student_typ UNDER Person_typ 
( advisor Person_typ); 

The following attempt succeeds. This version of the Student_typ subtype is substitutable. Oracle automatically enables table persons to store instances of this new type.

CREATE TYPE Student_typ UNDER Person_typ 
( deptid NUMBER, 
  major VARCHAR2(30)) NOT FINAL; 

-- Inserts an instance of the subtype in table persons 
INSERT INTO persons 
  VALUES (Student_typ(3456, 'Joe', '34 View', 12, 'HISTORY')); 

Dropping Subtypes After Creating Substitutable Columns

You can drop a subtype with the VALIDATE option only if no instances of the subtype are stored in any substitutable column of the supertype.

For example, the following statement fails because an instance of Student_typ is stored in substitutable column p of table persons:

-- This statement fails:
DROP TYPE Student_typ VALIDATE; 

To drop the type, first delete any of its instances in substitutable columns of the supertype:

DELETE FROM persons WHERE p IS OF (Student_typ); 

-- Now the DROP statement succeeds 
DROP TYPE Student_typ VALIDATE; 

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:

CREATE TABLE catalog (book Book_typ, price NUMBER)
  COLUMN book NOT SUBSTITUTABLE AT ALL LEVELS;

With object tables, the clause can be applied to the table as a whole, like this:

CREATE TABLE Student_books OF Book_typ NOT SUBSTITUTABLE AT ALL LEVELS;

You can specify that the element type of a collection is not substitutable using syntax like this:

CREATE TABLE departments(name VARCHAR2(10), emps emp_set)
  NESTED TABLE (emps) 
  NOT SUBSTITUTABLE AT ALL LEVELS STORE AS ...

Some things to note about turning off substitutability:

• There is no mechanism to turn off substitutability for REF columns.
• 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:

CREATE TABLE Student_books OF Book_typ 
  COLUMN author IS OF (ONLY Student_typ);

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 preceding example.

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 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 following assignments 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:

UPDATE T set empcol = perscol;

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.

For example, suppose we have the following collection types:

CREATE TYPE PersonSet AS TABLE OF Person_typ;
CREATE TYPE StudentSet AS TABLE OF Student_typ;

Expressions of these different collection types cannot be assigned to each other, but a collection element of Student_typ can be assigned to a collection of PersonSet type:

declare
  var1 PersonSet; var2 StudentSet;
  elem1 Person_typ; elem2 Student_typ;
begin
  var1 := var2;                 /* ILLEGAL - collections not of same type */
  var1 := PersonSet (elem1, elem2);      /* LEGAL : Element is of subtype */

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 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:

UPDATE TABLE(SELECT e.projects
               FROM        employees e
               WHERE       e.eno = 100) p
   SET VALUE(p) = project_typ(1, 'Project Pluto')
   WHERE p.pno = 1;

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 following example 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 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;

Currently, TREAT is supported only for SQL; it is not supported for PL/SQL.

IS OF type

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 persons table.

SELECT VALUE(p) FROM persons p
WHERE VALUE(p) IS OF (Student_typ);

VALUE(p)
--------
Student_typ('Joe', 3456, 12, 10000)
PartTimeStudent_typ('Tim', 5678, 13, 1000, 20)

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 following query retrieves only books authored by students. It excludes books authored by any student subtype (such as PartTimeStudent_typ).

SELECT b.title title, b.author author FROM books b
WHERE b.author IS OF (ONLY Student_typ);

TITLE             AUTHOR
-----             ------
Business Rules    Student_typ('Joe', 3456, 12, 10000)

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 following statement 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.

  See Also: "Hidden Columns for Substitutable Columns and Tables" for more information about type discriminant columns

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:

CREATE TABLE persons OF Person_typ;

INSERT INTO persons 
  VALUES (Person_typ(1243, 'Bob', '121 Front St'));

INSERT INTO persons 
  VALUES (Student_typ(3456, 'Joe', '34 View', 12, 'HISTORY'));
  
INSERT INTO persons 
  VALUES (PartTimeStudent_typ(5678, 'Tim', 13, 'PHYSICS', 20));

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

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

NAME   TYPEID
----   ------
Bob    01
Joe    02
Tim    03

The following query returns the most specific types of authors stored in the books table. author is a substitutable column of Person_typ:

SELECT b.title, b.author.name, SYS_TYPEID(author) typeid FROM books b;

TITLE                       AUTHOR     TYPEID
----                        ------     ------
An Autobiography            Bob        01
Business Rules              Joe        02
Mixing School and Work      Tim        03