2 Basic Components of Oracle Objects

Null Objects and Attributes

An object whose value is NULL is called atomically null. An atomically null object is different from an object that has null values for all its attributes. In an object with null values, a table column, object attribute, collection, or collection element might be 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. When all the attributes are null, you can still change these attributes and call the object's subprograms or methods. With an atomically null object, you can do neither of these things.

Example 2-1 creates the contacts table and defines the person_typ object type and two instances of this type.

-- Ex. 2-1 Inserting NULLs for Objects in a Table
CREATE OR REPLACE TYPE person_typ AS OBJECT (
  idno           NUMBER,
  name           VARCHAR2(30),
  phone          VARCHAR2(20),
  MAP MEMBER FUNCTION get_idno RETURN NUMBER, 
  MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) );
/

CREATE OR REPLACE TYPE BODY person_typ AS
  MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  BEGIN
    RETURN idno;
  END;
  MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) IS
  BEGIN
    -- use the PUT_LINE procedure of the DBMS_OUTPUT package to display details
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(idno) || ' - '  || name || ' - '  || phone);
  END;
END;
/
CREATE TABLE contacts (
  contact        person_typ,
  contact_date   DATE );

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

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

Two instances of person_typ are inserted into the table and give two different results. In both cases, Oracle Database allocates space in the contacts table for a new row and sets its DATE column to the value given. But in the first case, Oracle Database allocates space for an object in the contact column and sets each of the object's attributes to NULL. In the second case, Oracle Database sets the person_typ 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, one that has no elements.

Character Length Semantics

Lengths for character types CHAR and VARCHAR2 may be specified as a number of characters, instead of bytes, in object attributes and collections even if some of the characters consist of multiple bytes.

To specify character-denominated lengths for CHAR and VARCHAR2 attributes, you add the qualifier char to the length specification.

Like CHAR and VARCHAR2, NCHAR and NVARCHAR2 may also be used as attribute types in objects and collections. NCHAR and NVARCHAR2 are always implicitly measured in terms of characters, so no char qualifier is used.

For example, the following statement creates an object with both a character-length VARCHAR2 attribute and an NCHAR attribute:

-- Ex. 2-2 Creating the employee_typ Object Using a char Qualifier
CREATE OR REPLACE TYPE employee_typ AS OBJECT ( 
  name        VARCHAR2(30 char), 
  language    NCHAR(10), 
  phone       VARCHAR2(20) );
/

For CHAR and VARCHAR2 attributes whose length is specified without a char qualifier, the NLS_LENGTH_SEMANTICS initialization parameter setting (CHAR or BYTE) indicates the default unit of measure.

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 defining constraints.

Example 2-3 places an implicit PRIMARY KEY constraint on the office_id column of the object table office_tab.

-- Ex. 2-3 Creating the office_tab Object Table with a Constraint 
-- requires Ex. 2-1
CREATE OR REPLACE TYPE location_typ AS OBJECT (
  building_no  NUMBER,
  city         VARCHAR2(40) );
/

CREATE OR REPLACE TYPE office_typ AS OBJECT (
  office_id    VARCHAR(10),
  office_loc   location_typ,
  occupant     person_typ );/

CREATE TABLE office_tab OF office_typ (
             office_id      PRIMARY KEY );

The object type location_typ defined in Example 2-3 is the type of the dept_loc column in the department_mgrs table in Example 2-4.

Example 2-4 defines constraints on scalar attributes of the location_typ objects in the table.

-- Ex. 2-4 Creating the department_mgrs Table with Multiple Constraints
-- requires Ex. 2-1 and 2-3
CREATE TABLE department_mgrs (
  dept_no     NUMBER PRIMARY KEY, 
  dept_name   CHAR(20),
  dept_mgr    person_typ,
  dept_loc    location_typ,
  CONSTRAINT  dept_loc_cons1
      UNIQUE (dept_loc.building_no, dept_loc.city),
  CONSTRAINT  dept_loc_cons2
       CHECK (dept_loc.city IS NOT NULL) );

INSERT INTO department_mgrs VALUES 
          ( 101, 'Physical Sciences', 
           person_typ(65,'Vrinda Mills', '1-1-650-555-0125'),
           location_typ(300, 'Palo Alto'));

Indexes for Object 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. For an example of an index on a nested table, see Example 5-5.

You can define indexes on leaf-level scalar attributes of column objects, as shown in Example 2-5. You can only define indexes on REF attributes or columns if the REF is scoped. This example indexes city, which is a leaf-level scalar attribute of the column object dept_addr.

-- Ex. 2-5 Creating an Index on an Object Type in a Table
-- requires Ex. 2-1, 2-3, 
CREATE TABLE department_loc (
  dept_no     NUMBER PRIMARY KEY, 
  dept_name   CHAR(20),
  dept_addr   location_typ );

CREATE INDEX  i_dept_addr1
          ON  department_loc (dept_addr.city);

INSERT INTO department_loc VALUES
          ( 101, 'Physical Sciences',
           location_typ(300, 'Palo Alto'));
INSERT INTO department_loc VALUES 
          ( 104, 'Life Sciences', 
           location_typ(400, 'Menlo Park'));
INSERT INTO department_loc VALUES 
          ( 103, 'Biological Sciences', 
           location_typ(500, 'Redwood Shores'));

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

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.

Example 2-6 defines a trigger on the office_tab table defined in "Constraints for Object Tables".

-- Ex. 2-6 Creating a Trigger on Objects in a Table
-- requires Ex. 2-1 and 2-3
CREATE TABLE movement (
     idno           NUMBER,
     old_office     location_typ,
     new_office     location_typ );

