21.2 An Extended Example

I'd like to point out that this example—indeed, most of this chapter—relies heavily on features introduced in Oracle9i. In a number of areas, this section focuses on features available only in Oracle9i Release 2. If you really want server-side object orientation, you probably won't want to use any version earlier than Oracle9i anyway, and you'll probably agree that ignoring the OO-challenged earlier versions is no great sacrifice.

21.2.1 A Tree of Types

In keeping with the sample general application area we explored in our introductory book, Learning Oracle PL/SQL Programming (O'Reilly), I'd like to build an Oracle system that will use an object-oriented approach to modeling a trivial library catalog. The catalog can hold books, serials (such as magazines, proceedings, or newspapers), and, eventually, other artifacts.

A graphic portrayal of the top-level types appears in Figure 21-1. Later on, we might want to add to the type hierarchy, as the dotted-line boxes imply.

Figure 21-1. Type hierarchy for a trivial library catalog

21.2.1.1 Creating a base type

The "root" or top of the hierarchy represents the common characteristics of all the subtypes. For now, let's assume that the only things that books and serials have in common are a library-assigned identification number and some kind of filing title. We can create an object type for catalog items using the following SQL statement from SQL*Plus:

CREATE OR REPLACE TYPE catalog_item_t AS OBJECT (

id INTEGER,

title VARCHAR2(4000),

NOT INSTANTIABLE MEMBER FUNCTION ck_digit_okay

RETURN BOOLEAN,

MEMBER FUNCTION print

RETURN VARCHAR2

) NOT INSTANTIABLE NOT FINAL;

This statement creates an object type, which is similar to a Java or C++ class. In relational terms, an object type is akin to a record type bundled with related functions and procedures. These subprograms are known collectively as methods.

The NOT FINAL keyword at the end flags the datatype as being able to serve as the base type or supertype from which you can derive other types. I needed to include NOT FINAL because I want to create subtypes for books and serials; if this keyword is omitted, Oracle defaults to FINAL (no subtypes allowed).

Notice also that I've marked this type specification NOT INSTANTIABLE. Although PL/SQL will let me declare a variable of type catalog_item_t, I won't be able to give it a value—not directly, anyway. Similar to a Java abstract class, this kind of type exists only to serve as a base type from which to create subtypes—and objects of the subtype will, presumably, be instantiable.

For demonstration and debugging purposes, I've included a print method ("print" is not a reserved word, by the way) as a way to describe the object in a single string. When I create a subtype, it can (and probably should) "override" this method—in other words, the subtype will include a method with the same name, but will also print the subtype's attributes. Notice that instead of making print a procedure, which would have hardcoded a decision to use something like DBMS_OUTPUT.PUT_LINE, I made it a function whose output can be redirected later. This isn't particularly object-oriented, just good design.

I've also defined a ck_digit_okay method that will return TRUE or FALSE depending on whether the "check digit" is OK. The assumption here (which is a bad one, I admit) is that all subtypes of catalog_item_t will be known by some identifier other than their library-assigned id, and these other identifiers include some concept of a check digit.^[2] I'm only going to be dealing with books and serials, normally identified with an ISBN or ISSN, so the check digit concept applies to all the subtypes.

^[2] A check digit is a number incorporated into an identifier that is mathematically derived from the identifier's other digits. Its accuracy yields a small amount of confidence that the overall identifier has been correctly transcribed. The ISBN (International Standard Book Number) and ISSN (International Standard Serial Number)—identifiers assigned by external authorities—both contain check digits.

Here are a few further comments before moving on to the next part of the example:

• The CREATE TYPE statement above creates only an object type specification. The corresponding body, which implements the methods, will be created separately usingCREATE TYPE BODY.

• Object types live in the same namespace as tables and top-level PL/SQL programs. This is one of the reasons I use the "_t" naming convention with types.

• Object types are owned by the Oracle user (schema) that created them, and this user may grant EXECUTE privilege to other users.

• You can attempt to create synonyms on object types, but unless you're using Oracle9i Release 2, the synonyms won't work.

• As with conventional PL/SQL programs, you can create an object type using either definer rights (the default) or invoker rights (described in Chapter 20).

• Unlike some languages' object models, Oracle's model does not define a master root-level class from which all programmer-defined classes derive. Instead, you can create any number of standalone root-level datatypes such as catalog_item_t.

• If you see the compiler error PLS-00103: Encountered the symbol ";" when expecting one of the following..., you have probably made the common mistake of terminating the methods with a semicolon. The correct token in the type specification is a comma.