I Can Do It Myself

In object-oriented design, there is a school of thought that wants each object type to have the intelligence necessary to be self-sufficient. If the object needs to be stored persistently in a database, it would know how to save itself; similarly, it would include methods for update, delete, and retrieval. If I subscribed to this philosophy, here is one of the methods I would want to add to my type:

ALTER TYPE catalog_item_t ADD MEMBER PROCEDURE remove CASCADE; CREATE OR REPLACE TYPE BODY catalog_item_tAS ... MEMBER PROCEDURE remove IS BEGIN DELETE catalog_items WHERE id = SELF.id; SELF := NULL; END;END;

(Oracle does not offer a destructor method, by the way.) By defining this method at the supertype level, all my subtypes are taken care of too. This design assumes that corresponding objects will live in a single table; some applications might need some additional logic to locate the object. (Also, a real version of this method might include logic to perform ancillary functions like removing dependent objects and/or archiving the data before removing the object permanently.)

Assuming that my applications would always modify a transient object in memory before writing it to disk, I could combine insert and update into a single method I'll call "save":

ALTER TYPE catalog_item_t ADD MEMBER PROCEDURE save, CASCADE; CREATE OR REPLACE TYPE BODY catalog_item_tAS ... MEMBER PROCEDURE save IS BEGIN UPDATE catalog_items c SET c = SELF WHERE id = SELF.id; IF SQL%ROWCOUNT = 0 THEN INSERT INTO catalog_items VALUES (SELF); END IF; END;

You may correctly point out that this will replace all of the column values in the table even if they are unchanged, which could cause triggers to fire that shouldn't, and results in needless I/O. Alas, this is one of the unfortunate by-products of an object approach. It is true that with careful programming, you could avoid modifying columns from the supertype that haven't changed, but columns from any subtype are not individually accessible from any variation on the UPDATE statement that Oracle currently offers.

Retrieval is the most difficult operation to encapsulate, because of the many WHERE-clause permutations and the multi-set nature of the result. The specification of the query criteria can be a real rat's nest, as anyone who has ever built a custom query screen will attest. Considering only the result side, the options for what to return include:

· A collection of objects

· A collection of REFs

· A pipelined result set

· A cursor variable (strongly or weakly typed)

The requirements of the application and its programming environment will have the largest influence on how to choose from these options. Here's an example that uses the fourth approach, a cursor variable:

ALTER TYPE catalog_item_t ADD MEMBER FUNCTION retrieve_matching RETURN SYS_REFCURSOR CASCADE;

I use the built-in SYS_REFCURSOR type, which is a weak cursor type that Oracle provides just for convenience. The idea of this function is that the calling application will instantiate some object as a "sample record," invoke the retrieve_matching function on that object, and then use the returned cursor variable as the handle to the result set. Jumping ahead to how you might execute a query, let's look at this example:

DECLARE catalog_item catalog_item_t; l_refcur SYS_REFCURSOR; l_sample_object book_t := NEW book_t(title => 'Oracle%'); BEGIN l_refcur := l_sample_object.retrieve_matching( ); LOOP FETCH l_refcur INTO catalog_item; EXIT WHEN l_refcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(catalog_item.print); END LOOP; CLOSE l_refcur;END;

One of the issues with this specification, though, is whether the query should retrieve all matching catalog items, or only those matching the datatype of the sample object. If the answer is "all," the corresponding implementation could look like this:

CREATE OR REPLACE TYPE BODY catalog_item_tAS ... MEMBER FUNCTION retrieve_matching RETURN SYS_REFCURSOR IS l_refcur SYS_REFCURSOR; BEGIN IF SELF IS OF (book_t) THEN OPEN l_refcur FOR SELECT VALUE(c) FROM catalog_items c WHERE (SELF.id IS NULL OR id = SELF.id) AND (SELF.title IS NULL OR title LIKE SELF.title || '%') AND (SELF.publication_date IS NULL OR publication_date = SELF.publication_date) AND (TREAT(SELF AS book_t).isbn IS NULL OR TREAT(VALUE(c) AS book_t).isbn = TREAT(SELF AS book_t).isbn) AND (TREAT(SELF AS book_t).pages IS NULL OR TREAT(VALUE(c) AS book_t).pages = TREAT(SELF AS book_t).pages); ELSIF SELF IS OF (serial_t) THEN OPEN l_refcur FOR SELECT VALUE(c) FROM catalog_items c WHERE (SELF.id IS NULL OR id = SELF.id) AND (SELF.title IS NULL OR title LIKE SELF.title || '%') AND (SELF.publication_date IS NULL OR publication_date = SELF.publication_date) AND (TREAT(SELF AS serial_t).issn IS NULL OR TREAT(VALUE(c) AS serial_t).issn = TREAT(SELF AS serial_t).issn) AND (TREAT(SELF AS serial_t).open_or_closed IS NULL OR TREAT(VALUE(c) AS serial_t).open_or_closed = TREAT(SELF AS serial_t).open_or_closed); END IF; RETURN l_refcur; END;END;

I'm not completely satisfied with this approach, because it requires modifying this method in the supertype every time I add a subtype to the inheritance tree. I could override retrieve_matching methods in the subtypes, but when I later want a result set that includes both books and serials, there is no way to invoke the method in the supertype. In that case, I would wind up with the non-intuitive situation of a book query retrieving serials, or vice versa.

Although I could change the supertype to be instantiable, perhaps a better approach would be to change the retrieve_matching function into a static method. I leave this change as an exercise for the reader.