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, that is, 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 decided to make it a function whose output can be redirected later. This decision 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.^[*] 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.

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

I made catalog_item_t impossible to instantiate, so now would be a good time to show how to create a subtype for book objects. In the real world, a book is a type of catalog item; this is also true in my example, in which all instances of this book_t subtype will have four attributes:

In code, I can make the equivalent statement as follows:

 1   CREATE OR REPLACE TYPE book_t UNDER catalog_item_t (
 2      isbn VARCHAR2(13),
 3      pages INTEGER,
 4
 5      CONSTRUCTOR FUNCTION book_t (id IN INTEGER DEFAULT NULL,
 6         title IN VARCHAR2 DEFAULT NULL,
 7         isbn IN VARCHAR2 DEFAULT NULL,
 8         pages IN INTEGER DEFAULT NULL)
 9         RETURN SELF AS RESULT,
10
11       OVERRIDING MEMBER FUNCTION ck_digit_okay
12          RETURN BOOLEAN,
13
14       OVERRIDING MEMBER FUNCTION print
15          RETURN VARCHAR2
16    );

The interesting portions of this code are as follows:

If you’re a relational database programmer, you know that conventional tables have a unique identifier for every row. If you’re an object-oriented programmer, you know that OOP environments generally assign unique arbitrary identifiers that serve as object handles. If you’re a programmer using object-relational features of Oracle, you have a mix of both approaches. The following table summarizes where you will find object identifiers:

Here is an example of a table that can hold column objects:

    CREATE TABLE my_writing_projects (
       project_id INTEGER NOT NULL PRIMARY KEY,
       start_date DATE,
       working_title VARCHAR2(4000),catalog_item catalog_item_t  -- this is a "column object"
    );

Oracle Corporation takes the view that a column object is dependent on the row’s primary key, and should not be independently identified.^[*]

For any object table, Oracle can base its object identifier on one of two things:

Which kind of OID should you use? Primary-key-based OIDs typically use less storage than system-generated OIDs , provide a means of enforcing referential integrity, and allow for much more convenient loading of objects. System-generated OIDs have the advantage that REFs to them cause “SCOPED” or limited to values from only one table. For a more complete discussion of the pros and cons of these two approaches, check out Oracle’s document Application Developer’s Guide—Object-Relational Features. For now, you should know that a system-generated OID is:

To retrieve an object from the database, Oracle provides the VALUE function in SQL. VALUE accepts a single argument, which must be a table alias in the current FROM clause, and returns an object of the type on which the table is defined. It looks like this in a SELECT statement:

    SELECT VALUE(c)
      FROM catalog_items c;

I like short abbreviations as table aliases, which explains the c. The VALUE function returns an opaque series of bits to the calling program rather than a record of column values. SQL*Plus, however, has built-in features to interpret these bits, returning the following result from that query:

    VALUE(C)(ID, TITLE)
    -------------------------------------------------
    BOOK_T(10003, 'Perelandra', '0-684-82382-9', 222)
    SERIAL_T(10004, 'Time', '0040-781X', 'O')

PL/SQL also has features to deal with fetching objects. Start with a properly typed local variable named catalog_item:

    DECLARE
       catalog_item catalog_item_t;
       CURSOR ccur IS
          SELECT VALUE(c)
            FROM catalog_items c;
    BEGIN
       OPEN ccur;
       FETCH ccur INTO catalog_item;
       DBMS_OUTPUT.PUT_LINE('I fetched item #' || catalog_item.id);
       CLOSE ccur;
    END;

The argument to PUT_LINE uses variable.attribute notation to yield the attribute value, resulting in the output:

    I fetched item #10003

The fetch assigns the object to the local variable catalog_item, which is of the base type; this makes sense because I don’t know in advance which subtype I’ll be retrieving. My fetch simply assigns the object into the variable.

In addition to substitutability, the example also illustrates (by displaying catalog_item.id) that I have direct access to the base type’s attributes.

In case you’re wondering, normal cursor attribute tricks work too; the previous anonymous block is equivalent to:

    DECLARE
       CURSOR ccur IS
          SELECT VALUE(c) obj
            FROM catalog_items c;
       arec ccur%ROWTYPE;
    BEGIN
       OPEN ccur;
       FETCH ccur INTO arec;
       DBMS_OUTPUT.PUT_LINE('I fetched item #' || arec.obj.id);
       CLOSE ccur;
    END;

If I just wanted to print out all of the object’s attributes, I could, of course, use the print method I’ve already defined. It’s legal to use this because it has been defined at the root type level and implemented in the subtypes; at runtime, Oracle will find the appropriate overriding implementations in each subtype. Ah, the beauty of dynamic method dispatch.

