[ Team LiB ] Previous Section Next Section

1.16 Oracle's Object-Oriented Features

In Oracle, an object type combines attributes (data structures) and methods (functions and procedures) into a single programming construct. The object type construct allows programmers to define their own reusable datatypes for use in PL/SQL programs and table and column definitions. An object type must be created in a database before it can be used in a PL/SQL program.

An instance of an object type is an object in the same way that a variable is an instance of a scalar type. Objects are either persistent (stored in the database) or transient (stored only in PL/SQL variables). Objects can be stored in a database as a row in a table (a row object) or as a column in a table. A table of row objects can be created with syntax such as this: 

CREATE TABLE table_name OF object_type;

When stored in such a table, the object (row) has an OID (Object IDentifier) that is unique throughout the database.

1.16.1 Object Types

An object type has two parts: the specification and the body. The specification is required and contains the attributes and method specifications. The syntax for creating the object type specification is: 

CREATE [OR REPLACE] TYPE obj_type_name 
[AUTHID { CURRENT_USER | DEFINER } ] 
{ { IS | AS } OBJECT | UNDER parent_type_name }
(
   attribute_name datatype,...,
   [ [ [NOT] OVERRIDING ] [ {NOT] FINAL ] [ {NOT} 
   INSTANTIABLE ] method_spec,...,]
   [PRAGMA RESTRICT_REFERENCES(program_name, purities)]
)
[ [NOT] FINAL ]
[ [NOT] INSTANTIABLE ];

Where method_spec is one of the following:

MEMBER { PROCEDURE | FUNCTION } program_spec

or:

STATIC { PROCEDURE | FUNCTION } program_spec

or:

{ ORDER | MAP } MEMBER FUNCTION comparison_function_spec

or:

CONSTRUCTOR FUNCTION constructor_function_spec

Attribute specifications must appear before method specifications. Object attributes, like table columns, are defined with a name and a datatype. The name can be any legal identifier, and the datatype can be almost any datatype known to SQL other than LONG, LONG RAW, ROWID, and UROWID. Attributes can be declared on other programmer-defined object types or collection types, but not on the Oracle9i types ANYTYPE, ANYDATA, or ANYDATASET. Attributes cannot be of datatypes unique to PL/SQL, such as BOOLEAN.

Method headers appear in the object type specification in a comma-delimited list. Unlike in a package specification, commas (not semicolons) terminate the object type program specifications. To support object comparisons and sorting, the type can optionally include one comparison method—either ORDER or MAP. Member methods can be overloaded in object types following the same rules as function and procedure overloading in packages.

Method "specs" that appear above in the syntax can actually be call specs for Java classes in the database or for external procedures written in C.

The syntax for creating the object type body is:

CREATE [OR REPLACE] TYPE BODY obj_type_name 
{ IS | AS } 
(
   [ { ORDER | MAP } MEMBER FUNCTION
       comparison_function_body; ]
   [ { MEMBER | STATIC } { FUNCTION | PROCEDURE }
       program_body;]...
)
;

Again, the program bodies can be call specs to Java or C programs. The keywords CONSTRUCTOR, UNDER, FINAL, and INSTANTIABLE are all new with Oracle9i.

1.16.2 Type Inheritance (Oracle9i)

Beginning with Oracle9i, you can define subtypes of object types following a single-inheritance model. Oracle does not have a master root-level object type of the kind that you might find in other object programming models; instead; each type is "standalone" unless declared otherwise.

The UNDER keyword specifies that the type exists as a subtype in a hierarchy. When you are using UNDER, the parent type must be marked NOT FINAL. By default, types are FINAL, meaning that you cannot declare a subtype of that type.

A subtype contains all of the attributes and methods of its parent (supertype) and may contain additional attributes and methods. Methods can override corresponding methods from the parent. Changes to the supertype—such as the addition of attributes or methods—are automatically reflected in the subtypes.

By default, object types are INSTANTIABLE—that is, an invoking program may create an object of that type. The phrase NOT INSTANTIABLE tells Oracle that you don't want any objects of the type, in which case Oracle will not create a constructor for it. This variation generally makes sense only with types that will serve as parents of other types.