CREATE TRIGGER trigger1
  BEFORE UPDATE
             OF  office_loc
             ON  office_tab
   FOR EACH ROW
           WHEN  (new.office_loc.city = 'Redwood Shores')
   BEGIN
     IF :new.office_loc.building_no = 600 THEN
      INSERT INTO movement (idno, old_office, new_office)
       VALUES (:old.occupant.idno, :old.office_loc, :new.office_loc);
     END IF;
   END;/
INSERT INTO office_tab VALUES 
    ('BE32', location_typ(300, 'Palo Alto' ),person_typ(280, 'John Chan', 
       '415-555-0101'));
 
UPDATE office_tab set office_loc =location_typ(600, 'Redwood Shores')
  where office_id = 'BE32'; 
 
select * from office_tab;
 
select * from movement;

Rules for REF Columns and Attributes

janis maybe this should be in chapter 1 and that section here?

In Oracle Database, 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 Database 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.

A SCOPE constraint can be applied 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

Janis: should merge w/ sec Accessing Object Attributes With Dot Notation,4-4?

Oracle SQL lets you omit qualifying table names in some relational operations. For example, if dept_addr is a column in the department_loc table and old_office is a column in the movement table, you can use the following:

-- Name resolution w/o table names example
SELECT * FROM department_loc WHERE EXISTS 
  (SELECT * FROM movement WHERE dept_addr = old_office);

Oracle Database determines which table each column belongs to.

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

-- Ex. 2-7 Using the Dot Notation for Name Resolution 
-- requires Ex. 2-1, 2-3, 2-5, and 2-6
SELECT * FROM department_loc WHERE EXISTS 
  (SELECT * FROM movement WHERE department_loc.dept_addr = movement.old_office);

SELECT * FROM department_loc d WHERE EXISTS 
  (SELECT * FROM movement m WHERE d.dept_addr = m.old_office);

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

Restriction on Using User-Defined Types with a Remote Database

Objects or 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. Oracle Database restricts use of a database link as follows:

• You cannot connect to a remote database to select, insert, or update a user-defined type or an object REF on a remote table.

  You can use the CREATE TYPE statement with the optional keyword OID to create a user-specified object identifier (OID) that allows an object type to be used in multiple databases. See the discussion on assigning an OID to an object type in the Oracle Database Data Cartridge Developer's Guide.

  fyi... above is in cartidge because it was added for them and not recommended otherwise

• You cannot use database links within PL/SQL code to declare a local variable of a remote user-defined type.

• You cannot convey a user-defined type argument or return value in a PL/SQL remote procedure call.

Member Methods

Member methods provide an application with 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. Non-comparison member methods are declared as either MEMBER FUNCTION or MEMBER PROCEDURE. Comparison methods use MAP MEMBER FUNCTION or ORDER MEMBER FUNCTION as described in "Member Methods for Comparing Objects".

As an example of a member method, you might declare a function get_sum() that sums the total cost of a purchase order's line items. The following line of code calls this function for purchase order po and returns the amount into sum_line_items.

Comment: following fixes bug 5347524

sum_line_items:= po.get_sum();

Dot notation specifies the current object and the method it calls. Parentheses are required even if there are no parameters.

This section contains these topics:

• SELF Parameters in Member Methods

• Member Methods for Comparing Objects

-- Ex. 2-8 Creating a Member Method
CREATE OR REPLACE TYPE solid_typ AS OBJECT (
  len    INTEGER,
  wth    INTEGER,
  hgt    INTEGER,
  MEMBER FUNCTION surface RETURN INTEGER,
  MEMBER FUNCTION volume RETURN INTEGER,
  MEMBER PROCEDURE display (SELF IN OUT NOCOPY solid_typ) );
/

CREATE OR REPLACE TYPE BODY solid_typ AS
  MEMBER FUNCTION volume RETURN INTEGER IS
  BEGIN
    RETURN len * wth * hgt;
 -- RETURN SELF.len * SELF.wth * SELF.hgt; -- equivalent to previous line 
  END;
  MEMBER FUNCTION surface RETURN INTEGER IS
  BEGIN -- not necessary to include SELF in following line
    RETURN 2 * (len * wth + len * hgt + wth * hgt);
  END;
  MEMBER PROCEDURE display (SELF IN OUT NOCOPY solid_typ) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Length: ' || len || ' - '  || 'Width: ' || wth 
                          || ' - '  || 'Height: ' || hgt);
    DBMS_OUTPUT.PUT_LINE('Volume: ' || volume || ' - ' || 'Surface area: ' 
                          || surface);
  END;
END;
/

CREATE TABLE solids of solid_typ;
INSERT INTO solids VALUES(10, 10, 10);
INSERT INTO solids VALUES(3, 4, 5);
SELECT * FROM solids;
SELECT s.volume(), s.surface() FROM solids s WHERE s.len = 10;
DECLARE
  solid solid_typ;
BEGIN -- PL/SQL block for selecting a solid and displaying details
  SELECT VALUE(s) INTO solid FROM solids s WHERE s.len = 10;
  solid.display();
END;
/

-- Ex. 2-9 Creating a Map Method
CREATE OR REPLACE TYPE rectangle_typ AS OBJECT ( 
  len NUMBER,
  wid NUMBER,
  MAP MEMBER FUNCTION area RETURN NUMBER);
/