As a matter of fact, the VALUE function supports dot notation, which provides access to attributes and methods—but only those specified on the base type. For example, the following:

    SELECT VALUE(c).id, VALUE(c).print()
      FROM catalog_items c;

yields:

    VALUE(C).ID VALUE(C).PRINT()
    ----------- ----------------------------------------------------------
          10003 id=10003; title=Perelandra; isbn=0-684-82382-9; pages=222
          10004 id=10004; title=Time; issn=0040-781X; open_or_closed=Open

If I happen to be working in a client environment that doesn’t understand Oracle objects, I might want to take advantage of such features.

But what if I want to read only the attribute(s) unique to a particular subtype? I might first try something like this:

    SELECT VALUE(c).issn   /* Error; subtype attributes are inaccessible */
      FROM catalog_items c;

This gives me ORA-00904: invalid column name. Oracle is telling me that an object of the parent type provides no direct access to subtype attributes. I might try declaring book of book_t and assigning the subtyped object to it, hoping that it will expose the “hidden” attributes:

    book := catalog_item;  /* Error; Oracle won't do implied downcasts */

This time I get PLS-00382: expression is of wrong type. What’s going on? The non-intuitive answer to that mystery appears in the next section.

Before we move on, here are a few final notes about performing DML on object relational tables:

Now let’s go back and take a look at that last problem I mentioned.

If I’m dealing with a PL/SQL variable typed as a supertype, and it’s populated with a value of one of its subtypes, how can I gain access to the subtype-specific attributes and methods? In my case, I want to treat a generic catalog item as the more narrowly defined book. This operation is called narrowing or downcasting, and is something the compiler can’t, or won’t, do automatically. What I need to use is the special Oracle function called TREAT:

    DECLARE
       book book_t;
       catalog_item catalog_item_t := NEW book_t();
    BEGIN
       book := TREAT(catalog_item AS book_t);   /* Using 9i R2 or later */
    END;
    /

or, in SQL (Oracle9i Database Release 1 PL/SQL doesn’t directly support TREAT):

    DECLARE
       book book_t;
       catalog_item catalog_item_t := book_t(NULL, NULL, NULL, NULL);
    BEGIN
       SELECTTREAT (catalog_item AS book_t)
         INTO book
         FROM DUAL;
    END;

The general syntax of the TREAT function is:

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

where object_instance is any object with subtype as the name of one of its subtypes. Calls to TREAT won’t compile if you attempt to treat one type as another from a different type hierarchy. One notable feature of TREAT is that if you have supplied an object from the correct type hierarchy, it will return either the downcasted object or NULL—but not an error.

As with VALUE, you can use dot notation with TREAT to specify an attribute or method of the TREATed object. For example:

    DBMS_OUTPUT.PUT_LINE(TREAT (VALUE(c) AS serial_t).issn);

If I want to iterate over all the objects in the table in a type-aware fashion, I can do something like this:

    DECLARE
       CURSOR ccur IS
          SELECT VALUE(c) item FROM catalog_items c;
       arec ccur%ROWTYPE;
    BEGIN
       FOR arec IN ccur
       LOOP
          CASE
             WHEN arec.item IS OF (book_t)
             THEN
                DBMS_OUTPUT.PUT_LINE('Found a book with ISBN '
                   || TREAT(arec.item AS book_t).isbn);
             WHEN arec.item IS OF (serial_t)
             THEN
                DBMS_OUTPUT.PUT_LINE('Found a serial with ISSN '
                   || TREAT(arec.item AS serial_t).issn);
             ELSE
                DBMS_OUTPUT.PUT_LINE('Found unknown catalog item'),
          END CASE;
       END LOOP;
    END;

This block introduces the IS OF predicate to test an object’s type. Although the syntax is somewhat exciting:

object IS OF ( [ ONLY ] typename )

the IS OF operator is much more limited than one would hope: it works only on object types, not on any of Oracle’s core datatypes like NUMBER or DATE. Also, it will return an error if the object is not in the same type hierarchy as typename.

Notice the ONLY keyword . The default behavior—without ONLY—is to return TRUE if the object is of the given type or any of its subtypes. If you use ONLY, the expression won’t check the subtypes and returns TRUE only if the type is an exact match.

    TREAT(item AS book_t).set_isbn('0140714154'), --wrong

    book := TREAT(item AS book_t);
    book.set_isbn('0140714154'),

The IS OF predicate, like TREAT itself, became available in Oracle9i Database Release 1 SQL, although direct support for it in PL/SQL didn’t appear until Oracle9i Database Release 2. As a Release 1 workaround, I could define one or more additional methods in the type tree, taking advantage of dynamic method dispatch to perform the desired operation at the correct level in the hierarchy. The “correct” solution to the narrowing problem depends not just on the version number, though, but also on what my application is supposed to accomplish.

For the moment, I’d like to move on to another interesting area: exploring the features Oracle offers when (not if!) you have to deal with changes in application design.

Libraries classify their holdings within a strictly controlled set of subjects. For example, the Library of Congress might classify the book you’re reading now in the following three subjects:

The Library of Congress uses a hierarchical subject tree: “Computer file” is the broader subject or parent of “Oracle,” and “Computer program language” is the broader subject for “PL/SQL.”

When classifying things, any number of subjects may apply to a particular catalog item in a many-to-many (M:M) relationship between subjects and holdings. In my simple library catalog, I will make one long list (table) of all available subjects. While a relational approach to the problem would then establish an “intersection entity” to resolve the M:M relationship, I have other options out here in object-relational land.

I will start with an object type for each subject:

    CREATE TYPE subject_t AS OBJECT (
       name VARCHAR2(2000),
       broader_term_ref REF subject_t
    );

Each subject has a name and a broader term. However, I’m not going to store the term itself as a second attribute, but instead a reference to it. The third line of this type definition shows that I’ve typed the broader_term_ref attribute as a REF to a same-typed object. It’s kind of like Oracle’s old EMP table, with a MGR column whose value identifies the manager’s record in the same table.

I now create a table of subjects:

    CREATE TABLE subjects OF subject_t
      (CONSTRAINT subject_pk PRIMARY KEY (name),
       CONSTRAINT subject_self_ref FOREIGN KEY (broader_term_ref)
          REFERENCES subjects);

The foreign key begs a bit of explanation. Even though it references a table with a relational primary key, because the foreign key datatype is a REF, Oracle knows to use the table’s object identifier instead. This support for the REF-based foreign key constraint is a good example of Oracle’s bridge between the object and relational worlds.

Here are a few unsurprising inserts into this table (just using the default constructor):

    INSERT INTO subjects VALUES (subject_t('Computer file', NULL));
    INSERT INTO subjects VALUES (subject_t('Computer program language', NULL));
    INSERT INTO subjects VALUES (subject_t('Relational databases', NULL));
    INSERT INTO subjects VALUES (subject_t('Oracle',
       (SELECT REF(s) FROM subjects s WHERE name = 'Computer file')));
    INSERT INTO subjects VALUES (subject_t('PL/SQL',
       (SELECT REF(s) FROM subjects s WHERE name = 'Computer program language')));

For what it’s worth, you can list the contents of the subjects table, as shown here:

    SQL>SELECT VALUE(s) FROM subjects s;

    VALUE(S)(NAME, BROADER_TERM_REF)
    ---------------------------------------------------------------------------------
    SUBJECT_T('Computer file', NULL)
    SUBJECT_T('Computer program language', NULL)
    SUBJECT_T('Oracle', 00002202089FC431FBE6FB0599E0340003BA0F1F139FC431FBE6690599E03
    40003BA0F1F13)

    SUBJECT_T('PL/SQL', 00002202089FC431FBE6FC0599E0340003BA0F1F139FC431FBE6690599E03
    40003BA0F1F13)

    SUBJECT_T('Relational databases', NULL)

Even if that’s interesting, it’s not terribly useful. However, what’s both interesting and useful is that I can easily have Oracle automatically “resolve” or follow those pointers. For example, I can use the DEREF function to navigate those ugly REFs back to their target row in the table:

    SELECT s.name, DEREF(s.broader_term_ref).name bt
      FROM subjects s;

Dereferencing is like an automatic join, although it’s more of an outer join than an equi-join. In other words, if the reference is null or invalid, the driving row will still appear, but the target object (and column) will be null.

Oracle introduced a dereferencing shortcut that is really quite elegant. You only need to use dot notation to indicate what attribute you wish to retrieve from the target object:

    SELECT s.name, s.broader_term_ref.name bt FROM subjects s;

Both queries produce the following output:

    NAME                           BT
    ------------------------------ ------------------------------
    Computer file
    Computer program language
    Oracle                         Computer file
    PL/SQL                         Computer program language
    Relational databases

As a point of syntax, notice that both forms require a table alias, as in the following:

    SELECT
                        table_alias
                     
                     .ref_column_name.column_name
       FROM tablename 
                     
                        table_alias
                     

You can also use REF-based navigation in the WHERE clause. To show all the subjects whose broader term is “Computer program language,” specify:

    SELECT VALUE(s).name FROM subjects s
     WHERE s.broader_term_ref.name = 'Computer program language';

