O'Reilly Network: Inherit the Database: Oracle9<em>i</em>'s Support for Object Type Inheritance

Published on O'Reilly Network (http://www.oreillynet.com/)
http://www.oreillynet.com/pub/a/network/2002/10/29/Feuerstein.html
See this if you're having trouble printing code examples

Inherit the Database
Oracle9i's Support for Object Type Inheritance

by Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition
10/29/2002

Editor's note: In this article, Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition, offers a quick review of object types and then shows how you can build object-type hierarchies by taking advantage of inheritance.

With the release of Oracle8 (my, does that seem like a long time ago), Oracle became an object-relational database. It supports both a traditional, relational model and one based on object types (known in other languages as classes). Now, it's entirely possible that you did know that because the Oracle8 (and Oracle8i) implementation of these object types or classes was woefully inadequate. In particular, one of the main drawbacks with the first iteration of Oracle's object implementation was its lack of support for "inheritance." Inheritance refers to the ability of an object type to inherit attributes and methods from previously defined object types. As a result, very few development shops ever used object types.

That's all about to change, though. With Oracle9i, Oracle has fixed one of the most glaring problems with object types by adding support for inheritance.

Quick Review of Object Types

Object types consist of a combination of attributes (think "column" from relational tables) and methods (think "procedures" and "functions" from packages). Members are also commonly referred to as methods. To use an object type, you instantiate a specific object from the type. Here's a very simple example of such a process:

Define an object-type specification. A person object type has three attributes: name, weight, and date of birth (defined using the new TIMESTAMP datatype of Oracle9i). It has a single member, a function that returns the age of a person (relying on the new INTERVAL datatype of Oracle9i).
```
CREATE OR REPLACE TYPE person_ot
IS OBJECT 
(
   NAME    VARCHAR2 (100),
   weight  NUMBER,
   dob     TIMESTAMP (3),
 
   MEMBER FUNCTION age 
      RETURN INTERVAL YEAR TO MONTH
);
```

Define an object-type body that contains the implementation of the object-type members.

CREATE OR REPLACE TYPE BODY person_ot
IS 
   MEMBER FUNCTION age 
      RETURN INTERVAL YEAR TO MONTH
   IS
      retval INTERVAL YEAR TO MONTH;
   BEGIN
      RETURN (SYSDATE - SELF.dob) 
         YEAR TO MONTH;
   END;
END;

Instantiate an object and run some code: Notice that I call a constructor function (same name as the object type) to initialize the object. Then, if Steven is more than 18 years old, it's time for him to get a job.

DECLARE
   steven person_ot :=
      person_ot (
         'Steven', 175, '23-SEP-1958');
   poppa person_ot :=
      person_ot (
         'Sheldon', 200, '07-NOV-1929');
BEGIN
   IF steven.age > 
         INTERVAL '18' YEAR 
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'Time for ' || steven.name || 
         ' to get a job!');
   END IF;
END;

Notice that I was also able to instantiate or declare two persons from the same object type: Steven and his father, Sheldon. This is one of the key advantages of object types over packages: packages are static chunks of code. Object types are templates for individual objects to which the members are applied.

What Is Inheritance?

Features of Inheritance in Oracle9i

Here are some of the ways you can take advantage of inheritance with object types:

A subtype can be defined from a supertype either directly or indirectly, through multiple levels of other subtypes.
Oracle supports only single inheritance, which means that a subtype can be derived only from a single supertype. A supertype can have more than one sibling subtype (all subtypes of the same supertype), but a subtype can have no more than one direct parent supertype.
Subtypes inherit all attributes and members from all of their supertypes.
Subtypes can define new attributes and methods. These new elements can be in addition to the inherited ones. They can also override or replace existing methods.
When you call an object-type method, PL/SQL automatically executes the appropriate version of the method, based on the type of the object. This is known as dynamic dispatch or dynamic polymorphism.
You can define an object type (and even an individual member in an object type) to be FINAL, which means that you can't define a subtype from that type (override the member in a subtype). If an object type is NOT FINAL, it can be a supertype of another object type.
You can define an object type as NOT INSTANTIABLE, which means that you won't be able to instantiate objects from the type. Such an object type can only act as a supertype for other types.
You can declare an individual method as NOT INSTANTIABLE, which means that it exists only as a "template" for subtype implementations. You specify, in other words, only the header of the method without providing an implementation.

