#

| Chapter 20 Object Views |

## 20.4 Differences Between Object Views and Object Tables

In addition to the obvious difference between a view and a table, more subtle differences exist between an object view and an object table. Areas of difference include the following:

- Uniqueness of object identifiers

- Use of REFs

- Storage of REFs

- REFs to nonunique OIDs

Lets look at each difference in turn.

### 20.4.1 OID Uniqueness

An object table will always have a unique, system-defined object identifier, whether or not the object type includes attribute(s) which can serve as a unique identifier. While this is seldom, if ever, good practice, it is technically possible to create an object table where two or more object instances (rows) contain duplicate values in every column; the instances will still be unique in their object identifier. And, as discussed in the previous chapter, OIDs of table objects are globally unique, even across databases.

By contrast, object views can give rise to two types of OID duplication:

- Duplication of OIDs within a single object view

- Duplication of OIDs across multiple views (or across an object view and an object table)

#### 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 Using REFs with Object Views

As we saw in Chapter 18, Oracle allows us to define a "reference" or REF datatype to designate relationships between two object tables. We also saw how REF types provide convenient navigation among families of persistent objects through dot notation rather than explicit relational joins.

In the same way that you can define a REF to link two object tables, you can create a "virtual REF" to link two object views. To see how this works, and to examine a critical difference between virtual REFs and "real" REFs, let's return to an example at our favorite Web design firm, Planetary Pages.

#### 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.

### 20.4.3 Storage of Virtual REFs

With object tables, REFs get physically stored in a table. That is, a column defined as a REF type can contain a binary value that Oracle can use as a "pointer" to an object.

However, when dealing with object views, Oracle does not yet allow you to store a REF pointing to a virtual object. In other words, even if you create a table with an appropriately typed REF column, you cannot actually save a value in this column. From one perspective, this is an irritant rather than a significant deficiency; as a workaround, create an object view of the table, and use MAKE_REF.

From another perspective, it's a bit unpleasant that we cannot intermingle object tables with object views; nor can we perform a simple transformation from an object view into an object table. I would like to be able to create an object table:

>     CREATE TABLE images2 OF image_t
>        NESTED TABLE keywords STORE AS keyword_tab;

and then populate it from the view:

>     INSERT INTO images2                  -- invalid
>      SELECT VALUE(i) FROM images_v i;

But alas, Oracle tells me I cannot: this request results in an ORA-22979 error, "cannot INSERT a REF from an object view into a table."

### 20.4.4 REFs to Nonunique OIDs

What do you suppose will happen if you create a REF to an object in an object view, but it has multiple object instances for the OID in question? Granted, this is a pretty weird case; you shouldn't be creating object views with ambiguous OIDs.

I won't keep you in suspense for this one. In my testing, DEREFing this type of virtual REF returned a null OID. That seems like an unusual result, so I don't think I would count on it in an application.

20.3 Syntax for Object Views |  | 20.5 Not All Views with Objects Are Object Views

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.