Although my example table uses a reference to itself, in reality a reference can point to an object in any object table in the same database. To see this in action, let’s return to the definition of the base type catalog_item_t. I can now add an attribute that will hold a collection of REFs, so that each cataloged item can be associated with any number of subjects. First, I’ll create a collection of subject references:

    CREATE TYPE subject_refs_t AS TABLE OF REF subject_t;

Now I’ll allow every item in the catalog to be associated with any number of subjects:

    ALTER TYPE catalog_item_t
       ADD ATTRIBUTE subject_refs subject_refs_t
       CASCADE INCLUDING TABLE DATA;

And now (skipping gleefully over the boring parts about modifying any affected methods in the dependent types), I might insert a catalog record using the following exotic SQL statement:

    INSERT INTO catalog_items
    VALUES (NEW book_t(10007,
       'Oracle PL/SQL Programming',
       'Sept 1997',
        CAST(MULTISET(SELECT REF(s)
                        FROM subjects s
                       WHERE name IN ('Oracle', 'PL/SQL', 'Relational databases'))
          AS subject_refs_t),
       '1-56592-335-9',
       987));

The CAST/MULTISET clause performs an on-the-fly conversion of the subject REFs into a collection, as explained in “Working with Collections” in Chapter 12.

Here is a slightly more understandable PL/SQL equivalent:

    DECLARE
       subrefs subject_refs_t;
    BEGIN
       SELECT REF(s)
         BULK COLLECT INTO subrefs
         FROM subjects s
        WHERE name IN ('Oracle', 'PL/SQL', 'Relational databases'),

       INSERT INTO catalog_items VALUES (NEW book_t(10007,
          'Oracle PL/SQL Programming', 'Sept 1997', subrefs, '1-56592-335-9', 987));
    END;

In English, that code says “grab the REFs to three particular subjects, and store them with this particular book.”

REF-based navigation is so cool that I’ll show another example using some more of that long-haired SQL:

    SELECT VALUE(s).name
      || ' (' || VALUE(s).broader_term_ref.name || ')' plsql_subjects
      FROM TABLE(SELECT subject_refs
                   FROM catalog_items
                  WHERE id=10007) s;

This example retrieves values from the subjects table, including the name of each broader subject term, without ever mentioning the subjects table by name. (The TABLE function converts a collection into a virtual table.) Here are the results:

    PLSQL_SUBJECTS
    ------------------------------------
    Relational databases ()
    PL/SQL (Computer program language)
    Oracle (Computer file)

Other than automatic navigation from SQL, what else does all this effort offer the PL/SQL programmer? Er, well, not a whole lot. References have a slight edge, at least because as theory goes, they are strongly typed—that is, a REF-typed column can point only to an object that is defined on the same object type as the REF. Contrast this behavior with conventional foreign keys, which can point to any old thing as long as the target is constrained to be a primary key or has a unique index on it.

The UTL_REF built-in package performs the dereferencing operation without an explicit SQL call, allowing your application to perform a programmatic lock, select, update, or delete of an object given only its REF. As a short example, I can add a method such as the following to the subject_t type:

    MEMBER FUNCTION print_bt (str IN VARCHAR2)
       RETURN VARCHAR2
    IS
       bt subject_t;
    BEGIN
       IF SELF.broader_term_ref IS NULL
       THEN
          RETURN str;
      ELSE
          UTL_REF.SELECT_OBJECT(SELF.broader_term_ref, bt);
          RETURN bt.print_bt(NVL(str,SELF.name)) || ' (' || bt.name || ')';
       END IF;
    END;

This recursive procedure walks the hierarchy from the current subject to the “topmost” broader subject.

When using the procedures in UTL_REF, the REF argument you supply must be typed to match your object argument. The complete list of subprograms in UTL_REF follows:

All of the UTL_REF subprograms are procedures, not functions,^[*] and the parameters have the unique characteristic of being semiweakly typed. In other words, Oracle doesn’t need to know at compile time what the precise datatypes are, as long as the REF matches the object variable.

I’d like to mention a few more technical points about REFs when dealing with type hierarchies. Assume the following program declarations:

    DECLARE
       book book_t;
       item catalog_item_t;
       itemref REF catalog_item_t;
       bookref REF book_t;

As you have seen, assigning a REF to an “exactly typed” variable works fine:

    SELECT REF(c) INTO itemref
      FROM catalog_items c WHERE id = 10007;

Similarly, you can dereference an object into the exact type, using:

    UTL_REF.select_object(itemref, item);

or:

    SELECT DEREF(itemref) INTO item FROM DUAL;

However, you cannot directly narrow a REF:

    SELECT REF(c)
      INTO bookref    /* Error */
      FROM catalog_items c WHERE id = 10007;