There are many other nuances in the ways that you can define and use object types, but these concepts will be enough for a single article.

Defining an Object Hierarchy in PL/SQL

Let's take a look at how you set up a hierarchy with Oracle object types. Here's my definition of the root living thing entity:

CREATE OR REPLACE TYPE living_thing_ot 
IS OBJECT (
   species VARCHAR2 (100),
   
   NOT INSTANTIABLE MEMBER 
      PROCEDURE showpoliticalpower
   )
   NOT INSTANTIABLE 
   NOT FINAL;
/

All of my other objects are subtypes of this single object type. It's made up of a single attribute, species, and a single member, the showpoliticalpower method. Here are some of the special characteristics of this object type:

It's NOT INSTANTIABLE, which means that I can't declare an object based on this type. If I try to, as shown here, I'll get an error:

/* Instantiate the un-instantiable */
DECLARE
   squirrel living_thing_ot
      := living_thing_ot ();
BEGIN
   IF squirrel.dob < SYSDATE
   THEN
      DBMS_OUTPUT.put_line (
         'Senior citizen'
      );
   END IF;
END;
/ 

ERROR:
PLS-00713: attempting to instantiate a type that is NOT 
INSTANTIABLE

The living_thing_ot object type can only be used as the starting point for other object types.

It's NOT FINAL, which means that I can declare subtypes from the object type. Note that the combination of NOT INSTANTIABLE and FINAL would result in a thoroughly useless object type.
The showpoliticalpower method is defined as NOT INSTANTIABLE. This means that I won't provide an implementation of this procedure in the living_thing_ot type (There is, in fact, no body for living_thing_ot). Instead, I'm requiring that any object type declared as a subtype of living_thing_ot must provide its own implementation of the procedure. In Java, this is called an abstract method, and living_thing_ot would be called an abstract class. I like that terminology and will use it in the rest of this article.

So let's see how I might take advantage of the living_thing_ot object type. Listing 1 shows the specification for the person type.

Listing 1. Specification of the person type.

CREATE OR REPLACE TYPE person_ot UNDER living_thing_ot
(
   name    VARCHAR2 (100),
   weight  NUMBER,
   dob     TIMESTAMP (3),
 
   -- New methods in object type  
   MEMBER PROCEDURE show,

   MEMBER FUNCTION age RETURN INTERVAL YEAR TO MONTH,
      
   FINAL MEMBER PROCEDURE when_crime_committed, 
     
   MEMBER PROCEDURE showpunishment,

   -- Provide overriding implementation of abstract method.
   OVERRIDING MEMBER PROCEDURE showpoliticalpower
   )
   INSTANTIABLE 
   NOT FINAL;
/

Some observations about the person type:

Instead of specifying the type AS OBJECT, I instead use the UNDER clause to indicate that person_ot is a subtype of living_thing_ot.
Any object declared from this type has a total of four attributes: the species attribute from living_thing_ot, plus the three attributes of person (name, weight, and DOB- date of birth).
This type adds four new methods (show, age, when_crime_committed, and showpunishment) and also overrides the abstract (unimplemented) method of the same name in living_thing_ot.
The person type is declared as INSTANTIABLE and NOT FINAL. This means that I can declare objects based on this object type and I can also define subtypes of person_ot.
The when_crime_committed method is declared as FINAL. This means that if I define a subtype of person_ot, I'm NOT allowed to override when_crime_committed with a new, more specific definition. If I try to do so, I will raise an exception as shown here:
```
CREATE OR REPLACE TYPE immigrant_ot 
   under person_ot (
   from_nation VARCHAR2(200),
      
   OVERRIDING MEMBER PROCEDURE when_crime_committed
   )
   ;
/
ERROR:
PLS-00637: FINAL method cannot be overridden or hidden
```
By marking a method as FINAL, I make sure that whenever that method is executed for an object in the hierarchy, it's always executing the code in which it was finalized.

The FINAL status of an object type can be modified with the ALTER TYPE statement. Suppose, for example, I want to change the company type from FINAL to NOT FINAL. I can issue this statement:
```
ALTER TYPE company_ot NOT FINAL;
```
You can also change a type from NOT FINAL to FINAL with the ALTER TYPE command, but only if the target type has no subtypes.

Implementing the Object Type Body

Let's now take a look at the body of the person type, shown in Listing 2.

Listing 2. Body of person_ot object type.

CREATE OR REPLACE TYPE BODY person_ot
IS 
   MEMBER PROCEDURE show
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE ('Person named ' || SELF.name || 
          ' weighs ' || SELF.weight || 
          ' and was born on ' || SELF.dob);
   END;
   
   -- My wife has been reading Milton's Paradise Lost, so:
   MEMBER PROCEDURE showpunishment
   IS
   BEGIN 
      DBMS_OUTPUT.PUT_LINE ('Leave Garden of Eden');
   END;
   
   FINAL MEMBER PROCEDURE when_crime_committed
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (   '--Suppose '
          || name
          || ' Was Convicted of a Crime.--');
      DBMS_OUTPUT.PUT_LINE (' ');
      show;      
      DBMS_OUTPUT.PUT_LINE (' ');
      DBMS_OUTPUT.PUT_LINE ('Political power?');
      showPoliticalPower;
      DBMS_OUTPUT.PUT_LINE (' ');
      DBMS_OUTPUT.PUT_LINE ('Punishment?');
      showpunishment;
   END;
      
   FINAL MEMBER FUNCTION age RETURN INTERVAL YEAR TO MONTH
   IS
      retval INTERVAL DAY TO SECOND;
   BEGIN
      RETURN (SYSDATE - SELF.dob) YEAR TO MONTH;
   END;
   
   OVERRIDING MEMBER PROCEDURE showpoliticalpower
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE ('The existence of a soul');
   END;
END;
/

From a programming standpoint, there isn't much to note in the person_ot body. It mostly displays information (show displays the characteristics of a person, showpunishment shows the punishment a person receives when he or she commits a crime, and so on). The FINAL when_crime_committed calls a number of other programs (show, showpoliticalpower, showpunishment) and will figure prominently later in our analysis of the way PL/SQL determines which method in the hierarchy to execute.

The age function should catch your attention, as it makes use of the new INTERVAL datatype to compute the difference between two dates. You can define two types of INTERVALS:

DAY TO SECOND: Represent the precise difference between two datetime values.
YEAR TO MONTH: Calculate the difference between two datetime values, where the only significant portions are the year and month.

I'll talk about these new datatypes in more detail in a future article.

Since person_ot is a subtype of living_thing_ot, when I declare a person object, I must provide a total of four values in the constructor. This is shown here:

DECLARE
   steven person_ot :=
      person_ot (
         'HUMAN', 
         'Steven', 
         175, 
         '23-SEP-1958');
BEGIN
   steven.weight := 170;
   steven.showPoliticalPower;
END;

I provide a value for the living_thing_ot attribute, species and my three person- specific attributes. I can then directly address any of the attributes and call my methods.

Reflections on Inheritance

The ability to inherit methods and attributes from supertypes lends much power to object types, but it also introduces a higher level of complexity and dependency. The reason for this is that inheritance isn't a one-time affair in object types.

Any changes made after the initial definition of the hierarchy are reflected in the subtype as well. Unless a subtype overrides an inherited method, it always contains the same set of attributes and methods that are in the accumulation of supertypes in the hierarchy, plus any attributes and methods that the subtype contributes.

From this perspective, it's important to keep in mind that a subtype isn't a different type from any of its supertypes. Instead, it's a more specific kind of that type. If, for example, the characteristics of the employee object-type change, then the definition of the salaried employee changes as well.

This live connection means that you can make a change deep in the hierarchy that automatically affects many object types and the tables and code built around those types. That may be just what you want, but it also means that as you change one level in the hierarchy you need to think through the answers to such questions as:

If you're adding a new method, do you want to allow subtypes to redefine that method? Should all subtypes be required to use the method unchanged?
If the type or its subtypes are referenced in the definition of an object-relational table, do you want it to be available through all DML operations or should access be restricted?

Extending the Hierarchy

There's no limit to the number of levels in one's object-type hierarchy. With the hierarchy described earlier in this article, for example, I'd define my objects as follows (header lines only):

CREATE TYPE living_thing_ot IS OBJECT...
CREATE TYPE person_ot UNDER living_thing_ot...
CREATE TYPE citizen_ot UNDER person_ot...
CREATE TYPE war_criminal_ot UNDER citizen_ot...
CREATE TYPE corporation_ot UNDER person_ot...
CREATE TYPE employee_ot UNDER person_ot...
CREATE TYPE salaried_emp_ot UNDER employee_ot...
CREATE TYPE hourly_emp_ot UNDER employee_ot...
CREATE TYPE manager_ot UNDER salaried_emp_ot...
CREATE TYPE admin_ot UNDER salaried_emp_ot...
CREATE TYPE professional_ot UNDER salaried_emp_ot...

At each stage of the hierarchy, you need to decide:

Should my object type be INSTANTIABLE or NOT INSTANTIABLE? Will this object type, in other words, be used solely as a supertype of specialized subtypes?
Should my object type be FINAL or NOT FINAL? Can this object type be used as a supertype of more specialized subtypes? Or is it the "end of the line" in the hierarchy?

And then within each object type, you should examine your different methods and decide whether you want them to be INSTANTIABLE (which means you can override them in a subtype) or FINAL (they can't be overridden).

If you know that you're going to override a method in every subtype because it's defined differently at every level, there isn't any point in providing an implementation in the supertype. So you declare the method as INSTANTIABLE and compel all subtypes to implement the method.

The INSTANTIABLE status of an object type can be modified with the ALTER TYPE statement. Suppose, for example, I want to change the company type from NOT INSTANTIABLE to INSTANTIABLE. I can issue this statement:

ALTER TYPE company_ot INSTANTIABLE;

You can also change a type from INSTANTIABLE to NOT INSTANTIABLE with the ALTER TYPE command, but only if the target type has no columns, views, tables, or instances that reference that type, either directly, or indirectly through another type or subtype.

Options for Method Definition

You can define or make available methods in object types in the following ways:

Inherit a method: If the method is defined in any supertype in the hierarchy, that method is automatically available for use with the current type. You don't need to write any additional code in the object-type definition.
Create a new method: You can always create an entirely new method in an object type, one that doesn't appear in any supertype. You can, by the way, create STATIC methods that are associated with the object type and not with any object instantiated from the object type.
Overload a supertype method: You can create a method that has the same name as a method in a supertype, but has either a distinct parameter list or a different method type (function vs. procedure, for example). This constitutes an overloading (or static polymorphism) of the supertype method in the subtype.
Override a supertype method: You can also replace or substitute a supertype method in your subtype by specifying OVERRIDING MEMBER in the header definition.

Overloading has, of course, been available for years in PL/SQL. You can overload multiple programs with the same name in packages (the most common location for overloading) and any declaration section in a block.

The interesting aspect of overloading in object types is that you might be overloading a method from a supertype. In this case, you can't actually tell by looking at the subtype definition that what you've done is an overloading. It's the only program with that name present. You have to be able to examine the entire hierarchy to discover the overloading. Consider the object type for corporation shown in Listing 3.

Listing 3. Object type for corporation.

CREATE OR REPLACE TYPE corporation_ot 
   UNDER person_ot (

   CEOcompensation NUMBER,
   layoffs NUMBER,
   
   MEMBER procedure maximizeProfits,
   
   -- Show a corporation
   OVERRIDING MEMBER procedure show ,
   
   -- override of generic method.
   OVERRIDING MEMBER procedure  showPunishment,
   
   -- implementation of non-instantiable method
   OVERRIDING MEMBER procedure showPoliticalPower,
      
   MEMBER FUNCTION age (merger_date_in IN DATE) 
      RETURN INTERVAL YEAR TO MONTH 
   )
   INSTANTIABLE 
   NOT FINAL;
/

I've added an overloading of the age function (the person_ot contains the "original" age program). For corporations, I allow you to specify a merger date. If NOT NULL, then the computation of the age of the corporation changes from the default person age function. Here's a comparison of the two implementations, first the person age function:

MEMBER FUNCTION age 
   RETURN INTERVAL YEAR TO MONTH
IS
   retval INTERVAL YEAR TO MONTH;
BEGIN
   RETURN (SYSDATE - SELF.dob)
     YEAR TO MONTH;
END;

and now the corporation's age function:

MEMBER FUNCTION age (
   merger_date_in IN DATE) 
   RETURN INTERVAL YEAR TO MONTH
IS
   retval INTERVAL YEAR TO MONTH;
BEGIN
   IF merger_date_in > SELF.dob
   THEN
      RETURN (SYSDATE - merger_date_in) 
         YEAR TO MONTH;
   ELSE
      RETURN (SYSDATE - SELF.dob)
         YEAR TO MONTH;
   END IF;
END;

In other words, if a merger date is provided, use that to calculate the age of the corporation. Otherwise, rely on the "date of birth" of the corporation, obtained from the person-defined attribute. I do not include the OVERRRIDING keyword in my definition of this function since it isn't overriding the person.age function. Instead, corporation.age offers a second, overloaded implementation.

Remember that in my hierarchy, person is a supertype of corporation. I've defined a method in person to return the age of a person.

Interestingly, I can invoke either one of the age functions against a corporation object as shown here:

DECLARE
   oracle corporation_ot := 
      corporation_ot (
         'Artificial', -- species
         'Oracle Corporation',
         NULL,
         '01-JAN-1979',
         NULL,
         NULL
      );  
BEGIN
   IF -- person.age
      oracle.age 
      >
      -- corporation.age
      oracle.age (SYSDATE)
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'Impossible!');
   END IF;
