|Oracle9i Application Developer's Guide - Object-Relational Features
Release 1 (9.0.1)
Part Number A88878-01
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 functionality introduces a number of new concepts and resources. These are briefly described in the following sections.
An object type is a kind of datatype. You can use it in the same ways that you use more familiar datatypes such as
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:
Attributes hold the data about an object's features of interest. For example, a soldier object type might have the attributes
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.
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.
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.
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 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.
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
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:
personobject, allowing you to perform object-oriented operations
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 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.
"VALUE" for information on the
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.
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 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.
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 types require less storage space and allow more efficient access than unscoped
The following example shows
address_ref scoped to an object table of
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 ;
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.
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.
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:
If X represents an object of type PERSON, then the SQL expression:
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.)
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.
For more on storage of objects and REFs, see "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.
You use the
CREATE TYPE statement to define object types and collection types.
CREATE TYPE statements shown below define the object types
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
The indented elements
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,
purchase_order can be used in SQL statements in most of the same places you can use types like
For example, you might define a relational table to keep track of your contacts:
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").
This section describes object types and references, including:
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 );
gives a different result from
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
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.
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
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
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
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 );
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) );
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.
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.
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;
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 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.
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.
Oracle SQL lets you omit qualifying table names in some relational operations. For example, if
ASSIGNMENT is a column in
TASK is a column in
DEPTS, you can write:
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.
Using unqualified names can lead to problems. If you add an
ASSIGNMENT column to
DEPTS and forget to change the query given above, Oracle automatically recompiles the query such that the inner
SELECT uses the
ASSIGNMENT column from the
DEPTS table. This situation is called inner capture.
To avoid inner capture and similar problems resolving references, Oracle requires you to use a table alias to qualify any dot-notational reference to methods or attributes of objects. Use of a table alias is optional when referencing top-level attributes of an object table directly, without using the dot notation.
For example, the following statements define an object type
PERSON and two tables.
ptab1 is an object table for objects of type
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
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
ssnois 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.
ssnois the name of an attribute of the
PERSONobject in the column named
c1. This reference uses the dot notation and so requires a table alias, as shown in the fourth
SELECTuses 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
assignment column, and
duedate attribute of that column. But the expression is incorrect because
projects is a table name, not an alias.
The same requirement applies to attribute references that use
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.
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:
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 = 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:
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 are the means by which an application gains access to an object instance's data. You define a member method in the object type for each operation that you want an object of that type to be able to perform. For example, the method
get_sum() mentioned above that sums the total cost of a purchase order's line items operates on the data of a particular purchase order and is a member method.
Member methods have a built-in parameter named
SELF that denotes the object instance on which the method is currently being invoked. Member methods can reference the attributes and methods of
SELF without a qualifier. This makes it simpler to write member methods. For example, the following code shows a method declaration that takes advantage of
SELF to omit qualification of the attributes
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
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.
The values of a scalar datatype such as
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.
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
VARCHAR2 or to an ANSI SQL type such as
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
GROUP BY, and
ORDER BY clauses. Where
obj_2 are two object variables that can be compared using a map method
map(), the comparison:
is equivalent to:
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 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;
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).
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 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
You invoke a static method by using the "dot" notation to qualify the method call with the name of the object type:
Every object type has an implicit, system-defined constructor method, that is, a method that makes a new object and sets up the values of its attributes. The constructor method is a function; it returns the new object as its value. The name of the constructor method is just the name of the object type. Its parameters have the names and types of the object type's attributes.
For example, suppose we have a type
The following example creates a new object instance of
Customer_typ, specifies values for its attributes, and sets the object into a variable:
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'), ... ) ;
Oracle supports two collection datatypes: varrays and nested tables.
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.
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:
VARRAYs of type
PRICES have no more than ten elements, each of datatype
Creating an array type does not allocate space. It defines a datatype, which you can use as:
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
A varray cannot contain LOBs. This means that a varray also cannot contain elements of a user-defined type that has a LOB attribute.
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:
A table type definition does not allocate space. It defines a type, which you can use as
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
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
A convenient way to access the elements of a nested table individually is to use a nested cursor.
See Oracle9i SQL Reference for information about nested cursors, and see"Nested Tables" for more information on using nested tables.
Multi-level collection types are collection types whose elements are themselves directly or indirectly another collection type. Possible multi-level collection types are:
Like ordinary, single-level collection types, multi-level collection types can be used with columns in a relational table or with object attributes in an object table.
The following example creates a multi-level collection type that is a nested table of nested tables. The example models a system of stars in which each star has a nested table collection of the planets revolving around it, and each planet has a nested table collection of its satellites.
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;
A nested table type column or object table attribute requires a storage table where rows for all nested tables in the column are stored. Similarly with a multi-level nested table collection of nested tables: the inner set of nested tables requires a storage table just as the outer set does. You specify one by appending a second nested-table storage clause.
For example, the following code creates a table
stars that contains a column
planets whose type is a multi-level collection (a nested table of an object type that has a nested table attribute
satellites). Separate nested table clauses are provided for the outer
planets nested table and for the inner
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 example above can refer to the inner
satellite nested table by name because this nested table is a named attribute of an object. However, if the inner nested table is not an attribute, it has no name. The keyword
COLUMN_VALUE is provided for this case: you use it in place of a name for an inner nested table. For example:
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 example above, nested table
planets is made an IOT (index-organized table) by adding the
ORGANIZATION INDEX clause and assigning the nested table a primary key in which the first column is
NESTED_TABLE_ID. This column contains the ID of the row in the parent table with which a storage table row is associated. Specifying a primary key with
NESTED_TABLE_ID as the first column and index-organizing the table cause Oracle to physically cluster all the nested table rows that belong to the same parent row, for more efficient access.
Each nested table needs its own table storage clause, so you must have as many nested table storage clauses as you have levels of nested tables in a collection.
Multi-level varrays are stored in one of two ways, depending on whether the varray is a varray of varrays or a varray of nested tables.
LOBstorage is explicitly specified.
LOB, with only the
LOBlocator 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 example below 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;
As with single-level collections, both the source and the target must be of the same declared data type in assignments of multi-level collections.
Items whose data types are collection types, including multi-level collection types, cannot be compared.
You create an instance of a collection type in the same way that you create an instance of any other object type, namely, by calling the type's constructor method. The name of a type's constructor method is simply the name of the type. You specify the elements of the collection as a comma-separated list of arguments to the method.
Calling a constructor method with an empty list creates an empty collection of that type. Note that an empty collection is an actual collection that happens to be empty; it is not the same as a null collection.
Like single-level collection types, multi-level collection types are created by calling the respective type's constructor method. Like the constructor methods for other user-defined types, a constructor for a multi-level collection type is a system-defined function that has the same name as the type and returns a new instance of it--in this case, a new multi-level collection. Constructor parameters have the names and types of the object type's attributes.
The example below calls the constructor for the multi-level collection type
nt_pl_t. This type is a nested table of planets, each of which contains a nested table of satellites as an attribute. The constructor for the outer nested table calls the
planet_t constructor for each planet to be created; each planet constructor calls the constructor for the satellites nested table type to create its nested table of satellites; and the satellites nested table type constructor calls the
satellite_t constructor for each satellite instance to be created.
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) ) ) ) );
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.
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,
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.
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.
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
The collection column in the
TABLE expression uses a table alias to identify the containing table. In the following example, the containing table is listed in the
FROM clause solely to introduce a table alias for use by the collection:
TABLE expression in the
FROM clause can also have its own table alias:
The preceding examples produce rows only for employees who have projects. To get rows for employees with no projects, you can use outer-join syntax:
The (+) indicates that the dependent join between
e.projects should be
NULL-augmented. That is, there will be rows of
employees in the output for which
NULL or empty, with
NULL values for columns corresponding to
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
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
SELECTlist of the subquery must contain exactly one item
SELECT projects FROM employeessucceeds in a
TABLEexpression only if table
employeescontains 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
Unnesting queries can be used with multi-level collections, too, for both varrays and nested tables. The following example shows an unnesting query on a multi-level nested table collection of nested tables. From a table
stars in which each star has a nested table of planets and each planet has a nested table of satellites, the query returns the names of all satellites from the inner set of nested tables.
Oracle supports the following DML operations on nested table columns:
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
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;
For multi-level nested table collections, DML can be done atomically, on the collection as a whole, or piecewise, on selected elements. For multi-level varray collections, DML operations can be done only atomically.
The section "Constructors for Multi-Level Collections" above shows an example of inserting an entire multi-level collection with an
INSERT statement. Multi-level collections can also be updated atomically with an
UPDATE statement. For example, suppose
v_planets is a variable declared to be of the planets nested table type
nt_pl_t. The following statement updates
stars by setting the
planets collection as a unit to the value of
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
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);
Object types enable you to model the real-world entities such as customers and purchase orders that your application works with. But this is just the first step in exploiting the capabilities of objects. With objects, you can not only model an entity such as a customer, you can also define different specialized types of customers in a type hierarchy under the original type. You can then perform operations on a hierarchy and have each type implement and execute the operation in a special way.
A type hierarchy is a sort of family tree of object types. It consists of a parent base type, called a supertype, and one or more levels of child object types, called subtypes, derived from the parent.
Subtypes in a hierarchy are connected to their supertypes by inheritance. This means that subtypes automatically acquire the attributes and methods of their parent type. It also means that subtypes automatically acquire any changes made to these attributes or methods in the parent: any attributes or methods updated in a supertype are updated in subtypes as well.
A subtype becomes a specialized version of the parent type by adding new attributes and methods to the set inherited from the parent or by redefining methods it inherits. Redefining an inherited methods gives a subtype its own way of executing the method. Add to this that an object instance of a subtype can generally be substituted for an object instance of any of its supertypes in code, and you have polymorphism.
Polymorphism is the ability of a slot for a value in code to contain a value of either a certain declared type or any of a range of the declared type's subtypes. A method called on whatever value occupies the slot may execute differently depending on the value's type because the various types might implement the method differently.
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
corp_customer. Each of these subtypes is still at bottom a
customer, but a special kind of customer. What makes a subtype special and distinguishes it from its parent supertype is some change made in the subtype to the attributes or methods that the subtype received from its parent.
An object type's attributes and methods make the type what it is: they are its essential, defining features. If a
customer object type has the three attributes
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:
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.
For example, a
shape object type might define a method
calculate_area(). Two subtypes of
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.
An object type's definition determines whether subtypes can be derived from that type. To permit subtypes, the object type must be defined as not final. This is done by including the
NOT FINAL keyword in its type declaration. For example:
The statement above declares
Person_typ to be a not final type such that subtypes of
Person_typ can be defined. By default, an object type is final--that is, subtypes cannot be derived from it.
You can change a final type to a not final type and vice versa with an
ALTER TYPE statement. For example, the following statement changes
Person_typ to a final type:
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;
You create a subtype using a
CREATE TYPE statement that specifies the immediate parent of the subtype with an
The statement above creates
Student_typ as a subtype of
Person_typ. As a subtype of
Student_typ inherits all the attributes declared in or inherited by
Person_typ and any methods inherited by
Person_typ or declared in
The statement that defines
Person_typ by adding two new attributes. New attributes declared in a subtype must have names that are different from the names of any attributes or methods declared in any of its supertypes, higher up in its type hierarchy.
A type can have multiple child subtypes, and these can also have subtypes. The statement below creates another subtype
A subtype can be defined under another subtype. Again, the new subtype inherits all the attributes and methods that its parent type has, both declared and inherited. For example, the statement below defines a new subtype
Student_typ. The new subtype inherits all the attributes and methods of
Student_typ and adds another attribute.
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
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).
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 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).
MySubType_typ in the following example creates an overload of
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
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
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 overridden method in a
CREATE TYPE BODY statement.
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
sphere_typ, each type might define a method
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
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.
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
Employee_typ is called substitutability. A supertype is substitutable if one of its subtypes can substitute or stand in for it in a slot (a variable, column, etc.) whose declared type is the supertype.
In general, types are substitutable. This is what you would expect, given that a subtype is, after all, just a specialized kind of any of its supertypes. Formally, though, a subtype is a type in its own right: it is not the same type as its supertype. A column that holds all persons, including all persons who are students and all persons who are employees, actually holds data of multiple types.
Substitutability comes into play in attributes, columns, and rows (namely, of an object view or object table) declared to be an object type, a
REF to an object type, or a collection type.
In principle, object attributes, collection elements and REFs are always substitutable: there is no syntax at the level of the type definition to constrain their substitutability to some subtype. You can, however, turn off or constrain substitutability at the storage level, for specific tables and columns.
Object attributes, collection elements and REFs are substitutable. Where
MyType is an object type:
REFtype attributes: An attribute defined as
REF MyTypecan hold a
REFto an instance of
MyTypeor to an instance of any subtype of
MyTypecan hold an instance of
MyTypeor of any subtype of
MyTypecan hold instances of
MyTypeand instances of any subtype of
For example, the
author attribute is substitutable in the
Book_typ defined below:
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
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
Book_typ, you can use
TREAT to get the employee id of authors of
author column is of
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
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
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.
A subtype can have an attribute that is a supertype. For example:
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
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 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 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.
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:
With object tables, the clause can be applied to the table as a whole, like this:
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:
NOT SUBSTITUTABLE AT ALL LEVELSto be applied to it: the clause cannot be applied to an object-type attribute.
You can impose a constraint that limits the range of subtypes permitted in an object column or attribute to a particular subtype in the declared type's hierarchy. You do this using an
IS OF type constraint.
For example, the following statement creates a table of Book_typ in which authors are constrained to just those persons who are students:
Although the type
Book_typ allows authors to be of type
Person_typ, the column declaration imposes a constraint to store only instances of
You can only use the
IS OF type operator to constrain row and column objects to a single subtype (not several), and you must use the
ONLY keyword, as in the example above.
You can use either
IS OF type or
NOT SUBSTITUTABLE AT ALL LEVELS to constrain an object column, but you cannot use both.
The assignment rules described in this section apply to
INSERT/UPDATE statements, the
RETURNING clause, function parameters, and PL/SQL variables.
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:
Target_typare the same type
Source_typis a subtype of
Case 2 above illustrates widening. Widening is an assignment in which the declared type of the source is more specific than the declared type of the target. For example, assigning an employee instance to a variable of person type.
Intuitively, the idea here is that you are regarding an employee as a person. An employee is a more narrowly defined, specialized kind of person, so you can put an employee in a slot meant for a person if you do not mind ignoring whatever extra specialization makes that person an employee. All employees are persons, so a widening assignment always works.
To illustrate widening, suppose that you have the following table:
The assignments below show widening. The assignments are valid unless
perscol has been defined to be not substitutable.
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
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,
NULL, and the assignment returns
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:
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:
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 */
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.)
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.
There is no mechanism for comparing collections.
Several functions and predicates are particularly useful for working with objects and references to objects:
Examples are given below and throughout this book.
In PL/SQL the
DEREF functions can appear only in a SQL statement.
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:
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:
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:
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 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:
The example below returns a
REF to the person (or student or employee) whose
id attribute is 0001:
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.
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,
The two main uses of
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
TREAT returns an employee type or
NULL, depending on whether the given person happens to be an employee.
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
TREAT modification succeeds only if the most specific or specialized type of the value of
Student_typ or one of its subtypes. If
p is a person who is not a student, or if
NULL in SQL.
You can also use
TREAT to modify the declared type of a
REF expression. For example:
The example above returns
REFs to all
Student_typ instances. It returns
NULL REFs for all person instances that are not students.
Perhaps the most important use of
TREAT is to access attributes or methods of a subtype of a row or column's declared type. For example, the following query retrieves the
major attribute of all persons who have this attribute (namely, students and part-time students).
NULL is returned for persons who are not students:
SELECT name, TREAT(VALUE(p) AS Student_typ).major major FROM persons p; NAME MAJOR ---- ------ Bob null Joe HISTORY Tim PHYSICS
The following query will not work because
major is an attribute of
Student_typ but not of
Person_typ, the declared type of table
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:
"Assignments Across Types" for information on using
TREAT is supported only for SQL; it is not supported for PL/SQL.
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 query below retrieves only books authored by students. It excludes books authored by any student subtype (such as
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
Student_typ (and their subtypes, if any):
The statement below returns only students whose most specific or specialized type is
Student_typ. If the view contains any objects of a subtype of
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,
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:
IS OF is currently supported only for SQL, not for PL/SQL.
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 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:
For an example of
SYS_TYPEID, consider the substitutable object table
Person_typ is the root type of a hierarchy that has
Student_typ as a subtype and
PartTimeStudent_typ as a subtype of
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 query below returns the most specific types of authors stored in the books table.
author is a substitutable column of
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
"Hidden Columns for Substitutable Columns and Tables" in Chapter 6 for information about the type discriminant and other hidden columns