One way to narrow a REF would be to use TREAT, which understands how to narrow references:

    SELECT TREAT(REF(c) AS REF book_t)
      INTO bookref
      FROM catalog_items c WHERE id = 10007;

You can always widen or upcast while dereferencing, whether you are using:

    UTL_REF.select_object(TREAT(bookref AS ref catalog_item_t), item);

(notice the explicit upcast) or:

    SELECT DEREF(bookref) INTO item FROM DUAL;

And, although you cannot narrow or downcast while dereferencing with DEREF, as shown here:

    SELECT DEREF(itemref)
      INTO book   /* Error */
      FROM DUAL;

TREAT can again come to the rescue:

    SELECT DEREF(TREAT(itemref AS REF book_t))
      INTO book
      FROM catalog_items c WHERE id = 10007;

Or, amazingly enough, you can also perform an implicit downcast with UTL_REF:

    UTL_REF.select_object(itemref, book);

Got all that?

Here are a few final comments about object references:

Now let’s move on and take a look at some Oracle features for dealing with data whose type is either unknown or varying.

If I wanted to write a function that would print anything (that is, convert it to a string), I might start with this spec:

    FUNCTION printany (whatever IN ANYDATA) RETURN VARCHAR2;

and hope to invoke the function like this:

    DBMS_OUTPUT.PUT_LINE(printany(SYSDATE));         -- nope
    DBMS_OUTPUT.PUT_LINE(printany(NEW book_t(111));  -- nada
    DBMS_OUTPUT.PUT_LINE(printany('Hello world'));   -- nyet

Unfortunately, those calls won’t work. ANYDATA is actually an encapsulation of other types, and you must first convert the data into the ANYDATA type using one of its built-in static methods:

    DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertDate(SYSDATE));
    DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertObject(NEW book_t(12345)));
    DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertVarchar2('Hello world'));

Don’t think of ANYDATA as an exact replacement for overloading.

Let’s take a look at an implementation of the printany program and see how it figures out how to deal with data of different types. This code is not comprehensive; it deals only with numbers, strings, dates, objects, and REFs, but you could extend it to almost any other datatype.

        /* File on web: printany.fun */
 1   CREATE OR REPLACE FUNCTION printany (adata IN ANYDATA)
 2      RETURN VARCHAR2
 3   AS
 4      aType ANYTYPE;
 5      retval VARCHAR2(32767);
 6      result_code PLS_INTEGER;
 7   BEGIN
 8      CASE adata.GetType(aType)
 9      WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
10          RETURN 'NUMBER: ' || TO_CHAR(adata.AccessNumber);
11       WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
12          RETURN 'VARCHAR2: ' || adata.AccessVarchar2;
13       WHEN DBMS_TYPES.TYPECODE_CHAR THEN
14          RETURN 'CHAR: ' || RTRIM(adata.AccessChar);
15       WHEN DBMS_TYPES.TYPECODE_DATE THEN
16          RETURN 'DATE: ' || TO_CHAR(adata.AccessDate, 'YYYY-MM-DD hh24:mi:ss'),
17       WHEN DBMS_TYPES.TYPECODE_OBJECT THEN
18          EXECUTE IMMEDIATE 'DECLARE ' ||
19                            '   myobj ' || adata.GetTypeName || '; ' ||
20                            '   myad anydata := :ad; ' ||
21                            'BEGIN ' ||
22                            '   :res := myad.GetObject(myobj); ' ||
23                            '   :ret := myobj.print(); ' ||
24                            'END;'
25                            USING IN adata, OUT result_code, OUT retval;
26          retval := adata.GetTypeName || ': ' || retval;
27       WHEN DBMS_TYPES.TYPECODE_REF THEN
28          EXECUTE IMMEDIATE 'DECLARE ' ||
29                            '   myref ' || adata.GetTypeName || '; ' ||
30                            '   myobj ' || SUBSTR(adata.GetTypeName,
31                                           INSTR(adata.GetTypeName, ' ')) || '; ' ||
32                            '   myad anydata := :ad; ' ||
33                            'BEGIN ' ||
34                            '   :res := myad.GetREF(myref); ' ||
35                            '   UTL_REF.SELECT_OBJECT(myref, myobj);' ||
36                            '   :ret := myobj.print(); ' ||
37                           'END;'
38                            USING IN adata, OUT result_code, OUT retval;
39          retval := adata.GetTypeName || ': ' || retval;
40       ELSE
41          retval := '<data of type ' || adata.GetTypeName ||'>';
42       END CASE;
43
44       RETURN retval;
45
46    EXCEPTION
47       WHEN OTHERS
48       THEN
49          IF INSTR(SQLERRM, 'component ''PRINT'' must be declared') > 0
50          THEN
51             RETURN adata.GetTypeName || ': <no print() function>';
52          ELSE
53             RETURN 'Error: ' || SQLERRM;
54          END IF;
55    END;