END;

A Closer Look at Method Overriding

When you override an inherited method, you define a method in a subtype of the same name and have that method do something different from the supertype's version.

The compensation calculation for a manager will be different, for example, from that of other employees, so a specialized program will be needed in that object-type definition.

When a subtype overrides a method, then instances of the subtype will invoke that method rather than the overridden one. If the subtype itself has subtypes, those more specific types inherit the override of the method instead of the version of the supertype.

You'll only successfully override a method if your subtype's OVERRIDING member definition contains precisely the same signature (name, parameter list, RETURN clause if a function) as the supertype method. If the signature is different, then you've defined an overloading rather than an overriding method.

Keep the following restrictions in mind when you're figuring out when and how to overload methods:

You can override only methods that aren't declared to be final in the supertype.
Order methods (methods used to provide a sense of order or sequence to objects instantiated from the type) may be defined only in the root type of a type hierarchy. You can't override these methods.
You can't define an overridden method as a static method (one that's associated with the type itself and not with an object instantiated from the type).
A member method in a subtype isn't permitted to override a static method in the supertype.
If a method being overridden contains default values for parameters, then the overriding method must provide the same default values for the same parameters. The header of the programs must be entirely identical.

Polymorphism and Dynamic Method Dispatch

Polymorphism is the name given to a language's ability to choose from multiple methods of the same name and execute the appropriate method. There are two kinds of polymorphism:

Static polymorphism: The decision about which method to execute is made at the time the code is compiled. In PL/SQL, static polymorphism is also known as overloading.
Dynamic polymorphism: The decision about which method to execute is made at the time the code is executed, at runtime. This is also known as dynamic method dispatch and is available for the first time in PL/SQL with support for object-type inheritance.

Both types of polymorphism are very powerful features. Static polymorphism or overloading makes it easy for us to create simple, intuitive APIs or application programmatic interfaces for developers to use. Rather than having to remember 10 different names for "define a dynamic SQL column," for example, I can simply call the DBMS_SQL.DEFINE_COLUMN procedure; the PL/SQL compiler automatically figures out which program I need to run based on the parameter list.

Dynamic polymorphism is dramatically more useful and intriguing because it gives us so much more flexibility in the way we manipulate our objects. Suppose my PL/SQL block invokes a method on a type. The execution engine then uses the type of the object instance that invokes it to determine which implementation of the method to use. There can be multiple implementations due to either overloading or overriding. The call is then dispatched to that type's implementation for execution. This process of selecting a method implementation is called dynamic method dispatch since it's done at runtime, not at compile time.