CREATE OR REPLACE TYPE BODY rectangle_typ AS 
  MAP MEMBER FUNCTION area RETURN NUMBER IS
  BEGIN
     RETURN len * wid;
  END area;
END;
/

-- Ex. 2-10 Invoking a Map Method, requires Ex. 2-9

DECLARE
  po rectangle_typ;
 
BEGIN
  po :=NEW rectangle_typ(10,5);
 
DBMS_OUTPUT.PUT_LINE('AREA:' || po.area()); -- prints AREA:50
  END;
/

-- Ex. 2-11 Creating and Invoking an Order Method 
DROP TYPE location_typ FORCE;
-- above necessary if you have previously created object
CREATE OR REPLACE TYPE location_typ AS OBJECT (
  building_no  NUMBER,
  city         VARCHAR2(40),
  ORDER MEMBER FUNCTION match (l location_typ) RETURN INTEGER );/
CREATE OR REPLACE TYPE BODY location_typ AS 
  ORDER MEMBER FUNCTION match (l location_typ) RETURN INTEGER IS 
  BEGIN 
    IF building_no < l.building_no THEN
      RETURN -1;               -- any negative number will do
    ELSIF building_no > l.building_no THEN 
      RETURN 1;                -- any positive number will do
    ELSE 
      RETURN 0;
    END IF;
  END;
END;/

-- invoking match method
DECLARE
loc location_typ;
secloc location_typ;
a number;
 
BEGIN
 loc :=NEW location_typ(300, 'San Francisco');
 secloc :=NEW location_typ(200, 'Redwood Shores');
 a := loc.match(secloc);
 
DBMS_OUTPUT.PUT_LINE('order (1 is greater, -1 is lesser):' ||a); -- prints order:1 
 END;
/

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.

Static methods are declared using STATIC FUNCTION or STATIC PROCEDURE.

You invoke a static method by using dot notation to qualify the method call with the name of the object type, for example:

type_name.method()

See "Static Methods" for information on design considerations.

Constructor Methods

A constructor method is a function that returns a new instance of the user-defined type and sets up the values of its attributes. Constructor methods are either system-defined or user-defined.

To invoke a constructor, the keyword NEW can be used, but is not required.

--Informal Example  Create people_tab as table Using Literal Invocation 
CREATE TABLE people_tab OF person_typ;

INSERT INTO people_tab VALUES (
       person_typ(101, 'John Smith', '1-650-555-0135') );

External Implemented Methods

You can use PL/SQL to invoke external subprograms that have been written in other languages. This provides access to the strengths and capabilities of those languages.

Supertypes and Subtypes

A subtype can be derived from a supertype either directly or indirectly through intervening levels of other subtypes. A supertype can have multiple sibling subtypes, but a subtype can have at most one direct parent supertype (single inheritance).

Figure 2-1 Supertypes and Subtypes in Type Hierarchy

Description of "Figure 2-1 Supertypes and Subtypes in Type Hierarchy"

To derive a subtype from a supertype, define a specialized variant of the supertype that adds new attributes and methods to the set inherited from the parent or redefine (override) the inherited methods. For example, from a person_typ object type you might derive the specialized types student_typ and employee_typ. Each of these subtypes is still a person_typ, but a special kind of person. What distinguishes a subtype from its parent supertype is some change made to the attributes or methods that the subtype received from its parent.

Unless a subtype redefines 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. If a person_typ object type has the three attributes idno, name, and phone and the method get_idno(), then any object type that is derived from person_typ will have these same three attributes and a method get_idno(). If the definition of person_typ changes, so do the definitions of any subtypes.

Subtypes are created using the keyword UNDER as follows:

CREATE TYPE student_typ UNDER person_typ

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 student_typ as a special kind of person_typ by adding an attribute for major. 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 that a subtype inherits so that the subtype's version executes different code from the parent's.

  For example, a ellipse object might define a method calculate(). Two subtypes of ellipse_typ, circle_typ and sphere_typ, might each implement this method in a different way.

  See Also:

  "Overloading and Overriding Methods"

where should we put following

The inheritance relationship between a supertype and its subtypes is the source of much of the power of objects and much of their complexity. Being able to change a method in a supertype and have the change take effect in all the subtypes downstream just by recompiling is very powerful. But this same capability means that you have to consider whether or not you want to allow a type to be specialized or a method to be redefined. Similarly, for a table or column to be able to contain any type in a hierarchy is also powerful, but you must decide whether or not to allow this in a particular case. Also, you may need to constrain DML statements and queries so that they pick out just the range of types that you want from the type hierarchy.

Defining FINAL and NOT FINAL Types and Methods

For an object type to be inheritable, the object type definition must specify that it is inheritable. Then subtypes can be derived from it. For a method, the definition must indicate whether or not it can be overridden The keywords FINAL or NOT FINAL are used for both types and methods.

In order for an object type to be inheritable, thus allowing subtypes to be derived from it, the object definition must specify this. For a method, the definition must indicate whether or not it can be overridden The keywords FINAL or NOT FINAL are used for both types and methods.

• For a type FINAL, (default) means that no subtypes can be derived from it. NOT FINAL means subtypes can be derived.

• For a method, FINAL means that subtypes cannot override it by providing their own implementation. NOT FINAL (default) means that you can override the method of the supertype.

Definitions of object types and method includes the NOT FINAL or FINAL keywords in the type and method declarations, as shown in Example 2-12 and Example 2-13.

-- Ex 2-12 Creating the person_typ Object Type as NOT FINAL
DROP TYPE person_typ FORCE;
-- above necessary if you have previously created object

CREATE OR REPLACE TYPE person_typ AS OBJECT (
   idno           NUMBER,
   name           VARCHAR2(30),
   phone          VARCHAR2(20)) 
NOT FINAL;
/

Example 2-12 declares person_typ to be a not final type and therefore subtypes of person_typ can be defined.

Example 2-13 creates a not final object type that contains a final member function.

-- Ex 2-13 Creating an Object Type as NOT FINAL with a FINAL Member Function 
DROP TYPE person_typ FORCE;
-- above necessary if you have previously created object

CREATE OR REPLACE TYPE person_typ AS OBJECT (
   idno           NUMBER,
   name           VARCHAR2(30),
   phone          VARCHAR2(20),
   FINAL MAP MEMBER FUNCTION get_idno RETURN NUMBER)
NOT FINAL;
/

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 to FINAL example, not sample schema
ALTER TYPE person_typ FINAL;
DROP TYPE person_typ FORCE;

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

Creating Subtypes

You create a subtype using a CREATE TYPE statement that specifies the immediate parent of the subtype with the UNDER keyword.

-- EX 2-14 Creating the parent or supertype
DROP TYPE person_typ FORCE;
-- if created
CREATE OR REPLACE TYPE person_typ AS OBJECT (
 idno           NUMBER,
 name           VARCHAR2(30),
 phone          VARCHAR2(20),
 MAP MEMBER FUNCTION get_idno RETURN NUMBER,
 MEMBER FUNCTION show RETURN VARCHAR2)
 NOT FINAL;
/
 
CREATE OR REPLACE TYPE BODY person_typ AS
 MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
 BEGIN
   RETURN idno;
 END;
-- function that can be overriden by subtypes
 MEMBER FUNCTION show RETURN VARCHAR2 IS
 BEGIN
   RETURN 'Id: ' || TO_CHAR(idno) || ', Name: ' || name;
 END;
 
END;
/

-- Ex. 2-15 Creating a Subtype Using the UNDER Clause
-- requires Ex. 2-14
CREATE TYPE student_typ UNDER person_typ (
   dept_id NUMBER,
   major VARCHAR2(30),
   OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2)
   NOT FINAL;
/
 
CREATE TYPE BODY student_typ AS
 OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2 IS
 BEGIN
    RETURN (self AS person_typ).show || ' -- Major: ' || major ;
 END;
 
END;
/

(SELF AS person_typ).show

-- Ex. 2-16 Using Generalized Invocation
-- Requires Ex. 2-14 and 2-15
DECLARE
 myvar student_typ := student_typ(100, 'Sam', '6505556666', 100, 'Math');
 name VARCHAR2(100);
BEGIN
 name := (myvar AS person_typ).show; --Generalized invocation
END;
/ 

-- Ex. 2-17 Using Generalized Expression
-- Requires Ex. 2-14 and 2-15
DECLARE
 myvar2 student_typ := student_typ(101, 'Sam', '6505556666', 100, 'Math');
 name2 VARCHAR2(100);
BEGIN
 name2 := person_typ.show((myvar2 AS person_typ)); -- Generalized expression
END;
/ 

-- Ex. 2-18 Create employee_typ as subtype
-- requires Ex. 2-14

DROP TYPE employee_typ FORCE;
-- if previously created
CREATE OR REPLACE TYPE employee_typ UNDER person_typ (
    emp_id NUMBER, 
    mgr VARCHAR2(30),
    OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2);
/

CREATE OR REPLACE TYPE BODY employee_typ AS
  OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2 IS
  BEGIN
    RETURN (SELF AS person_typ).show|| ' -- Employee Id: ' 
           || TO_CHAR(emp_id) || ', Manager: ' || mgr ;
  END;
  
END;
/

-- Ex. 2-19 Create part_time_student_typ as subtype
-- Requires Ex. 2-14, 2-15
CREATE TYPE part_time_student_typ UNDER student_typ (
  number_hours NUMBER,
  OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2);
/

CREATE TYPE BODY part_time_student_typ AS
  OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2 IS
  BEGIN
    RETURN (SELF AS person_typ).show|| ' -- Major: ' || major ||
           ', Hours: ' || TO_CHAR(number_hours);
  END;
  
END;
/

-- Ex. 2-20 Inserting Values into Substitutable Rows of an Object Table 
-- Requires Ex. 2-14, 2-15, 2-18, and 2-19
CREATE TABLE person_obj_table OF person_typ;

INSERT INTO person_obj_table 
  VALUES (person_typ(12, 'Bob Jones', '650-555-0130'));

INSERT INTO person_obj_table 
  VALUES (student_typ(51, 'Joe Lane', '1-650-555-0140', 12, 'HISTORY'));

INSERT INTO person_obj_table 
  VALUES (employee_typ(55, 'Jane Smith', '1-650-555-0144', 
                       100, 'Jennifer Nelson'));

INSERT INTO person_obj_table  
  VALUES (part_time_student_typ(52, 'Kim Patel', '1-650-555-0135', 14,
         'PHYSICS', 20));

-- example of show function
SELECT p.show() FROM person_obj_table p;
DROP TYPE employee_typ FORCE;

Declaring Types and Methods NOT INSTANTIABLE

Types and methods can be declared NOT INSTANTIABLE when they are created.

• NOT INSTANTIABLE Types

  If a type is not instantiable, you cannot instantiate instances of that type. There are no constructors (default or user-defined) for it. You might use this with types intended to serve solely as supertypes from which specialized subtypes are instantiated.

• NOT INSTANTIABLE Methods

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

A type that contains a non-instantiable method must itself be declared not instantiable, as shown in Example 2-21.

-- Ex. 2-21 Creating an Object Type that is NOT INSTANTIABLE

DROP TYPE person_typ FORCE;
-- if previously created
CREATE OR REPLACE TYPE person_typ AS OBJECT (
  idno           NUMBER,
  name           VARCHAR2(30),
  phone          VARCHAR2(20),
  NOT INSTANTIABLE MEMBER FUNCTION get_idno RETURN NUMBER)
  NOT INSTANTIABLE NOT FINAL;/

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. In the following example, the ALTER TYPE statement makes person_typ instantiable:

-- Ex. 2-22 Altering an Object Type to INSTANTIABLE
CREATE OR REPLACE TYPE person_typ AS OBJECT (
  idno           NUMBER,
  name           VARCHAR2(30),
  phone          VARCHAR2(20))
  NOT INSTANTIABLE NOT FINAL;/
ALTER TYPE person_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.

Overloading and Overriding Methods

A subtype can redefine methods it inherits, and it can also add new methods, including methods with the same name.

See the examples in "Creating Subtypes" and Example 8-10.

Dynamic Method Dispatch

Dynamic method dispatch refers to the way that method calls are dispatched to the nearest implementation at run time, working up the type hierarchy from the current or specified type. This feature is only available when overriding member methods and does not apply to static methods.

With method overriding, a type hierarchy can define multiple implementations of the same method. In the following hierarchy of types ellipse_typ, circle_typ, and sphere_typ, each type might define a calculate() method differently.

Figure 2-2 Hierarchy of Types

Description of "Figure 2-2 Hierarchy of Types"

When one of these methods is invoked, the type of the object instance that invokes it determines 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.

The method call works up the type hierarchy: never down. 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.

Substituting Types in a Type Hierarchy

When you work with types in a type hierarchy, sometimes you need to work at the most general level, for example, to select or update all persons. But at other times, you need to select or update only a specific subtype such as a student, 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 subtype 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 variable or column whose declared type is the supertype.

In general, types are substitutable. Object attributes, collection elements and REFs are substitutable. An attribute defined as a REF, type, or collection of type person_typ can hold a REF to an instance of, or instances of an instance of person_typ, or an instance of any subtype of person_typ.

This seems expected, given that a subtype is, after all, just a specialized kind of one 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.

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. See "Turning Off Substitutability in a New Table" and "Constraining Substitutability".

Column and Row Substitutability

Object type columns and object-type rows in object tables are substitutable, and so are views: a column or row of a specific type can contain instances of that type and any of its subtypes.

For example, consider the person_typ type hierarchy such as the one introduced in Example 2-14. You can create an object table of person_typ that contains rows of all types. To do this, you insert an instance of a given type into an object table using the constructor for that type in the VALUES clause of the INSERT statement as shown in Example 2-20.

Similarly, Example 2-23 shows that a substitutable column of type person_typ can contain instances of all three types, in a relational table or view. The example recreates person, student, and part-time student objects from that type hierarchy and inserts them into the person_typ column contact.

-- Ex. 2-23 Inserting Values into Substitutable Columns of a Table
DROP TYPE person_typ FORCE;
-- if previously created

DROP TYPE student_typ FORCE; -- if previously created

DROP TYPE part_time_student_typ FORCE; -- if previously created
DROP TABLE contacts; if previously created
CREATE OR REPLACE TYPE person_typ AS OBJECT (
  idno           NUMBER,
  name           VARCHAR2(30),
  phone          VARCHAR2(20))
  NOT FINAL;/
CREATE TYPE student_typ UNDER person_typ (
    dept_id NUMBER,
    major VARCHAR2(30))
    NOT FINAL;
/
CREATE TYPE part_time_student_typ UNDER student_typ (
  number_hours NUMBER);
/
CREATE TABLE contacts (
  contact         person_typ,
  contact_date    DATE );

INSERT INTO contacts 
  VALUES (person_typ (12, 'Bob Jones', '650-555-0130'), '24 Jun 2003' );

INSERT INTO contacts 
  VALUES (student_typ(51, 'Joe Lane', '1-650-555-0178', 12, 'HISTORY'),
         '24 Jun 2003' );

INSERT INTO contacts 
  VALUES (part_time_student_typ(52, 'Kim Patel', '1-650-555-0190', 14,
          'PHYSICS', 20), '24 Jun 2003' );

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.

In general, you can access attributes using dot notation. To access attributes of a subtype of a row or column's declared type, you can use the TREAT function. For example:

-- Using TREAT to access subtype attributes
SELECT TREAT(contact AS student_typ).major FROM contacts;

See "TREAT".

-- Ex. 2-24 Using OBJECT_VALUE and OBJECT_ID  
-- Requires Ex. 2-22

DROP TABLE person_obj_table; -- required if previously created
CREATE TABLE person_obj_table OF person_typ;

INSERT INTO person_obj_table
  VALUES (person_typ(20, 'Bob Jones', '650-555-0130'));

SELECT p.object_id, p.object_value FROM person_obj_table p;
DROP TABLE person_obj_table;
DROP TYPE part_time_student_typ FORCE;

--Ex 2-25 Creating a Subtype with a Supertype Attribute
-- requires Ex 2-22
CREATE TYPE student_typ UNDER person_typ (
    dept_id   NUMBER,
    major     VARCHAR2(30),
    advisor   person_typ);
/
DROP TYPE student_typ FORCE;
DROP TYPE person_typ FORCE;