Here are just a few highlights:

Running my earlier invocations:

    DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertDate(SYSDATE));
    DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertObject(NEW book_t(12345)));
    DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertVarchar2('Hello world'));

yields:

    DATE: 2005-03-10 16:00:25
    SCOTT.BOOK_T: id=12345; title=; publication_date=; isbn=; pages=
    VARCHAR2: Hello world

As you can see, using ANYDATA isn’t as convenient as true inheritance hierarchies because ANYDATA requires explicit conversions. On the other hand, it does make possible the creation of a table column or object attribute that will hold any type of data.^[*]

Although PL/SQL still does not support defining new object types inside a program’s declaration section, it is possible to use these ANY built-ins to create this kind of “transient” type—that is, one that exists only at runtime. Wrapped up as an ANYTYPE, you can even pass such a type as a parameter and create an instance of it as an ANYDATA. Here is an example:

    /* Create (anonymous) transient type with two attributes: number, date */
    CREATE OR REPLACE FUNCTION create_a_type
       RETURN ANYTYPE
    AS
       mytype ANYTYPE;
    BEGIN
       ANYTYPE.BeginCreate(typecode => DBMS_TYPES.TYPECODE_OBJECT,
                           atype => mytype);

       mytype.AddAttr(typecode => DBMS_TYPES.TYPECODE_NUMBER,
                      aname => 'just_a_number',
                      prec => 38,
                      scale => 0,
                      len => NULL,
                      csid => NULL,
                      csfrm => NULL);

       mytype.AddAttr(typecode => DBMS_TYPES.TYPECODE_DATE,
                      aname => 'just_a_date',
                      prec => 5,
                      scale => 5,
                      len => NULL,
                      csid => NULL,
                      csfrm => NULL);

       mytype.EndCreate;
       RETURN mytype;
    END;
    /

As you can see, there are three main steps:

Similarly, when you wish to use the type, you will need to assign attribute values in a piecewise manner:

    DECLARE
       ltype ANYTYPE := create_a_type;
       l_any ANYDATA;
    BEGIN
       ANYDATA.BeginCreate(dtype => ltype, adata => l_any);
       l_any.SetNumber(num => 12345);
       l_any.SetDate(dat => SYSDATE);
       l_any.EndCreate;
    END;

If you don’t know the structure of the datatype in advance, it is possible to discover it using ANYTYPE methods (such as GetAttrElemInfo) in combination with a piecewise application of the ANYDATA.Get methods. (See the anyObject.sql script on the book’s web site for an example.)

Attribute-level comparison may not be precisely what you want, but it is fairly easy in PL/SQL, or even in SQL if you remember to use a table alias in the SQL statement. Oracle lets you expose attributes via dot notation:

    SELECT * FROM catalog_history c
     WHERE c.old_item.id > 10000
     ORDER BY NVL(TREAT(c.old_item as book_t).isbn, TREAT(c.old_item AS serial_t).issn)

Attribute-level index creation is equally easy:

    CREATE INDEX catalog_history_old_id_idx ON catalog_history c (c.old_item.id);

Both the MAP and the ORDER methods make it possible to perform statements such as the following:

    SELECT * FROM catalog_history
     ORDER BY old_item;

    IF old_item > new_item
    THEN ...

First let’s look at MAP. I can add a trivial MAP method to catalog_item_t as follows:

    ALTER TYPE catalog_item_t
       ADD MAP MEMBER FUNCTION mapit RETURN NUMBER
       CASCADE;

    CREATE OR REPLACE TYPE BODY catalog_item_t
    AS ...
       MAP MEMBER FUNCTION mapit RETURN NUMBER
       IS
       BEGIN
          RETURN id;
       END;
       ...
    END;

Assuming, of course, that ordering by id makes sense, now I can order and compare catalog items to my heart’s content, and Oracle will call this method automatically whenever necessary. The function needn’t be so simple; for example, it could return a scalar value computed from all the object attributes, melded together in some way that actually might be of some value to librarians.

Creating a MAP method like this has a side effect, though: the equality comparison gets defined in a way you might not like. “Equality” now becomes a matter of the mapped value’s being equal for the objects you’re comparing. If you want an easy way to compare two objects for attribute-by-attribute equality, you will want to either create your own (non-MAP) method and invoke it by name when needed, or use an ORDER method.

The alternative to MAP is an ORDER member function, which compares two methods: SELF, and another object of the same type that you supply as an argument. You want to program the function to return an integer that is positive, zero, or negative, indicating the ordering relationship of the second object to SELF. Table 25-2 illustrates the behavior you need to incorporate.

