20.4.1.1 Duplicate OIDs in a single view

An object view can easily contain multiple object instances (rows) for a given OID. Let's look at how that can happen.

In our earlier example, we chose to "smush" the detail table into a collection and use the primary key of the parent table as the (unique) OID of the view. If for some reason we had used a simple join on the foreign key, a given value of the primary key of the parent could indeed yield a nonunique OID. This would require a different underlying object type where the "keywords" attribute is a scalar rather than a collection:

CREATE TYPE Image_keyword_t AS OBJECT (
   image_id INTEGER,
   file_name VARCHAR2(512),
   file_type VARCHAR2(12),
   bytes INTEGER,
   keywords VARCHAR2(45)
);

The object view could then be created as follows.

CREATE VIEW image_keyword_v
   OF Image_keyword_t
   WITH OBJECT OID (image_id)
AS
   SELECT i.image_id, i.file_name, i.file_type, i.bytes, k.keyword
     FROM keywords k, images i
    WHERE k.image_id = i.image_id;

This duplication of OIDs may make sense for some applications; however, in the more likely case that you want to avoid duplicate OIDs, simply include enough attributes to make it unique:

WITH OBJECT OID (image_id, keywords)

20.4.1.2 Duplicate OIDs across multiple views

There are other scenarios in which object views duplicate object identifiers used elsewhere. For example, if your object view is defined on an underlying object table or view and if you use the DEFAULT keyword to specify the OID, the view contains OIDs that match the OIDs of the underlying construct:

CREATE VIEW gif_images_v
   OF Image_t
   WITH OBJECT OID DEFAULT
AS
   SELECT *
     FROM images_v
    WHERE file_type = 'GIF';

In this case, all of the OIDs that Oracle uses for instances (rows) of gif_images_v will match existing OIDs of the images_v view.

Logically, this possibility of "duplication" makes a certain amount of sense. A view is often thought of as a "stored query." Used here, the query simply retrieves a subset of the underlying objects and does not modify them in any way.

NOTE: Just because your object view is defined on an object table, you don't have to use the underlying OID. If you want the OIDs of the objects in gif_images_v to be unique from those of the underlying object, you could simply specify the image_id attribute as the OID:

CREATE VIEW gif_images_v
   OF Image_t
   WITH OBJECT OID (image_id)
   AS SELECT * 
        FROM images_v
       WHERE file_type = 'GIF';

In this view, Oracle will use its internal algorithm to derive unique OIDs for the objects retrieved via gif_images_v.1

20.4.2.1 Defining virtual REFs

As it turns out, the table of images we mentioned earlier includes a column that designates the creator of the image, as a foreign key to a table of artists. Our intention is to make the images_v object view include a REF to an object view of the artists table.

The (admittedly simplistic) table of artists looks like this:

CREATE TABLE artists (
   id INTEGER NOT NULL,
   name VARCHAR2(60),
   CONSTRAINT artists_pk PRIMARY KEY (id)
);

Here's the images table definition, with the new foreign key defined:

CREATE TABLE images (
   image_id INTEGER NOT NULL,
   file_name VARCHAR2(512),
   file_type VARCHAR2(12),
   bytes INTEGER,
   artist_id INTEGER,
   CONSTRAINT image_pk PRIMARY KEY (image_id),
   CONSTRAINT image_created_by_artist FOREIGN KEY (artist_id)
      REFERENCES artists (id)
);

Because we will use the Oracle function MAKE_REF, which only works with object views, we will need to define an object type for artists:

CREATE TYPE Artist_t AS OBJECT (
   id INTEGER,
   name VARCHAR2(60)
);

Now we can layer the Artist_t type on the artists table using an object view:

CREATE VIEW artists_v
   OF Artist_t
   WITH OBJECT OID (id)
AS
   SELECT id, name
     FROM artists;

We need to redefine the image_t type to include the REF:

CREATE TYPE Image_t AS OBJECT (
   image_id INTEGER,
   file_name VARCHAR2(512),
   file_type VARCHAR2(12),
   bytes INTEGER,
   artist_ref REF Artist_t,
   keywords Keyword_tab_t,
   MEMBER FUNCTION set_attrs (new_file_name IN VARCHAR2,
      new_file_type IN VARCHAR2, new_bytes IN INTEGER)
      RETURN Image_t,
   MEMBER FUNCTION set_keywords (new_keywords IN Keyword_tab_t)
      RETURN Image_t,
   PRAGMA RESTRICT_REFERENCES (DEFAULT, RNDS, WNDS, RNPS, WNPS)
);

