18. Object Types

Contents:
Introduction to Oracle8 Objects
Oracle Objects Example
Syntax for Creating Object Types
Manipulating Objects in PL/SQL and SQL
Modifying Persistent Objects
Object Housekeeping
Making the Objects Option Work

In the last ten years, those of us who spend our lives with computers have probably seen and heard a lot about object-oriented (OO) programming and how it has revolutionized software development. Proponents cite the time-worn analogy with the integrated circuit (IC), which has allowed computer hardware manufacturers to bind together larger and larger reusable units into more and more powerful machines. "If only software people could match the pace of the hardware people," lament the advocates of objects. If we could build systems using reusable "software ICs," programming would be faster and more reliable, and we could address complex problems with relative ease. Detractors argue that, in contrast to relational theory, object approaches have no mathematical foundation, and they also shudder at the thought of turning the average corporate programmer into the rocket scientist needed to code in strangely popular OO languages. How many PL/SQL developers do you know, for example, who can also program in C++? For most of us, one look at the mysteriously juxtaposed punctuation marks gives us the heebie jeebies.

Of course, object orientation is much more than "programming in C++," just as relational databases are much more than "programming in SQL." The application programming language is only the last tool in the life cycle; techniques, methods, and user interfaces can now bear the object-oriented moniker. In fact, many Oracle users have been practicing at least some aspects of object orientation for years using solid engineering design principles, with SQL and PL/SQL as tools. If you model your system using entity-relationship diagrams, you have experience with "entities" that share many characteristics with objects. Not that I date from the Mesozoic era of computing history, but allow me to point out that the idea that programmers can modularize code into reusable fragments, and that data structures can include rich typing systems, predate Smalltalk and Simula, the first object languages.

Maybe you're a true believer in the software IC, or maybe your response is "objects, schmobjects, who needs `em?", or maybe objects haven't even been on your radar screen because you're living with Oracle, and Oracle is, well, a relational database, with only rows and columns. That used to be true. Now with Oracle's objects option, the database can contain not only rows and columns, but also complex data structures which encapsulate both data and behavior.

Because objects may be new to many readers, this chapter will introduce objects concepts and terminology before plunging into the details of the Oracle objects option. First we'll look at the objects option in the context of the relational features of the Oracle server, and give some examples of places where objects can be used. To put the subject in the broader technology context, we'll also step back to look at what it means to be object-oriented, and how Oracle fulfills the object motif. Next, we'll show an extended example of creating and using Oracle objects for a hypothetical pet store, introducing salient points about using objects in a step-by-step fashion. Only then will we take a more in-depth look at the syntax and rules of usage in both PL/SQL and SQL. We'll also present four different technical strategies for applying object approaches in a new application. Finally, we'll look at housekeeping and the unpleasant business of modifying object designs, and conclude with a few words on making your object initiative work.

Although this chapter covers all aspects of using objects in PL/SQL, we don't have space to cover every possible aspect of using objects in the server. Some of the things we don't discuss in detail include: triggers on object tables (since objects do not alter what you already know about triggers from Oracle7); ways of using PL/SQL objects with the Oracle Call Interface (OCI) for C or C++; and some of the physical storage considerations of objects.

18.1 Introduction to Oracle8 Objects

When a mainstream vendor like Oracle Corporation ventures into new technology waters, it is virtually certain that the change will be evolutionary rather than revolutionary. True to form, Oracle8's relational capabilities are still the mainstay of Oracle Corporation's flagship database server, and these capabilities satisfy the need for compatibility with older Oracle versions. But with the objects option, Oracle8 allows programmers to use a new set of datatypes and models drawn from object programming languages, allowing persistent objects [ 1 ] to be created in the database and accessed, via an API, from C++, Smalltalk, Object COBOL, Java, and other languages.

[1] "Persistence" is the characteristic illustrated when an object "sticks around" from one program session to the next, something taken for granted in the relational world. If all you have is an object-oriented language like Smalltalk or C++, you have to either do extensive custom programming to achieve object persistence, or use an OODB.