Let’s take a look at a nontrivial example of an ORDER method:

 1   ALTER TYPE catalog_item_t
 2      DROP MAP MEMBER FUNCTION mapit RETURN NUMBER
 3      CASCADE;
 4
 5   ALTER TYPE catalog_item_t
 6      ADD ORDER MEMBER FUNCTION orderit (obj2 IN catalog_item_t)
 7         RETURN INTEGER
 8      CASCADE;
 9
10    CREATE OR REPLACE TYPE BODY catalog_item_t
11    AS ...
12       ORDER MEMBER FUNCTION orderit (obj2 IN catalog_item_t)
13          RETURN INTEGER
14       IS
15          self_gt_o2 CONSTANT PLS_INTEGER := 1;
16          eq CONSTANT PLS_INTEGER := 0;
17          o2_gt_self CONSTANT PLS_INTEGER := -1;
18          l_matching_count NUMBER;
19       BEGIN
20          CASE
21             WHEN obj2 IS OF (book_t) AND SELF IS OF (serial_t) THEN
22                RETURN o2_gt_self;
23             WHEN obj2 IS OF (serial_t) AND SELF IS OF (book_t) THEN
24                RETURN self_gt_o2;
25             ELSE
26                IF obj2.title = SELF.title
27                   AND obj2.publication_date = SELF.publication_date
28                THEN
29                   IF obj2.subject_refs IS NOT NULL
30                      AND SELF.subject_refs IS NOT NULL
31                      AND obj2.subject_refs.COUNT = SELF.subject_refs.COUNT
32                   THEN
33                      SELECT COUNT(*) INTO l_matching_count FROM
34                         (SELECT *
35                           FROM TABLE(SELECT CAST(SELF.subject_refs AS subject_refs_t)
36                                       FROM dual)
37                         INTERSECT
38                         SELECT *
39                           FROM TABLE(SELECT CAST(obj2.subject_refs AS subject_refs_t)
40                                        FROM dual));
41                      IF l_matching_count = SELF.subject_refs.COUNT
42                      THEN
43                         RETURN eq;
44                      END IF;
45                   END IF;
46                END IF;
47                RETURN NULL;
48          END CASE;
49       END;
50       ...
51    END;

Here are the important things to note:

Overall, however, my ORDER method is still inadequate because it fails to treat the subtype-specific attributes, but anything longer would just be too unwieldy for this book.

To close out this discussion, here are a few additional rules and recommendations for comparison methods:

On the one hand, you could use PL/SQL programs such as packages and object methods to provide a more comprehensive technique than triggers for encapsulating DML. It is nearly trivial to take the logic from our INSTEAD OF trigger and put it into an alternate PL/SQL construct that has more universal application. In other words, if you’ve already standardized on some combination of packages and methods as the means of performing DML, you could keep your environment consistent without using view triggers. You might conclude that view triggers are just added complexity in an increasingly confusing equation.

Moreover, even Oracle cautions against the “excessive use” of triggers because they can cause “complex interdependencies.” Imagine if your INSTEAD OF triggers performed DML on tables that had other triggers, which performed DML on still other tables with triggers ... it’s easy to see how this could get impossible to debug.

On the other hand, you can put much of the necessary logic that you would normally put into a package or method body into an INSTEAD OF trigger instead. Doing this in combination with a proper set of privilege restrictions could protect your data just as well as, or even better than, methods or packages.

If you happen to use a client tool such as Oracle Forms, INSTEAD OF triggers allow you to use much more of the product’s default functionality when you create a Forms “block” against a view rather than a table.

Finally, if you use OCI, INSTEAD OF triggers are required if the object view is not inherently modifiable, and you want to be able to easily “flush” cached object view data back to the server.

The bigger question is this: what’s the best place for the SQL statements that insert, update, and delete data, especially when using object views? Assuming that you want to localize these operations on the server side, you have at least three choices: PL/SQL packages, object methods, and INSTEAD OF triggers.

Table 25-3 summarizes some of the major considerations of the three techniques. Note that this table is not meant to compare these approaches for general-purpose use, but only as they apply to localizing DML on object views .

As you can see, there is no clear “winner.” Each technique has benefits that may be of more or less importance to your application.

One important point about using INSTEAD OF triggers in view hierarchies is that you will need a separate trigger for each level of the hierarchy. When you perform DML through a subview, the subview’s trigger will fire; when you perform DML through the superview, the superview’s trigger will fire.