After all of this preparatory work, we can at last show the new version of the object view. This statement uses the built-in MAKE_REF function, which accepts a key value and returns a REF to an object view:

CREATE VIEW images_v
   OF Image_t
   WITH OBJECT OID (image_id)
AS
   SELECT i.image_id, i.file_name, i.file_type, i.bytes,
      MAKE_REF (artists_v, i.artist_id),
      CAST (MULTISET (SELECT keyword
                        FROM keywords k
                       WHERE k.image_id = i.image_id)
            AS Keyword_tab_t)
     FROM images i;

The expression in bold above means "construct the REF that should point to the artists_v virtual object with artist ID of i.artist_id." I say "should" rather than "will" because Oracle does not check to make sure that the artist_id actually exists.

Let's take a closer look at the MAKE_REF in the example above:

MAKE_REF (artists_v, i.artist_id)

The first argument of this function, artist_v, is the target object view. The second argument, i.artist_id, is the value which MAKE_REF will convert into an OID to retrieve an instance of the virtual artist object.

It is not strictly necessary to have a foreign key, or even a primary key, in order to use MAKE_REF. But in almost all cases, it is highly desirable to make sure the target of the REF is a primary key-based OID, and that the REF is based on a foreign key.

20.4.2.2 Using virtual REFs

Where does all this get us? Once we have the view with its virtual REF, we can issue SQL statements that use dot navigation:

SELECT image_id, i.artist_ref.name

  FROM images_v i
 WHERE file_name = 'cheesefries.jpg';

On the surface, this statement is indistinguishable from what we might use with object tables. But there is a very important difference:

Using MAKE_REF in a view to convert a foreign key into a REF will produce an error if the foreign key value is NULL.

To illustrate this inconvenient behavior, let's put some data in the underlying artist and image tables:

INSERT INTO artists VALUES (100, 'Sam Picasso');

/* Insert an image with a valid artist id (for Case 1) */
INSERT INTO images VALUES (1000, 'cheesefries.jpg', 'JPG', 2097, 100);

/* Insert an image with an invalid artist id (must first disable the
|| foreign key constraint.)  This is for Case 2.
*/
ALTER TABLE images
   DISABLE CONSTRAINT image_created_by_artist;

INSERT INTO images VALUES (1002, 'sodajerk.jpg', 'JPG', 813, 99);

/* Insert an image with a NULL artist id. This is for Case 3. */
INSERT INTO images VALUES (1001, '57chevy.gif', 'GIF', 3128, NULL);

Let's run each of three cases and assess the results.

Case 1 is the "normal" case in which the artist_id actually exists:

/* Case 1 */
SELECT image_id, i.artist_ref.name

  FROM images_v i
 WHERE file_name = 'cheesefries.jpg';

 IMAGE_ID ARTIST_REF.NAME
--------- ------------------------------------------------------------
     1000 Sam Picasso

That works just fine -- just as if images_v and artists_v were object tables. As long as there is a valid foreign key, we can use dot navigation until the cows come home.

Case 2 illustrates what happens when the artist_id in the images table is non-null, but does not point to a valid artist_id in the artists table. In other words, you have a referential integrity problem -- we had to disable the foreign key to try this one out:

/* Case 2 */
SELECT image_id, i.artist_ref.name
  FROM images_v i
 WHERE file_name = 'sodajerk.jpg';

 IMAGE_ID ARTIST_REF.NAME
--------- ------------------------------------------------------------
     1002

Here, Oracle simply returns i.artist_ref.name as a NULL. This behavior matches that of object tables; if the object corresponding to the REF doesn't exist, you get a NULL back. Another name for this unknown reference value is a dangling REF.

Case 3 is the problem child. For the 57chevy.gif record, the artist_id field in the images table is simply null. This might be a common occurrence if, for example, the artist is unknown.

/* Case 3 */
SELECT image_id, i.artist_ref.name
  FROM images_v i
 WHERE file_name = '57chevy.gif';

Oracle replies, somewhat unforgivingly:

ERROR at line 3:
ORA-22972: NULL value not allowed in PRIMARY KEY-based object identifier

We get the same Oracle error whether or not the foreign key constraint is enabled. This behavior is vastly different from that of object tables, which simply return a null (as in the previous case). Personally, I prefer the behavior of object tables, in which REF-based navigation is more like a relational "outer join."

20.4.2.3 Working around the ORA-22972 problem