Contrast this "object-relational" database approach with the true "object-oriented databases" (OODBs) that first appeared commercially in the mid-1980s. Pure OODBs are most successful in problem domains characterized by complex, often versioned, data (such as engineering, CASE, or CAD); they typically extend the type system of object-oriented languages to allow for persistent objects. Oracle8, on the other hand, extends the programming system of the database to allow for operations, and extends conventional datatypes to include complex structures. While these object extensions to SQL and PL/SQL sometimes look as if they were designed merely to confuse the programmer, object types in Oracle, properly implemented, can be the cornerstone of an overall object strategy. (And at least they don't use alien punctuation!)

18.1.1 Terminology

The first big hurdle to cross is the nomenclature; object technology in general, and Oracle8 in particular, seem to litter us with terms that seem familiar but aren't. For example, even prior to Oracle8, Oracle did have objects -- that is, tables, indexes, packages, procedures, etc. -- in fact, you can see them all in the USER_OBJECTS view. Now we have something most precisely called object types, which can have object instances , the latter of which are referred to simply as objects. Even more ironically, these objects don't show up in the USER_OBJECTS view (object types do, though). Confused? To keep things straight in this chapter, items you've always seen in USER_OBJECTS I'll call the database objects , and Oracle8 objects I'll call simply objects .

Rather than lob all the new terminology at you in eye-glazing detail, I'll introduce the most important terms first, and present the others as needed. These terms are not strictly from the lexicon of object-oriented purists, but are defined in an Oracle context.

Object type: An Oracle database construct, managed via Data Definition Language (DDL) extensions, that defines a data structure (attributes) and the legal operations (methods) on the attributes. The type is only a template and holds no data itself; you may create variables, tables, columns, and other constructs of this type. If you are familiar with object terminology, note that an object type is the closest thing to a class . It is also very similar to an abstract data type (ADT).
Object: An instance of an Oracle8 object type. The object is the place where the actual data resides. Objects may be stored within tables (in such cases, they are called persistent ), or they may exist only temporarily in PL/SQL variables, in which case they are called transient .
Attribute: A structural part of an Oracle object, roughly akin to a column in a table. Each attribute must be of a single datatype, either scalar, like VARCHAR2 or INTEGER, or composite, like a user-defined nested table or another (nested) object. Scalar attributes are sometimes called simple , and composite attributes may be referred to as complex .
Method: A procedure or function, usually implemented in PL/SQL, that (typically) operates on an object's attributes. The methods for an object can only be invoked in the context of a specific object of that type. Similar to a program unit in a PL/SQL package, a method has a specification which is separate from its body. Method bodies can also be implemented in C (or other languages) and can be invoked as an Oracle "external procedure" (see Chapter 21, External Procedures ). There is a special default method supplied by Oracle, a constructor , that initializes objects.

18.1.2 Some Simple Examples

To give you a better sense of how object technology works in Oracle, let's look at some specific code samples that use objects. The following examples use a simple object type, Pet_t. Don't worry too much about what all the syntax means yet; we'll review it in detail later. First, let's define the object type:

CREATE TYPE Pet_t AS OBJECT (
   tag_no INTEGER,
   name VARCHAR2(60),
   MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER)
      RETURN Pet_t
);

This object type has two attributes, tag_no and name, and one method, set_tag_no . But the method won't do anything until we create the associated body for the object type:

CREATE TYPE BODY Pet_t
AS
   MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER) RETURN Pet_t
   IS
      the_pet Pet_t := SELF;  -- initialize to "current" object
   BEGIN
      the_pet.tag_no := new_tag_no;
      RETURN the_pet;
   END;
END;

SELF is a way of referencing the object on which the method is invoked. This method returns an object with its tag_no attribute set to a new value.

Using this object type, here are some code fragments illustrating different applications of the type. Object types can serve as the datatype of any of a number of different entities.

