Comparing Objects

So far, my examples have used object tables—tables in which each row constitutes an object built with the CREATE TABLE...OF type statement. As I've illustrated, such an arrangement enjoys some special features, such as REF-based navigation and the treatment of entire objects (rather than individual column values) as the unit of I/O.

You can also use an object type as the datatype for individual columns in a table. For example, imagine that I want to create an historical record of changes in the catalog_items table, capturing all inserts, updates, and deletes.[11]

[11] It might seem reasonable to expect DML triggers on the catalog_items table to populate this history table automatically. Unfortunately, inside a trigger there is no access to attributes specific to lower-level subtypes. With a trigger, you can only capture the history of the common (supertype) attributes.

CREATE TABLE catalog_history ( id INTEGER NOT NULL PRIMARY KEY, action CHAR(1) NOT NULL, action_time TIMESTAMP DEFAULT (SYSTIMESTAMP) NOT NULL, old_item catalog_item_t, new_item catalog_item_t) NESTED TABLE old_item.subject_refs STORE AS catalog_history_old_subrefs NESTED TABLE new_item.subject_refs STORE AS catalog_history_new_subrefs;

As soon as you start populating a table with column objects, though, you raise some questions about how Oracle should behave when you ask it to do things like sort or index on one of those catalog_item_t columns. Well, there are four different ways you can compare objects, though some are more useful than others:

 

Attribute-level comparison

Include the relevant attribute(s) when sorting, creating indexes, or comparing.

 

Default SQL

Oracle's SQL knows how to do a simple equality test. In this case, two objects are considered equal if they are defined on exactly the same type and every corresponding attribute is equal. This will work if the objects have only scalar attributes (no collections or LOBs) and if you haven't already defined a MAP or ORDER member method on the object type.

 

MAP member method

You can create a special function method that returns a "mapping" of the object value onto a datatype that Oracle already knows how to compare, such as a number or a date. This will work only if no ORDER method exists.

 

ORDER member method

This is another special function that compares two objects and returns a flag value that indicates their relative ordering. This will work only if no MAP method exists.

Default SQL comparison is not terribly useful, so I won't say any more about it. The following sections describe the other, more useful ways to compare objects.