-- not to be executed
CREATE TYPE subcategory_ref_list_typ
  AS TABLE OF REF category_typ; 
/ 

CREATE TYPE composite_category_typ
  UNDER category_typ 
    ( 
      subcategory_ref_list subcategory_ref_list_typ 
...

Storing Newly Created Subtypes in Substitutable Columns

If you create a subtype, any table that already has substitutable columns of the supertype can 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 do not violate table limits or constraints.

The following example creates a person_typ and then shows several attempts to create a subtype student_typ under person_typ.

-- Ex. 2-27 Creating a Subtype After Creating Substitutable Columns
DROP TYPE person_typ FORCE;
DROP TABLE person_obj_table;
DROP TYPE student_typ; 
-- perform above drops if objects/tables created
CREATE OR REPLACE TYPE person_typ AS OBJECT (
  idno           NUMBER,
  name           VARCHAR2(30),
  phone          VARCHAR2(20))
  NOT FINAL;/

CREATE TABLE person_obj_table (p person_typ);

The following statement fails because student_typ has a supertype attribute, and table person_obj_table has a substitutable column p of the supertype.

-- Incorrect Create student_typ subtype example
CREATE TYPE student_typ UNDER person_typ ( -- incorrect CREATE subtype
    advisor person_typ);
/

The next attempt succeeds. This version of the student_typ subtype is substitutable. Oracle Database automatically enables table person_obj_table to store instances of this new type.

-- Create student_typ subtype example, not sample schema
CREATE TYPE student_typ UNDER person_typ (
    dept_id NUMBER,
    major VARCHAR2(30));/
INSERT INTO person_obj_table 
  VALUES (student_typ(51, 'Joe Lane', '1-650-555-0178', 12, 'HISTORY'));

Dropping Subtypes After Creating Substitutable Columns

When you drop a subtype with the VALIDATE option, it checks that no instances of the subtype are stored in any substitutable column of the supertype. If there are no such instances, the DROP operation completes.

The following statement fails because an instance of student_typ is stored in substitutable column p of table person_obj_table:

-- Incorrect Drop Type example, not sample schema
DROP TYPE student_typ VALIDATE -- incorrect: an instance still exists ; 

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

-- Delete from table and drop student_typ subtype example, not sample schema
DELETE FROM person_obj_table WHERE p IS OF (student_typ); 

DROP TYPE student_typ VALIDATE;
DROP TABLE person_obj_table FORCE;

Turning Off Substitutability in a New Table

When you create a table, 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 the column office of a relational table to storing only office_typ instances and disallows any subtype instances:

-- Ex. 2-28 Turning off Substitutability When Creating a Table
-- Requires Ex. 2.27

DROP TYPE location_typ FORCE; -- required if previously created
DROP TYPE office_typ FORCE; -- required if previously created
CREATE OR REPLACE TYPE location_typ AS OBJECT (
  building_no  NUMBER,
  city         VARCHAR2(40) );
/

CREATE TYPE people_typ AS TABLE OF person_typ;
/
CREATE TYPE office_typ AS OBJECT (
  office_id    VARCHAR(10),
  location     location_typ,
  occupant     person_typ )
  NOT FINAL;/

CREATE TABLE dept_office (
  dept_no      NUMBER,
  office       office_typ)
  COLUMN office NOT SUBSTITUTABLE AT ALL LEVELS;

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

-- Create table NOT SUBSTITUABLE

DROP TABLE office_tab; -- if previously created
CREATE TABLE office_tab OF office_typ
  NOT SUBSTITUTABLE AT ALL LEVELS;

The clause can also turn off substitutability in a particular column, that is, for a particular attribute of the object type of the table:

-- Create table NOT SUBSTITUABLE for a specific column
DROP TABLE office_tab; -- if previously created
CREATE TABLE office_tab OF office_typ
  COLUMN occupant NOT SUBSTITUTABLE AT ALL LEVELS;

You can specify that the element type of a collection is not substitutable using syntax such as the following:

-- Create table NOT SUBSTITUABLE for a collection type
DROP TABLE people_tab;
-- required if previously created
CREATE TABLE people_tab (
    people_column people_typ )
    NESTED TABLE people_column 
      NOT SUBSTITUTABLE AT ALL LEVELS STORE AS people_column_nt;

There is no mechanism to turn off substitutability for REF columns.

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

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.

The following statement creates a table of office_typ in which occupants are constrained to just those persons who are employees:

-- Ex. 2-29 Constraining Substitutability When Creating a Table -- Requires 2-14 and 2-18
DROP TABLE office_tab;
-- if previously created
CREATE TABLE office_tab OF office_typ
  COLUMN occupant IS OF (ONLY employee_typ);

Although the type office_typ allows authors to be of type person_typ, the column declaration imposes a constraint to store only instances of employee_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.

Modifying Substitutability

In an existing table, you can change an object column from SUBSTITUTABLE to NOT SUBSTITUTABLE (or from NOT SUBSTITUTABLE to SUBSTITUTABLE) by using an ALTER TABLE statement. To do so, you specify the clause [NOT] SUBSTITUTABLE AT ALL LEVELS for the particular column.

You can modify substitutability only for a specific column, not for an object table as a whole.

The following statement makes the column office substitutable:

-- Ex. 2-30 Modifying Substitutability in a Table
-- Requires Ex. 2-28
ALTER TABLE dept_office
  MODIFY COLUMN office SUBSTITUTABLE AT ALL LEVELS;  

The following statement makes the column not substitutable. Notice that it also uses the FORCE keyword. This keyword causes any hidden columns containing typeid information or data for subtype attributes to be dropped:

-- Alter table subsitutability with FORCE 
ALTER TABLE  dept_office
  MODIFY COLUMN office NOT SUBSTITUTABLE AT ALL LEVELS FORCE;
DROP TABLE dept_office;

If you do not use the FORCE keyword to make a column not substitutable, the column and all attributes of the type must be FINAL or the ALTER TABLE statement will fail.

A VARRAY column can be modified from SUBSTITUTABLE to NOT SUBSTITUTABLE only if the element type of the varray is final itself and has no embedded types (in its attributes or in their attributes, and so on) that are not final.

See "Hidden Columns for Substitutable Columns and Object Tables" for more information about hidden columns for typeids and subtype attributes.

Restrictions on Modifying Substitutability

You can change the substitutability of only one column at a time with an ALTER TABLE statement. To change substitutability for multiple columns, you must issue multiple statements.

In an object table, you can only modify substitutability for a column if substitutability was not explicitly set at the table level, when the table was created.

For example, the following attempt to modify substitutability for column address succeeds because substitutability has not been explicitly turned on or off at the table level in the CREATE TABLE statement:

-- Alter table with subsitutability not set 
DROP TABLE office_tab;
-- if previously created
CREATE TABLE office_tab OF office_typ;

ALTER TABLE office_tab
  MODIFY COLUMN occupant NOT SUBSTITUTABLE AT ALL LEVELS FORCE;

However, in the following example, substitutability is explicitly set at the table level, so the attempt to modify the setting for column address fails:

-- Incorrect Alter table with subsitutability explicitly set 
DROP TABLE office_tab;
-- if previously created
CREATE TABLE office_tab OF office_typ
  NOT SUBSTITUTABLE AT ALL LEVELS;

/* Following SQL statement generates an error: */
ALTER TABLE office_tab 
  MODIFY COLUMN occupant SUBSTITUTABLE AT ALL LEVELS FORCE  -- incorrect ALTER;

A column whose substitutability is already constrained by an IS OF type operator cannot have its substitutability modified with a [NOT] SUBSTITUTABLE AT ALL LEVELS clause. See "Constraining Substitutability" for information about IS OF type.

Assignments Across Types

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

DROP TYPE person_typ FORCE;
-- if previously created
CREATE TYPE person_typ AS OBJECT (
 idno           NUMBER,
 name           VARCHAR2(30),
 phone          VARCHAR2(20))
 NOT FINAL;
/
DROP TYPE employee_typ FORCE; -- if previously created
CREATE TYPE employee_typ UNDER person_typ (
   emp_id NUMBER, 
   mgr VARCHAR2(30));
/
-- PL/SQL assignment example
DECLARE
  var1 person_typ;
  var2 employee_typ;
BEGIN
  var2 := employee_typ(55, 'Jane Smith', '1-650-555-0144', 100, 'Jennifer Nelson');
  var1 := var2;
END;
/

-- Ex. 2-32 Create collection person_set 
-- Requires 2-21
DROP student_typ;
-- if previously created
CREATE TYPE student_typ UNDER person_typ (
    dept_id NUMBER,
    major VARCHAR2(30))
  NOT FINAL;
/
CREATE TYPE person_set AS TABLE OF person_typ;
/

CREATE TYPE student_set AS TABLE OF student_typ;
/

-- PL/SQL collection assignment
DECLARE
  var1 person_set; 
  var2 student_set;
  elem1 person_typ; 
  elem2 student_typ;
BEGIN
--  var1 := var2;   /* ILLEGAL - collections not of same type */
  var1 := person_set (elem1, elem2);   /* LEGAL : Element is of subtype */
END;
/
DROP TYPE student_set FORCE;
DROP TYPE person_set FORCE;

CAST

-- following needed to run subsequent examples
CREATE TABLE contacts (
  contact         person_typ,
  contact_date    DATE );
INSERT INTO contacts 
  VALUES (person_typ (12, 'Bob Jones', '650-555-0130'), '24 Jun 2003' );
INSERT INTO contacts 
  VALUES (student_typ(51, 'Joe Lane', '1-650-555-0178', 12, 'HISTORY'),
         '24 Jun 2003' );
INSERT INTO contacts 
  VALUES (part_time_student_typ(52, 'Kim Patel', '1-650-555-0135', 14,
          'PHYSICS', 20), '24 Jun 2003' );
CREATE TABLE person_obj_table OF person_typ;
INSERT INTO person_obj_table 
  VALUES (person_typ(12, 'Bob Jones', '650-555-0130'));
INSERT INTO person_obj_table 
  VALUES (student_typ(51, 'Joe Lane', '1-650-555-0178', 12, 'HISTORY'));
INSERT INTO person_obj_table 
  VALUES (employee_typ(55, 'Jane Smith', '1-650-555-0144', 
                       100, 'Jennifer Nelson'));
INSERT INTO person_obj_table  
  VALUES (part_time_student_typ(52, 'Kim Patel', '1-650-555-0135', 14,
         'PHYSICS', 20));
CREATE TABLE contacts_ref (
  contact_ref   REF person_typ SCOPE IS person_obj_table,
  contact_date  DATE );
INSERT INTO contacts_ref
  SELECT REF(p), '26 Jun 2003'
    FROM person_obj_table p
    WHERE p.idno = 51;
INSERT INTO contacts_ref
  SELECT REF(p), '26 Jun 2003'
    FROM person_obj_table p
    WHERE p.idno = 12;

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

-- Using the CAST Function example, not sample schema
CREATE TYPE person_list_typ AS TABLE OF person_typ;/

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

For more information about the SQL CAST function, Oracle Database SQL Language Reference.

CURSOR

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

For more information about the SQL CURSOR expression, see Oracle Database SQL Language Reference.

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_typ objects from the table contact_ref.

-- Using the DEREF Function example, not sample schema
SELECT DEREF(c.contact_ref), c.contact_date 
  FROM contacts_ref c;

See "Dereferencing REFs". For more information about the SQL DEREF function, see Oracle Database SQL Language Reference.

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

-- Using the IS OF type Operator to query Value of a subtype 
SELECT VALUE(p) 
  FROM person_obj_table p
WHERE VALUE(p) IS OF (student_typ);

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

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

-- Using the IS OF type Operator to query for multiple subtypes
SELECT REF(p) 
  FROM person_obj_table p
WHERE VALUE(p) IS OF (employee_typ, student_typ);

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

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

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

-- Using the IS OF type Operator to query for specific subtype only
SELECT TREAT(VALUE(p) AS student_typ)
  FROM person_obj_table p 
WHERE VALUE(p) IS OF(ONLY student_typ);

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

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

-- Using the IS OF type Operator with DEREF 
SELECT * 
  FROM contacts_ref
WHERE DEREF(contact_ref) IS OF (student_typ);

For more information about the SQL IS OF type condition, see Oracle Database SQL Language Reference.

REF

The REF function in a SQL statement takes as an argument a correlation name (or table alias) for an object table or view and returns a reference (a REF) to an object instance from that table or view. The REF function may return references to objects of the declared type of the table, view, or any of its subtypes. For example, the following statement returns the references to all persons, including references to students and employees, whose idno attribute is 12:

-- Using the REF Function example, not sample schema
SELECT REF(p) 
  FROM person_obj_table p
  WHERE p.idno = 12;

For more information about the SQL REF function, see Oracle Database SQL Language Reference.

SYS_TYPEID

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

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

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

The syntax of the function is:

SYS_TYPEID(object_type_value)

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

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

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

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

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

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

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

-- Using the SYS_TYPEID Function example, not sample schema
SELECT name, SYS_TYPEID(VALUE(p)) typeid FROM person_obj_table p;

See "Hidden Columns for Substitutable Columns and Object Tables" for information about the type-discriminant and other hidden columns. For more information about the SQL SYS TYPEID function, see Oracle Database SQL Language Reference.

TABLE()

Table functions are functions that produce a collection of rows, a nested table or a varray, that can be queried like a physical database table or assigned to a PL/SQL collection variable. You can use a table function like the name of a database table, in the FROM clause of a query, or like a column name in the SELECT list of a query.

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

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

For information on TABLE() functions, see Oracle Database Data Cartridge Developer's Guide and Oracle Database PL/SQL Language Reference.

TREAT

The TREAT function does a runtime check to confirm that an expression can be operated on as if it were of a different specified type in the hierarchy, normally a subtype of the declared type of the expression. In other words, the function attempts to treat a supertype instance as a subtype instance, for example, to treat a person as a student. If the person is a student, then the person is returned as a student, with the additional attributes and methods that a student may have. If the person is not a student, TREAT returns NULL in SQL.

The two main uses of TREAT are:

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

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

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

-- Using the TREAT Function to Return a Specific Subtype in a Query example,
-- not sample schema
SELECT TREAT(VALUE(p) AS student_typ)
  FROM person_obj_table p;

-- Using the TREAT Function to modify declared type of a REF example,
-- not sample schema
SELECT TREAT(REF(p) AS REF student_typ)
  FROM person_obj_table p;

-- Using the TREAT Function to Access Attributes of a Specific Subtype example,
-- not sample schema
SELECT name, TREAT(VALUE(p) AS student_typ).major major 
  FROM person_obj_table p;

-- Incorrect use of the TREAT Function to Access Attributes example,
-- not sample schema
SELECT name, VALUE(p).major major FROM person_obj_table p -- incorrect;

-- PL/SQL example, not sample schema
DECLARE 
  var person_typ; 
BEGIN 
  var := employee_typ(55, 'Jane Smith', '1-650-555-0144', 100, 'Jennifer Nelson');
  DBMS_OUTPUT.PUT_LINE(TREAT(var AS employee_typ).mgr);
END;
/

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. The VALUE function may return instances of the declared type of the row or any of its subtypes.

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

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

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

-- Using the VALUE Function to Retrieve part time students example
-- not sample schema, uses person_typ
SELECT VALUE(p) FROM person_obj_table p 
  WHERE VALUE(p) IS OF (ONLY part_time_student_typ);

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

-- using the VALUE function to update
UPDATE person_obj_table p
   SET VALUE(p) = person_typ(12, 'Bob Jones', '1-650-555-0130')
   WHERE p.idno = 12;
SELECT VALUE(p) FROM person_obj_table p;

See also Example 5-22, "Using VALUE to Update a Nested Table". For more information about the SQL VALUE function, see Oracle Database SQL Language Reference.

-- clean up for example testing
DROP TABLE person_obj_table;
DROP TABLE contacts;
DROP TABLE contacts_ref;
DROP TYPE person_list_typ FORCE;
DROP TYPE employee_typ FORCE;
DROP TYPE part_time_student_typ FORCE;
DROP TYPE student_typ FORCE;
DROP TYPE person_typ FORCE;
SPOOL OFF
COMMIT;