An object type can serve as the datatype of each of the rows in a table . The table is then referred to as an object table, and it contains row objects; that is, each row is an object instance:

CREATE TABLE pets OF Pet_t;

-- Now we can create an object (object instance) of type Pet_t
INSERT INTO pets VALUES (Pet_t(23052, 'Mambo'));

An object type can serve as the datatype of a column . The column is then said to contain column objects. Different columns in a table could be of different object types. (The example below also uses Address_t, an object assumed to be already defined. Curiously enough, it contains attributes and methods appropriate to street addresses.)

CREATE TABLE families (
   surname VARCHAR2(50),
   favorite_pet Pet_t,
   address Address_t);

An object type can serve as the datatype of a local variable . Here, we declare and initialize an object variable in one statement. The initialization uses the automatically available constructor which has the same name as the datatype:

DECLARE
   my_pet Pet_t := Pet_t(23052, 'Mambo');
    ^       ^        ^
    |       |         constructor
    |     type
  variable

An object type can serve as the datatype of a PL/SQL return parameter. Functions may also return object types, as the example shows. The VALUE operator is needed to retrieve a table object:

CREATE FUNCTION find_pet (the_tag_no IN NUMBER) RETURN Pet_t
IS
   the_pet Pet_t;
   CURSOR pet_cur IS
      SELECT VALUE(p)
        FROM pets p
       WHERE tag_no = the_tag_no;
BEGIN
   OPEN pet_cur;
   FETCH pet_cur INTO the_pet;
   CLOSE pet_cur;
   RETURN the_pet;
END;

An object type can serve as the datatype of a nested table or VARRAY. This is possible as long as the object type has no TABLE or VARRAY attributes itself:

CREATE TYPE homeless_pet_t AS TABLE OF pet_t;

An object type can serve as the datatype of a "field" in a record variable. Nothing terribly exotic here:

DECLARE
   -- first define the type
   TYPE family_t IS RECORD (
      surname VARCHAR2(50),
      favorite_pet Pet_t);

   -- now declare a variable of that type
   family family_t;

18.1.3 Comparison: Oracle8 Objects and Earlier Features

In practical terms, object types live inside the Oracle database rather than inside PL/SQL programs. That is, you must issue the SQL DDL statement, CREATE TYPE ... AS OBJECT in order to create a type; only then can you use the type within PL/SQL. Once created, an object type defines an interface to a set of data. There is no good analogy for this behavior in Oracle7. An object type isn't really like a table, since it holds no data, but you can create tables based on the type.

An object type is a bit more like a package that contains only type declarations and functions that operate on those types. This is especially true since the object type, like the package, can have a separate "body" section in which to implement its procedures and functions (methods). There are key differences, though; perhaps most significantly, code in the object type body can only be invoked on a particular object. That is, you cannot call a method unless you also indicate an object instance on which to apply it. In addition, you can't create a table based on a package specification, the way you can create a table from an object type, and object types cannot include constants, exceptions, cursors, or datatypes. Table 18.1 compares the new object features with features of tables and packages.

Table 18.1: Comparing Oracle8 Objects to Earlier Oracle Features
Characteristic	Oracle7 Table	Oracle7 Package	Oracle8 Object
Stores data	Yes	Temporary only; package variables exist for duration of session	Object instance data may be persistent (stored in tables) or transient (stored in variables)
Serves as a template	No	No	Object types serve as a template for object instances
May contain complex data	No; normalized columns contain scalar values only	Yes; some datatypes such as RECORD and TABLE types do not require the objects option	Yes (requires objects option installed)
Contains procedural code	No (except for table triggers)	Yes	The code is in the object type definition, but can be invoked only on a specific instance
Has a body separate from its specification	N/A (in the case of triggers, the answer is no)	Yes	Yes (object type definition has separate body for method implementation)
May expose constants, exceptions, cursors, or datatypes	N/A (in the case of triggers, the answer is no)	Yes	No
Rights model (see "Privileges" later in this chapter)	Owner must explicitly grant DML privileges on table to user or role	If owner grants EXECUTE to invoker, latter inherits owner's DML privileges	Currently, if owner grants EXECUTE to invoker, latter inherits owner's DML privileges