And of course, you may decide that INSTEAD OF triggers make sense in combination with PL/SQL packages and/or object methods to provide layers of encapsulation. For example:

    CREATE OR REPLACE TRIGGER images_v_insert
    INSTEAD OF INSERT ON images_v
    FOR EACH ROW
    BEGIN
       /* Call a packaged procedure to perform the insert. */
       manage_image.create_one(:NEW.image_id, :NEW.file_type,
          :NEW.file_name, :NEW.bytes, :NEW.keywords);
    END;

In an ideal world, developers would select an overall architecture and design approach before hurling every Oracle feature at their application. Use a feature only if it make sense for your design. I agree with Oracle’s advice that if you do use triggers, you should use them in moderation.

An object table will always have a unique object identifier, either system-generated or derived from the primary key. It is technically possible—though poor practice—to create an object table with duplicate rows, but the instances will still be unique in their object identifier. This can happen in two different ways:

It seems more likely that this second possibility of duplication would be legitimate in your application because separate views are just separate stored queries.

If you’ve built an application with physical object tables, you can store REFs to those objects persistently in other tables. A REF is a binary value that Oracle can use as a pointer to an object.

However, Oracle returns an error if you attempt to store a virtual REF—that is, a REF to a row of an object view—in an actual table. Because the reference depends on some column value(s), you will need to save the underlying column value(s) instead of the virtual reference. From one perspective, this is an irritant rather than a major obstacle. Still, it’s a bit unpleasant that we cannot intermingle object tables with object views, nor can we perform a simple transformation from an object view into an object table. I would like to be able to create an object table:

    CREATE TABLE images2 OF image_t
       NESTED TABLE keywords STORE AS keyword_tab;

and then populate it from the view:

    INSERT INTO images2       /* invalid because images_v includes a REF */
     SELECT VALUE(i) FROM images_v i;

But alas, Oracle tells me ORA-22979: cannot INSERT object view REF or user-defined REF. Life goes on, however.

I don’t believe that it is possible to have a REF to a non-unique OID when dealing with object tables. You may want to consider what will happen if you create a REF to an object in an object view, but the view has multiple object instances for the OID in question. Granted, this is a pretty weird case; you shouldn’t be creating object views with ambiguous OIDs.

In my testing, DEREFing this type of virtual REF did indeed return an object—apparently, the first one Oracle found that matched. In previous versions of Oracle, this operation returns a NULL. I don’t think I would count on either behavior in an application.

If you want your associate Joe to use one of your types in his own PL/SQL programs or tables, you can grant the EXECUTE privilege to him:

    GRANT EXECUTE on catalog_item_t TO joe;

If Joe has the privilege needed to create synonyms and is running Oracle9i Database Release 2 or later, he will be able to create a synonym:

    CREATE SYNONYM catalog_item_t FOR scott.catalog_item_t;

and use it as follows:

    CREATE TABLE catalog_items OF catalog_item_t;

and/or:

    DECLARE
      an_item catalog_item_t;

Joe can also use a qualified reference to the type scott.catalog_item_t.

If you refer to an object type in a stored program and grant EXECUTE privilege on that program to a user or role, having EXECUTE on the type is not required, even if the program is defined using invoker rights (described in Chapter 23). Similarly, if a user has a DML privilege on a view that has an INSTEAD OF trigger for that DML operation, that user doesn’t need explicit EXECUTE privileges if the trigger refers to the object type because triggers run under the definer rights model. However, the EXECUTE privilege is required by users who need to run anonymous blocks that use the object type.

The UNDER privilege gives the grantee the right to create a subtype. You can grant it as follows:

    GRANT UNDER ON image_t TO scott;

For a schema to be able to create a subtype, the supertype must be defined using invoker rights (AUTHID CURRENT_USER).

This privilege can also grant the recipient the right to create a subview:

    GRANT UNDER ON images_v TO scott;

If one of your associates is using a PL/SQL debugger to analyze code that uses a type you have created, you may want to grant him the DEBUG privilege:

    GRANT DEBUG ON image_t TO joe;

Doing so will enable the grantee to look “under the covers” to examine the variables used in the type and to set breakpoints inside methods.

The DEBUG privilege also applies to object views, providing a way to debug the PL/SQL source code of INSTEAD OF triggers.

For object tables, the traditional SELECT, INSERT, UPDATE, and DELETE privileges still have some meaning. A user with only SELECT privilege on the object table may retrieve any relational columns in the base type on which the table is defined, but cannot retrieve the object-as-object. That is, VALUE, TREAT, REF, and DEREF are not available. Similarly, the other DML privileges, INSERT, UPDATE, and DELETE, also apply only to the relational interpretation of the table.

In the same fashion, the grantee will not have permission to use the constructor or other object methods unless the object type owner has granted the user EXECUTE privilege on the object type. Any columns defined on subtypes will be invisible.