1.16.3 Methods

There are four kinds of methods: member, static, constructor, and comparison.

1.16.3.1 Member methods

A member method is a procedure or function designated with the keyword MEMBER. Calling programs may invoke such a method only on objects that have been instantiated.

1.16.3.2 Static methods

A static method has no access to a current (SELF) object. Such a method is declared using the keyword STATIC and can be invoked at any time using type.method syntax.

1.16.3.3 Constructor methods

Even if you don't declare any methods, every instantiable object has a default constructor method which allows a calling program to create new objects of that type. This built-in method:

  • Has the same name as the object type

  • Is a function that returns an object of that type

  • Accepts attributes in named or positional notation

  • Must be called with a value (or NULL) for every attribute—there is no DEFAULT clause for object attributes

  • Cannot be modified

Oracle9i programmers can replace this default constructor with their own using the CONSTRUCTOR FUNCTION syntax. This method must have the same name as the object type, but there are no restrictions on its parameter list. The RETURN clause of the constructor's header must be RETURN SELF AS RESULT. Oracle supports the overloading of programmer-defined constructors. All non-static methods have the implied parameter SELF, which refers to the current instance of the object. The default mode for the SELF parameter is IN for functions and IN OUT for procedures. A programmer can alter the mode by explicitly including SELF in the formal parameter list.

1.16.3.4 Comparison methods

The comparison methods, ORDER and MAP, establish ordinal positions of objects for comparisons such as "<" or "between" and for sorting (ORDER BY, GROUP BY, DISTINCT). Oracle invokes a comparison method automatically whenever it needs to perform such an operation.

MAP and ORDER methods are actually special types of member methods—that is, they only execute in the context of an existing object. An ORDER function accepts two parameters: SELF and another object of the same type. It must return an INTEGER value as explained in the following table:

Return value

Object comparison

Any negative integer (commonly -1)

SELF < second object

0

SELF = second object

Any positive integer (commonly 1)

SELF > second object

NULL

Undefined comparison: attributes needed for the comparison are NULL

For example, the Senate ranks majority party members higher than non-majority party members and within the majority (or non-majority) by years of service. Here is an example ORDER function incorporating these rules: 

CREATE TYPE senator_t AS OBJECT (
   majority boolean_t,
   yrs_service NUMBER,
   ORDER MEMBER FUNCTION ranking (other IN 
      senator_t)
      RETURN INTEGER  );

CREATE OR REPLACE TYPE BODY senator_t AS 
   ORDER MEMBER FUNCTION ranking (other IN 
      senator_t)
      RETURN INTEGER
   IS
   BEGIN
      IF SELF.majority.istrue(  ) 
         AND other.majority.istrue(  )
      THEN
         RETURN SIGN(SELF.yrs_service -
            other.yrs_service);
      ELSIF SELF.majority.istrue(  ) 
         AND other.majority.isfalse(  )
      THEN
         RETURN 1;
      ELSIF SELF.majority.isfalse(  ) 
         AND other.majority.istrue(  )
      THEN
         RETURN -1;
      ELSIF SELF.majority.isfalse(  ) 
         AND other.majority.isfalse(  )
      THEN
         RETURN SIGN(SELF.yrs_service - 
            other.yrs_service);
      END IF;
   END ranking;
END;

A MAP function accepts no parameters and returns a scalar datatype such as DATE, NUMBER, or VARCHAR2 for which Oracle already knows a collating sequence. The MAP function translates, or maps, each object into this scalar datatype space.

If no ORDER or MAP function exists for an object type, SQL, but not PL/SQL, supports only limited equality comparisons of objects. Objects are equal if they are of the same object type and if each attribute is equal.

Use MAP if possible when frequently sorting or comparing a large number of objects, as in a SQL statement; an internal optimization reduces the number of function calls. With ORDER, the function must run once for every comparison.

1.16.4 Methods in Subtypes (Oracle9i)

The method modifiers OVERRIDING, FINAL, and NOT INSTANTIABLE specify how method overriding works in the subtype:

OVERRIDING

Tells Oracle that the subtype's method will override the supertype's method.

FINAL

Tells Oracle that new subtypes may not override this method.

NOT INSTANTIABLE

Tells Oracle that this method is not available in the subtype.

As you can imagine, certain combinations of these modifiers are disallowed.

Oracle9i supports dynamic method dispatch to determine which overridden method to invoke at runtime. That is, it will choose the method in the most specific subtype associated with the currently instantiated object.

1.16.5 Manipulating Objects in PL/SQL and SQL

Variables declared as objects begin their life atomically null, meaning that the expression: 

object IS NULL

evaluates to TRUE. Attempting to assign values to the attributes of an atomically null object will return an ACCESS_INTO_NULL exception. Instead, you must initialize the object, in one of these ways:

  • Use either the default constructor method or a user-defined constructor

  • Assign to it the value of an existing object

  • Use SELECT INTO or FETCH INTO

Here is an example using each initialization technique:

DECLARE
   project_boiler_plate  project_t;
   build_web_site        project_t;

   -- Initialize via constructor.
   new_web_mgr  proj_mgr_t := 
         proj_mgr_t('Ruth', 'Home Office');

   -- Initialize via Oracle9i user-defined constructor 
   -- that provides defaults
   new_web_mgr proj_mgr_t := NEW proj_mgr_t(  );

   CURSOR template_cur IS
      SELECT VALUE(proj)
        FROM projects
       WHERE project_type = 'TEMPLATE'
         AND  sub_type = 'WEB SITE';
   BEGIN
      OPEN template_cur;
      -- Initialize via FETCH INTO.
      FETCH template_cur
         INTO project_boiler_plate;

      -- Initialize via assignment.
      build_web_site := project_boiler_plate;
      ...

After an object is initialized, it can be stored in the database, and you can then locate and use that object with the REF, VALUE, and DEREF operators.

1.16.6 Upcasting and Downcasting (Oracle9i)

Oracle9i supports implicit upcasting (widening) of a subtype and provides the TREAT operator to downcast (narrow) a supertype. TREAT can also explicitly upcast a subtype.

Assuming that book_t is a subtype of catalog_item_t, the following example shows both upcasts and downcasts: 

DECLARE
   my_book book_t := NEW book_t(  );
   your_book book_t;
   some_catalog_item catalog_item_t;
BEGIN
   /* An implied upcast */
   some_catalog_item := my_book;

   /* An explicit downcast */
   your_book := TREAT(some_catalog_item AS book_t);
END;

The syntax of TREAT is:

TREAT (object_instance AS [ REF ] type)

where object_instance is a value that is of a particular supertype in an object hierarchy, and type is the name of subtype (or supertype) in the same hierarchy. The TREAT expression won't compile if you attempt to cast a type to another from a different type hierarchy. If you supply an object from the correct type hierarchy, TREAT will return either the casted object or NULL—but not an error.

You can also use dot notation to obtain access to the casted object's attributes and methods: 

TREAT (object_instance AS type).{ attribute |
       method(args...) } ]

SQL also supports TREAT and implied upcasting.

1.16.6.1 REF operator

REF, short for REFerence, designates a datatype modifier or an operator to retrieve a logical pointer to an object. This pointer encapsulates the OID and can simplify navigation among related database objects. The syntax for a REF operator is: 

REF(table_alias_name)

For example:

SELECT REF(p) FROM pets p WHERE ...

A PL/SQL variable can hold a reference to a particular object type:

DECLARE
   petref REF Pet_t;
BEGIN
   SELECT REF(p) INTO petref FROM pets p WHERE ...

Through deletions, REFs can reference a nonexistent object—called a dangling REF—resulting in a state that can be detected with the IS DANGLING predicate. For example: 

UPDATE pets
   SET owner_ref = NULL
 WHERE owner_ref IS DANGLING.

Oracle's built-in package UTL_REF provides programmatic access to stored objects via their REF.

1.16.6.2 VALUE operator