18.1.4 Characteristics of Objects

Whether or not you have a background in the object-oriented world, you might find it useful to review some of the characteristics of objects and how they are implemented in Oracle. This section does not try to present a complete treatment of objects, but only a primer on the core principles.[ 2 ]

[2] The technically inclined who want more a detailed discussion of the technology can refer to Grady Booch's Object-Oriented Analysis and Design with Applications , Benjamin/Cummings, 1994. The managerially inclined might enjoy David A. Taylor's Object-Oriented Technology: A Manager's Guide , Addison-Wesley, 1990.

In a frequently cited work on the object approach, James Rumbaugh introduces four characteristics of objects: identity, classification, inheritance, and polymorphism.[ 3 ] Using this taxonomy as a starting point, let's dive in.

[3] See Rumbaugh, James., et al. Object-Oriented Modeling and Design , Prentice Hall, 1991.

An object is a "thing," like a pet, or a contract, or a holiday, which is discrete and identifiable, which shares characteristics with other like objects. One of the major thrusts of object orientation is that its approach to modeling and implementing systems relies on an intuitive mapping of the problem space -- the actual pet, for example -- to a programmatic or database representation. The argument goes that the relational world is a bit artificial when it introduces one table to hold a list of animals, and another table to hold a list of vaccinations that the animal has received.

18.1.4.1 Identity

Since each object has identity, each object also has a unique identifier or handle . Even if all the properties of two different objects are identical, each has a different identity. Often, object-oriented programming systems assign invisible arbitrary numbers to serve as handles.

In Oracle8, unique object identifiers are automatically assigned to objects when they are stored as "table objects." It turns out that this identifier is stored in a hidden 16-byte RAW field. This "object identifier," or OID, can be referenced from columns in other tables, much as a primary key can be referenced from a foreign key.

18.1.4.2 Classification

Just as the human observer groups real-world objects into categories, so can the programmer in an object-oriented programming language. Objects are put into categories which share characteristics or properties, as well as legal operations on the category, or methods. In many languages, these categories are called classes . A more generic academic term for a class is an abstract data type or ADT ; in fact, early beta versions of Oracle8 used the term "ADT" rather than "object type."

The actual object, such as the red-haired mutt named Mambo, is known as an instance of a class or object type Dog, in much the same way that a row in a relational table can be described as an instance of a relation.

In Oracle, objects are classified into object types , and properties are called attributes . Methods are either member functions or member procedures .

18.1.4.3 Inheritance

Although there are few things that object practitioners agree on, one is that any language that claims to be object-oriented must support inheritance . Inheritance allows for hierarchies in which each child has characteristics of its parent . Each level in the type hierarchy has properties which can be shared by those beneath it; lower levels can have their own specialized attributes or functions as well.

As an example, our Dog type might exist as a subtype of a Pet type. So if a Pet has a name, each Dog would have a name, and you would not need to define this property a second time. The Dog type might extend the Pet type by including a dog-specific attribute such as is_house_trained. Note that this hierarchy allows the inheriting of properties and methods; it is not a hierarchy of data, like an Oracle CONNECT BY scheme.

From an object programming standpoint, the bad news is that Oracle 8.0 does not directly support inheritance. You cannot create a user-defined object type as a subtype of another. As a corollary, you cannot reuse method definitions. The good news, though, is that the ANSI and ISO committees working on object-oriented extensions to SQL are forming a position on how inheritance should behave in object-relational databases.[ 4 ] If history is any indicator, Oracle will move quickly to adopt as much of the new standard (known as SQL3), as possible. As we go to press, SQL3 is still a bit fluid, and the Oracle user community still has an opportunity to communicate to Oracle the most important inheritance features from a user perspective. Do we need multiple inheritance? Do we have to have late method binding? If you have an opinion, let Oracle know!