One workaround for this behavior is to use a DECODE in the MAKE_REF so that null artist_ids get converted to some weird value that will never appear in the artists table. Since Oracle doesn't complain if an artist_id doesn't exist (as illustrated in Case 2) the silent response results in a null -- which is the desired result.

CREATE VIEW images_v
   OF Image_t
   WITH OBJECT OID (image_id)
AS
   SELECT i.image_id, i.file_name, i.file_type, i.bytes,
          MAKE_REF (artists_v,
             DECODE(i.artist_id,
                    NULL, -1,
                    i.artist_id)),
      CAST (MULTISET (SELECT keyword
                        FROM keywords k
                       WHERE k.image_id = i.image_id)
            AS Keyword_tab_t)
     FROM images i;

The DECODE returns -1 if the artist_id is unknown; we are making the assumption that the artist_id will never, in fact, equal -1.

Seems like a lot of work just to use dot notation, doesn't it? There are other good reasons to use REFs, such as complex object retrieval (COR), but this feature is at present available only to OCI applications. (Note that OCI is beyond the scope of this book.) COR allows an application to efficiently retrieve an object and pre-fetch all objects REFerenced by that object to a predefined "depth level." It isn't yet clear whether Oracle will support some form of COR in a future version of PL/SQL.

20.4.2.4 DEREF: Interpreting a virtual REF

What should we do about updates to an object view that contains a REF if, for example, we are writing an INSTEAD OF trigger? How do we tell the view to deal with the constructed REF? Why, we use the DEREF function, of course. (Just what you were thinking, I bet.)

Here is the new version of our earlier INSTEAD OF trigger, modified to deal with the REF:

CREATE OR REPLACE TRIGGER images_v_insert
INSTEAD OF INSERT ON images_v
FOR EACH ROW
DECLARE
   /* The unrefcur cursor serves to "dereference" the artist_ref,
   || so that we can retrieve the object to which it refers.
   */
   CURSOR unrefcur IS
      SELECT DEREF(:new.artist_ref)
        FROM DUAL;

   /* We'll need a place to hold the object: */
   artist Artist_t;
BEGIN
   /* Go get the object... */
   OPEN unrefcur;
   FETCH unrefcur INTO artist;
   CLOSE unrefcur;

   /* This will fail with DUP_VAL_ON_INDEX if the images table
   || already contains a record with the new image_id.
   */
   INSERT INTO images
      VALUES (:NEW.image_id, :NEW.file_name, :NEW.file_type,
              :NEW.bytes, artist.id);

   IF :NEW.keywords IS NOT NULL THEN
      DECLARE
         /* Note: apparent bug prevents use of :NEW.keywords.LAST.
         || The workaround is to store :NEW.keywords as a local
         || variable (in this case keywords_holder.)
         */
         keywords_holder Keyword_tab_t := :NEW.keywords;
      BEGIN
         FOR the_keyword IN 1..keywords_holder.LAST
         LOOP
            INSERT INTO keywords
            VALUES (:NEW.image_id, keywords_holder(the_keyword));
         END LOOP;
       END;
   END IF;
END;

With this trigger defined, we can look briefly at an example of doing an INSERT:

INSERT INTO images_v                             -- invalid (bug?)
SELECT Image_t(451, 'library.jpg', 'JPG', 3092,
               REF(a),
               keyword_tab_t('BOOKS', 'LIBRARY', 'PUBLIC BUILDINGS'))
  FROM artists_v a
 WHERE id = 100;

which ought to work but fails with an ORA-00932 error, "inconsistent datatypes." (This is a suspected bug in Oracle 8.0.3.) As a workaround, you can either use the "relational equivalent" insert or issue an equivalent PL/SQL statement that does work.

The relational equivalent insert uses column values rather than the default constructor:

INSERT INTO images_v
SELECT 451, 'library.jpg', 'JPG', 3092,
       REF(a),
       keyword_tab_t('BOOKS', 'LIBRARY', 'PUBLIC BUILDINGS')
  FROM artists_v a
 WHERE id = 100;

The following PL/SQL snippet also works, with (or without) the constructor:

DECLARE
   image Image_t;
BEGIN
  SELECT Image_t(451, 'library.jpg', 'JPG', 3092,
                REF(a),
                keyword_tab_t('BOOKS', 'LIBRARY', 'PUBLIC BUILDINGS'))
    INTO image
    FROM artists_v a
   WHERE id = 100;
  INSERT INTO images_v VALUES (image);
END;

For more details about DEREF, refer to Chapter 18.