Evolution and Creation

I have little but good news here: Oracle9i is light years beyond Oracle8i in the area known as type evolution. That is, Oracle now lets you make a variety of changes to object types, even if you have created tables full of objects that depend on the type. Yippee!

Earlier in this chapter, I did a quick-and-dirty job of defining catalog_item_t. As almost any friendly librarian would point out, it might also be nice to carry publication date information^[8] about all the holdings in the library. So I just hack out the following (no doubt while my DBA cringes):

^[8] I can't make this attribute an Oracle DATE type, though, because sometimes it's just a year, sometimes a month or a quarter, and occasionally something completely offbeat. I might get really clever and make this a nifty object type . . . well, maybe in the movie version.

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

Et voilà! Oracle propagates this change to perform the needed physical alterations in the corresponding table(s). It appends the attribute to the bottom of the supertype's attributes, and adds a column after the last column of the supertype in the corresponding object table. A DESCRIBE of the type now looks like this:

SQL> DESC catalog_item_t catalog_item_t is NOT FINAL catalog_item_t is NOT INSTANTIABLE Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(38) TITLE VARCHAR2(4000) PUBLICATION_DATE VARCHAR2(400) METHOD------ MEMBER FUNCTION CK_DIGIT_OKAY RETURNS BOOLEAN CK_DIGIT_OKAY IS NOT INSTANTIABLE METHOD------ MEMBER FUNCTION PRINT RETURNS VARCHAR2

And a DESCRIBE of the table now looks like this:

SQL> DESC catalog_items Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(38) TITLE VARCHAR2(4000) PUBLICATION_DATE VARCHAR2(400)

In fact, the ALTER TYPE statement fixes nearly everything—though alas, it isn't smart enough to rewrite my methods. My constructors are a particular issue because I need to alter their signature. Hey, no problem! I can change a method signature by dropping and then re-creating the method.

When evolving object types, you may encounter the message ORA-22337: the type of accessed object has been evolved. This condition may prevent you from doing a DESCRIBE on the type. You might think that recompiling it will fix the problem, but it won't. Moreover, if you have hard dependencies on the type, Oracle won't let you recompile the object type specification. To get rid of this error, disconnect and then reconnect your Oracle session. This clears various buffers and enables DESCRIBE to see the new version.

To drop the method from the book type specification, specify:

ALTER TYPE book_t DROP CONSTRUCTOR FUNCTION book_t (id INTEGER DEFAULT NULL, title VARCHAR2 DEFAULT NULL, isbn VARCHAR2 DEFAULT NULL, pages INTEGER DEFAULT NULL) RETURN SELF AS RESULT CASCADE;

Notice that I supply the full function specification. That will guarantee that I'm dropping the correct method, as multiple overloaded versions of it might exist. (Strictly speaking, though, the DEFAULTs are not required, but I left them in because I'm usually just cutting and pasting this stuff.)

The corresponding add-method operation is easy:

ALTER TYPE book_t ADD CONSTRUCTOR FUNCTION book_t (id INTEGER DEFAULT NULL, title VARCHAR2 DEFAULT NULL, publication_date VARCHAR2 DEFAULT NULL, isbn VARCHAR2 DEFAULT NULL, pages INTEGER DEFAULT NULL) RETURN SELF AS RESULT CASCADE;

Easy for me, anyway; Oracle is doing a lot more stuff behind the scenes than I will probably ever know.

The next steps (not illustrated in this chapter) would be to alter the serial_t type in a similar fashion and then rebuild the two corresponding object type bodies with the CREATE OR REPLACE TYPE BODY statement. I would also want to inspect all the methods to see whether any changes would make sense elsewhere (for example, it would be a good idea to include the publication date in the print method).

By the way, you can drop a type using the statement:

DROP TYPE typename [ FORCE ];

Use the FORCE option with care because it cannot be undone. Any object types or object tables that depend on a force-dropped type will be rendered permanently useless. If there are any columns defined on a force-dropped type, Oracle marks them as UNUSED and makes them inaccessible. If your type is a subtype and you have used the supertype in any table definitions, you might benefit from this form of the statement:

DROP TYPE subtypename VALIDATE;

VALIDATE causes Oracle to look through the table and drop the type as long as there are no instances of the subtype, avoiding the disastrous consequences of the FORCE option.

Now let's visit the strange and fascinating world of object referencing.

21.2.5 Back to Pointers?

The object-relational features in Oracle include the ability to store an object reference or REF value. A REF is a logical pointer to a particular row in an object table. Oracle stores inside each reference the following information:

· The target row's primary key or system-generated object identifier

· A unique identifier to designate the table

· At the programmer's option, a hint on the row's physical whereabouts on disk, in the form of its ROWID

The literal contents of a REF are not terribly useful unless you happen to like looking at long hex strings:

SQL> SELECT REF(c) FROM catalog_items c WHERE ROWNUM = 1; REF(C)--------------------------------------------------------------------------------------00002802099FC431FBE5F20599E0340003BA0F1F139FC431FBE5F10599E0340003BA0F1F130240000C0000

However, your queries and programs can use a REF to retrieve a row object without having to name the table where the object resides. Huh? Queries without table names? A pointer in a relational database? Let's take a look at how this feature might work in our library catalog.