[4] A discussion of the committees' work on object extensions appears at http://info.gte.com/ftp/doc/activities/x3h7/by_model/SQL3.html .

Although inheritance gets much attention in object technologies, it is one of several types of relationships that can be incorporated into an object model or, in some languages, an object implementation. Inheritance is often described as an is-a relationship; that is, a Dog is-a Pet.[ 5 ] Other prominent relationships include aggregation and association. Aggregation occurs where one object is composed, at least in part, of other objects; you could call it a part-of relationship: a Tail is part-of a Dog. Association is a more generic, usually named relationship, indicating some other link between object types, as in a Dog gets examined by a Veterinarian.

[5] Viewing inheritance as synonymous with "is-a" relationships can sometimes be too simplistic. Martin Fowler describes a number of scenarios where doing so can lead to logical errors. See UML Distilled: A Concise Guide for Applications Developers , Addison-Wesley, 1997.

While these relationship types are common in object modeling nomenclatures such as the Unified Modeling Language (UML),[ 6 ] relational models rarely categorize them using these names. However, a kind of pseudo-inheritance is available in an entity-relationship (ER) model that uses supertype/subtype entities, which can be transformed into several different physical implementations. Aggregation and association can be represented as named relationships among entities and then transformed into foreign keys. The Oracle objects extension does give us the ability to create relationships using a new kind of pointer called a reference (REF), described later in this chapter.

[6] UML, the combined effort of Rational Corporation's object mavens Grady Booch, James Rumbaugh, and Ivar Jacobson, is expected to figure prominently in Oracle Corporation's object modeling conventions and tools. Visit http://www.rational.com/uml/index.html or http://www.rational.com/uml/1.1/index.html for documentation of the latest UML standard.

18.1.4.4 Polymorphism

Polymorphism, by one definition, means that a given operation behaves consistently even when applied to different datatypes. You can implement polymorphism in at least two different ways, even without the Oracle objects option:

Module overloading (see Chapter 15, Procedures and Functions ) allows a given PL/SQL module to have multiple specifications and bodies, distinguished by the datatype of the arguments supplied.
Programmers can implement a kind of ad hoc polymorphism in the way they program object types. For example, a Pet type might implement an operator called LIST_ME which gives a simple ability to display its contents on a report. This operator could be implemented in other object types as well.

The idea here is that a common core of functionality exists across the population of object types; each object knows how to respond to the requests sensibly. The classical form of polymorphism is one in which an object is not of a single type but actually represents many types, all of which are descendants of some common supertype; this behavior in fact exists in PL/SQL's implicit datatype conversions. For example, you can add a real number to an integer using a polymorphic "+" operator.

18.1.5 Object Programming Themes

While object-oriented programming languages have special features to support object identity, classification, inheritance, and polymorphism, software developers can adopt the general themes of object orientation in many languages. These themes[ 7 ] include:

[7] Again, see James Rumbaugh.

Information hiding
Abstraction
Combining data with behavior
Decomposition of things, not processes

18.1.5.1 Information hiding

When I first learned about "information hiding," I said to myself, "Why in the world do we want to hide information? Isn't more better?" That was before I had worked on systems from the Jackson Pollock[ 8 ] school of design. Now I can say with conviction that hiding the internals of a software construct, and carefully limiting its public interface, is a good thing. Not only does it make for fewer unnecessary bits competing for attention in my limited biological CPU, it also brings clarity to application design, and keeps other modules from relying on the unsavory details.

[8] Jackson Pollock (1912-1956) was an American painter prominent in the Abstract Expressionist movement. His paintings abandoned the idea that composition should be expressed as the relationship among parts. Some say his paintings look as if he tossed paint cans at the canvas.