PL/SQL dispatches a method call to the "nearest" implementation, working up the inheritance hierarchy from the current or specified type to its supertypes, if any.

An Example of Dynamic Polymorphism

Let's take a look at how I can take advantage of dynamic polymorphism with my hierarchy of different types of persons. I need to write a program to show what happens when different types of persons (which, in the U.S., include corporations by legal precedent) commit a crime. I'll walk through the individual steps for such a program.

First, I'll define my different types of persons:

A pre-citizenship person, Eve:

   eve person_ot
   := person_ot (
         'Human',
         'Eve',
         175 /* Rubanesque */,
         NULL
      );

A U.S. citizen who's currently on death row for murder, but is believed by many to be innocent of the crime (or at least to have received a grossly unfair trial). Notice that I add two additional attributes for a citizen, nation, and political preference.
```
   ondeathrow citizen_ot
   := citizen_ot (
         'Human',
         'Mumia Abul Jamal',
         150,
         NULL,
         'USA',
         'Radical' 
      );
```

A very scary company, the likes of which we're likely to see in the next decade or so with the way mergers and acquisitions are proceeding these days:

   theglobalmonster corporation_ot
   := corporation_ot (
         'Inhuman',
         'Northrup-Ford-Mattel-Yahoo-ATT',
         NULL,
         SYSDATE,
         5000000,
         50000000
      );

An even scarier human being, defined as a war criminal for his direction of the carpet- bombing of Vietnam and Cambodia:

   wiseman warcriminal_ot
   := warcriminal_ot (
         'Human?',
         'Henry Kissinger',
         175,
         NULL,
         'USA',
         'Above the law',
         1000000,
         'Vietnam and Cambodia'
      );

I've now defined four different objects, instantiated from different types in the person hierarchy: person, citizen, corporation, and war criminal. Now for the really interesting stuff. I want to define an array or list that contains all of these objects and then manipulate the contents of that array. Since all of the objects derive from the person supertype, I can declare a nested table of persons and populate that array with either person objects or objects declared from subtypes of person:

   TYPE bighappyfamily_nt IS TABLE OF person_ot;

   bighappyfamily bighappyfamily_nt
   := bighappyfamily_nt (
         eve,
         ondeathrow,
         theglobalmonster,
         wiseman
      );

I've now completed my declarations. On to my executable section. What I need to do is show what happens to each "person" when he or she commits a crime. What political power do they have and what is their punishment? To do that I can simply execute the following loop:

BEGIN
   FOR persindx IN
       bighappyfamily.FIRST .. bighappyfamily.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE (' ');
      bighappyfamily (persindx).when_crime_committed;
   END LOOP;
END;

The output from this loop's execution is shown in Listing 4. It all looks perfectly appropriate. For each different type of person, the information displayed is specific to that object. Yet quite a lot is going on behind the scenes to make that happen. Let's take a closer took.

Listing 4. Output from displaying the contents of the bighappyfamily array.

Suppose Eve Was Convicted of a Crime.--
Person named Eve weighs 175 and was born on
Political power?
The existence of a soul
Punishment?
Leave Garden of Eden

--Suppose Mumia Abul Jamal Was Convicted of a Crime.--
Citizen Mumia Abul Jamal is a citizen of USA whose politics are Radical
Political power?
One vote
Punishment?
Go to jail, do not pass Go, do not survive.

--Suppose Northrup-Ford-Mattel-Yahoo-ATT Was Convicted of a Crime.--
Corporation Northrup-Ford-Mattel-Yahoo-ATT is a trans-national entity with
50000000 laid-off employees, paying its Chief Executive Officer 5000000
Political power?
Virtually unlimited
Punishment?
Increased political contributions.

--Suppose Henry Kissinger Was Convicted of a Crime.--
War criminal Henry Kissinger killed 1000000 in Vietnam and Cambodia
Political power?
Filling the vacuum.
Punishment?
Sometimes you win the Nobel Peace Prize.

My FOR loop iterates through every row of bighappyfamily. Recall that bighappyfamily is a nested table of persons. Within the FOR loop, I invoke the when_crime_committed method for each element in the array. The when_crime_committed method is declared as FINAL in the person object and implemented as follows:

FINAL MEMBER PROCEDURE when_crime_committed
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE (   '--Suppose '
     || name
     || ' Was Convicted of a Crime.--');
   DBMS_OUTPUT.PUT_LINE (' ');
   show;      
   DBMS_OUTPUT.PUT_LINE (' ');
   DBMS_OUTPUT.PUT_LINE ('Political power?');
   showPoliticalPower;
   DBMS_OUTPUT.PUT_LINE (' ');
   DBMS_OUTPUT.PUT_LINE ('Punishment?');
   showpunishment;
END;

By defining the method as FINAL, I disallow any of the subtypes of person to override this program. Notice, however, that when_crime_committed invokes three other methods: show, showPoliticalPower and showPunishment. None of these methods, defined in person_ot, are defined as FINAL and, in fact, are overridden in each of the object types citizen_ot, corporation_ot, and war_criminal_ot. Here's one example of such an override, the citizen.showPoliticalPower method:

OVERRIDING 
   MEMBER PROCEDURE 
      showpoliticalpower
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE (
      'One vote');
END;

So, at the time of compilation of the person_ot object type, references to the show, showPoliticalPower, and showPunishment methods are all resolved to the person_ot methods. But when the when_crime_committed method is actually executed by the PL/SQL runtime engine, dynamic method dispatch results in a very different resolution.

When Oracle executes the following method, for example:

bighappyfamily(3).when_crime_committed

it identifies the object type as corporation_ot. Then it checks to see whether when_crime_committed is defined as a method in corporation_ot. It's not and, in fact, is only defined in person_ot. So the runtime engine then invokes person.when_crime_committed. As it runs that method, it encounters the showPoliticalPower method. Once again, Oracle must identify which of the showPoliticalPower methods to run. Even though it's currently executing a person_ot method, it hasn't forgotten where it came from. It therefore starts its search through the hierarchy with the corporation_ot object type. And, in fact, it finds that corporation_ot has implemented an overriding showPoliticalPower method. So it uses that one and not the person_ot.show method.

There are two approaches to resolving invocations to methods, static and dynamic.

Also In This Series

Substituting and Converting Object Types in a Hierarchy

New Datatypes, New Possibilities

Native Compilation, CASE, and Dynamic Bulk Binding

Table Functions and Cursor Expressions

Multi-Level Collections in Oracle 9i

HTTP Communication from Within the Oracle Database

Oracle 9i Release 2 Developments for PL/SQL Collections

Using PL/SQL Records in SQL Statements

Dynamic polymorphism gives developers an incredible amount of flexibility. We can write generalized programs that seemingly overlook variations in subtypes and levels in the hierarchy. We leave it to the runtime engine to locate and execute the most appropriate definition of a method.

It's Time to Try Out Object Types

Prior to Oracle9i, there was no compelling reason to use object types unless you used Oracle Advanced Queuing or some other Oracle utility that relied on object types. There were simply too many limitations to its implementation. With Oracle9i and the support for inheritance, however, Oracle takes PL/SQL a big step closer to a true object-oriented language.

Now that we can set up object-type hierarchies, it's worth revisiting object types to see how they can be used to construct robust applications based on PL/SQL. We'll explore further nuances of inheritance in the next article.

This article was originally published in the December 2001 issue of Oracle Professional. The material in Feuerstein and Bryn Llewellyn's articles is based on Oracle Corporation white papers originally prepared by Llewellyn for Oracle OpenWorld 2001 in San Francisco and OracleWorld Copenhagen in June 2002 and Oracle PL/SQL Programming, 3rd Edition.

Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language.

O'Reilly & Associates recently released (September 2002) Oracle PL/SQL Programming, 3rd Edition.

Sample Chapter 10, Dates and Timestamps , is available free online.
You can also look at the Table of Contents, the Index, and the Full Description of the book.
For more information, or to order the book, click here.

Inherit the Database Oracle9i's Support for Object Type Inheritance