Use the VALUE operator to retrieve a row object as a single object rather than multiple columns. The syntax for the VALUE operator is: 

VALUE(table_alias_name)

For example:

SELECT VALUE(p) FROM pets p WHERE ...
1.16.6.3 DEREF operator

Use the DEREF operator to retrieve the value of an object for which you have a REF. The syntax for DEREF is: 

DEREF(table_alias_name)

For example:

DECLARE
   person_ref   REF person_t;
   author       person_t;
BEGIN
   -- Get the ref. 
   SELECT REF(p) INTO person_ref 
      FROM persons WHERE p.last_name ='Pribyl';

   -- Dereference the pointer back to the value.
   SELECT DEREF(person_ref) INTO author FROM dual;
   ...

In addition, Oracle uses an OID internally as a unique key to each object. As with a ROWID, you don't typically use an OID directly.

The following table shows ways of referencing persistent objects:

Scheme

Description

Applications

OID

An opaque, globally unique handle, produced when the object is stored in the database as a table (row) object.

This is the persistent object's handle; it's what REFs point to. Your program never uses it directly.

VALUE

An operator. In SQL, it acts on an object in an object table and returns the object's contents. Different from the VALUES keyword found in some INSERT statements.

Allows quasi-normalizing of object-relational databases and joining of object tables using dot navigation. In PL/SQL, REFs serve as input/output variables.

REF

A pointer to an object. May be used within a SQL statement as an operator or in a declaration as a type modifier.

Used when fetching a table (row) object into a variable, or when you need to refer to an object table as an object instead of a list of columns.

DEREF

Reverse pointer lookup for REFs.

Used for retrieving the contents of an object when all you know is its REF.

1.16.7 Changing Object Types

You can add methods, but not attributes, to an object type stored in the database using the ALTER TYPE statement. There are several forms of this statement: 

ALTER TYPE typename
   { ADD | MODIFY | DROP } ATTRIBUTE attribute_spec
   { INVALIDATE | CASCADE 
   { [ NOT ] INCLUDING TABLE DATA | CONVERT TO SUBSTITUTABLE } 
   [ FORCE ] };

ALTER TYPE typename
   [ NOT ] { INSTANTIABLE | FINAL }
   { INVALIDATE | CASCADE 
      { [ NOT ] INCLUDING TABLE DATA |
          CONVERT TO SUBSTITUTABLE } 
      [ FORCE ] };

ALTER TYPE typename
   COMPILE [ DEBUG ] [ SPECIFICATION | BODY ]
            [ REUSE SETTINGS ];

Because altering the structure of a type can have quite a few repercussions on database objects, Oracle requires you either to INVALIDATE the dependent objects or to CASCADE the change.

When making a change from FINAL to NOT FINAL and cascading the change, you can cause existing table objects to be either NOT SUBSTITUTABLE (the default) or SUBSTITUTABLE. The following is an example of adding an attribute: 

ALTER TYPE catalog_item_t
   ADD ATTRIBUTE publication_date VARCHAR2(400)
   CASCADE INCLUDING TABLE DATA;

The next example shows adding a method:

ALTER TYPE catalog_item_t
   ADD MEMBER PROCEDURE save,
   CASCADE;

After adding a method to a spec, you would use CREATE OR REPLACE TYPE BODY to implement it in the body (include all the other methods as well).

There are a variety of restrictions on modifying types; for example, you cannot change a type from INSTANTIABLE to NOT INSTANTIABLE if you have created tables that depend on the type.

The syntax for dropping an object type is:

DROP TYPE type_name [FORCE];

You can drop only an object type that has not been implemented in a table (or you can drop the tables first). The FORCE option will drop object types even if they have dependencies, but FORCE will irreversibly invalidate any dependent objects such as tables. FORCE does not do a DROP CASCADE.

If you are dropping a type whose parent type has table dependents, this form of the statement: 

DROP TYPE subtype_name VALIDATE;

will "validate" the safety of dropping the subtype before performing it. That is, Oracle will only perform the drop if there are no objects of the subtype in any substitutable columns of the parent type.

    [ Team LiB ] Previous Section Next Section