One beautiful way that PL/SQL supports information hiding is by separating specifications from bodies and allowing separate recompilation. This separation is available to both packages (since Oracle7) and object types. In addition, package bodies can include "private" local variables, procedures, and functions which cannot be invoked outside the package.

Oracle 8.0.3 does not support private methods inside an object body, but we can hope that such a feature will appear in a future release.

18.1.5.2 Encapsulation

Encapsulation, a sister concept to information hiding, asserts that you can only "get at" an object's contents using predefined functions. The extreme degree of encapsulation asserts that no data can be viewed or modified except through explicitly defined (or inherited) methods. This allows the programmer to retain control of the data, and helps reduce the impact of schema changes. By establishing cleanly defined object interfaces, we can develop decoupled, reusable modules that can be made to fit together gracefully even when future requirements change.

Encapsulation was gracefully achievable as early as Oracle7 using packages. By putting all DML into procedures and functions and requiring applications to use these modules rather than issuing direct INSERT, UPDATE, or DELETE statements, developers can realize enormous long-term benefits. Oracle objects extend the encapsulation options available to programmers to include object methods.

Oracle still allows server-side database triggers on object tables. In one sense, these triggers violate encapsulation because they allow PL/SQL code to modify object contents directly. However, in keeping with Oracle's hybrid object-relational strategy, you can use triggers if they make sense to your application.

18.1.5.3 Abstraction

The concept of abstraction includes both data abstraction and functional abstraction. An example of abstraction is a purchase order.[ 9 ] With a programming system that allows us to implement abstract datatypes, we can represent a purchase order as a purchase order object, rather than as a parent table, plus a child table of line items, plus a number of applications to manage the tables. In the real world, purchase orders can be created, edited, completed, and destroyed. In our object-oriented program, a single purchase order ADT would be defined using whatever complex data structures are appropriate, and would include methods for each of the corresponding real world operations.

[9] The purchase order example is particularly common in Larry Ellison speeches.

One assumption about abstraction is that dealing with fewer "things" in the program can reduce the likelihood of errors. In addition, only by the practice of abstraction can we possibly build and maintain extremely large, distributed systems.

With the Oracle objects option, the essential structure we use to create an abstraction is the object type. While PL/SQL packages can also implement an ADT, programmers have typically used packages only for the abstraction of complex processing. Objects, on the other hand, are designed to encourage both process abstraction and data abstraction. These abstractions can encompass complex, multi-level structures. For example, an Oracle object may contain other embedded objects, collections (similar to arrays), and references (pointers) to other objects.

18.1.5.4 Combining data with behavior

In a typical relational application, there is a database schema consisting of a number of possibly normalized tables, plus a body of application code that manipulates the data. During design, analysts model the entity relationship graph separately from the module hierarchy, and this can result in the need for complex cross-reference matrices which can be costly to develop and more challenging to maintain.

In contrast, in an object model, the data and the legal operations on the data are co-located. In an object implementation, attributes and methods are welded into a single reusable part. A good object design means that there are fewer interconnections to deal with, increasing the likelihood of both reusability and adaptability.

Oracle now directly supports this object programming theme, storing data in object attributes (persistent or not), with behavior defined by object methods.

18.1.5.5 Decomposition of things, not processes

It is common knowledge that changing a database's table design after the applications are built is a risky and expensive proposition. Database professionals have for years known that data structures are more stable than processes. That's why we require large scheduling windows (and expensive support tools) for entity-relationship modeling and database design.

Object programming carries this argument one step further, into the design of applications themselves. A well-designed object application, rather than being a hierarchy of sequential procedures exchanging flow of control, is more akin to a community of objects exchanging messages.

Non-object approaches, by contrast, often emphasize functional decomposition: breaking a problem, and its solution, into a series of processing steps. One problem with this type of approach is that, over time, the processes in the problem space are likely to change, and this will force extensive changes in the application. An object-oriented decomposition of the same problem, if properly designed, would require fewer changes.