The VALUE function

Data manipulation on object tables introduces a few twists into the SQL syntax. For example, I can create an object with my constructor and use it in a somewhat normal INSERT statement:^[7]

^[7] I would prefer to use named notation in these static function calls, but Oracle does not support named notation when calling any kind of PL/SQL function from SQL (at least as of Oracle9i Release 2).

INSERT INTO catalog_items VALUES (NEW book_t(10003, 'Perelandra', '0-684-82382-9', 222));INSERT INTO catalog_items VALUES (NEW serial_t(10004, 'Time', '0040-781X', 'O'));

Emulating Global Constants with Object Methods Students of the design approaches advocated elsewhere in this book series will immediately object to the hardcoding of magic values such as `O' for Open. I could substitute a package constant there, or I could create a static method called something like open_c, which would return the chosen value. I would want to use a static method for this purpose because Oracle object types do not support global constants, global variables, or global anything, the way that packages do. In this case, my INSERT would look like this: INSERT INTO catalog_items VALUES (NEW serial_t(10002, 'Time', '0040-781X', serial_t.open_c)); The body of the corresponding static method is just a RETURN 'O' statement, but this coding practice has advantages such as ensuring consistency and accommodating requirements changes in the future.

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 display objects, 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:

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—defined on some subtype I don't know in advance—into the variable. This is not only elegant, but also quite useful!

The example also illustrates (by printing catalog_item.id) that I have immediate 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 supportsdot 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. Direct access to subtype attribute won't work. */ 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. Direct assignment to subtype won't work either. */

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 performingDML on object relational tables:

· For object tables built on object types that lack subtypes, it is possible to select, insert, update, and delete all column values using conventional SQL statements. In this way, some object-oriented and relational programs can share the same underlying data.

· You cannot perform conventional relational DML on hidden columns that exist as a result of subtype-dependent attributes. You must use an "object DML" approach.

· To update an entire persistent object from a PL/SQL program, you can use an object DML statement such as:

UPDATE catalog_items c SET c = object_variable WHERE ...

That will update all the attributes (columns), including those unique to a subtype.

· The only good way I have found to update a specific column that is unique to a subtype is to update the entire object. For example, to change the page count to 1000 for the book with id 10007, specify: