#

| Chapter 20 |

# 20. Object Views

Contents:
Example: Using Object Views
INSTEAD OF Triggers
Syntax for Object Views
Differences Between Object Views and Object Tables
Not All Views with Objects Are Object Views
Schema Evolution
Object Views Housekeeping
Postscript: Using the BFILE Datatype

Although Oracle's object extensions offer rich possibilities for the design of new systems, few Oracle shops with large relational databases in place will want to, or be able to, completely reengineer those systems to use objects. To allow established applications to take advantage of the new object features over time, Oracle8 provides object views. With object views, you can achieve the following benefits:

- Efficiency of object access. In PL/SQL, and particularly in Oracle Call Interface (OCI) applications, object programming constructs provide for the convenient retrieval, caching, and updating of object data. These programming facilities provide performance improvements, with the added benefit that application code can now be more succinct.

- Ability to navigate using REFs (reference pointers). By designating unique identifiers as the basis of an object identifier (OID), you can reap the benefits of object navigation. For example, you can retrieve attributes from related "virtual objects" using dot notation rather than via explicit joins.

- Easier schema evolution. In early versions of Oracle8, a pure object approach renders almost any kind of schema change at best ugly (see Chapter 18, Object Types). In contrast, object views offer more ways that you can change both the table structure and the object type definitions of an existing system.

- Consistency with new object-based applications. If you need to extend the design of a legacy database, the new components can be implemented in object tables; new object-oriented applications requiring access to existing data can employ a consistent programming model. Legacy applications can continue to work without modification.

Other new features of Oracle can improve the expressiveness of any type of view, not just object views. Two features which are not strictly limited to object views are collections and "INSTEAD OF" triggers. Consider two relational tables with a simple master-detail relationship. Using the Oracle objects option, you can portray the detail records as a single nonscalar attribute (collection) of the master, which could be a very useful abstraction. In addition, by using INSTEAD OF triggers, you can tell Oracle exactly how to perform inserts, updates, and deletes on any view. These two features are available to both object views and nonobject views. (I've described collections in Chapter 19, Nested Tables and VARRAYs, and I describe INSTEAD OF triggers later in this chapter.)

From an object-oriented perspective, there is one unavoidable "disadvantage" of object views, when compared to reengineering using an all-object approach: object views cannot retrofit any benefits of encapsulation. Insofar as old applications apply INSERT, UPDATE, and DELETE statements directly to table data, they will subvert the benefits of encapsulation normally provided by an object approach. As I discussed in Chapter 18, object-oriented designs typically prevent free-form access directly to object data.

Despite this intrusion of reality, if you do choose to layer object views on top of a legacy system, your future applications can employ object abstractions and enjoy many benefits of encapsulation and information hiding. And your legacy systems are no worse off than they were before! Figure 20.1 illustrates this use of object views.

#### Figure 20.1: Object views allow you to "bind" an object type definition to (existing) relational tables

This chapter discusses the nuances of creating and, to a lesser extent, using object views. The discussion of PL/SQL-specific aspects of object views is rather terse, for two reasons:

1. Object views are substantially similar to regular object types, which are covered in a Chapter 18.

2. As a topic, object views are closer to SQL than to PL/SQL.

However, PL/SQL developers who are interested in fully exploiting Oracle's object features must understand object views. This chapter pays close attention to the areas of difference between object tables and object views.

## 20.1 Example: Using Object Views

In our first example, let's look at how object views might be used at Planetary Pages, a fictitious firm that designs Web sites. Their existing relational application tracks JPEG, GIF, and other images that they use when designing client Web sites. These images are stored in files, but data about them are stored in relational tables. To help the graphic artists locate the right image, each image has one or more associated keywords, stored in a straightforward master-detail relationship.

Our legacy system has one table for image metadata:

>     CREATE TABLE images (
>        image_id INTEGER NOT NULL,
>        file_name VARCHAR2(512),
>        file_type VARCHAR2(12),
>        bytes INTEGER,
>        CONSTRAINT image_pk PRIMARY KEY (image_id));

and one table for the keywords associated with the images:

>     CREATE TABLE keywords (
>        image_id INTEGER NOT NULL,
>        keyword VARCHAR2(45) NOT NULL,
>        CONSTRAINT keywords_pk PRIMARY KEY (image_id, keyword),
>        CONSTRAINT keywords_for_image FOREIGN KEY (image_id)
>           REFERENCES images (image_id));

To create a more useful abstraction, Planetary Pages has decided to logically merge these two tables into a single object view. To do so, we must first create an object type with appropriate attributes. Since there are usually only a few keywords for a given image, this relationship lends itself to using an Oracle collection to hold the keywords.

Before we can create the top-level type, we must first define a collection to hold the keywords. We choose a nested table because keyword ordering is unimportant and because there is no logical maximum number of keywords.[1]

> [1] If ordering were important, or if there were a (small) logical maximum number of keywords per image, a VARRAY collection would be a better choice. See Chapter 19 for details.

>     CREATE TYPE Keyword_tab_t AS TABLE OF VARCHAR2(45);

From here, it's a simple matter to define the object type. To keep the example short, we'll define only a couple of methods. In the following object type specification, notice that the keywords attribute is defined on the Keyword_tab_t collection type:

>     CREATE TYPE Image_t AS OBJECT (
>         image_id INTEGER,
>         file_name VARCHAR2(512),
>         file_type VARCHAR2(12),
>         bytes INTEGER,
>         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)
>     );

Here is the body:

>     CREATE TYPE BODY Image_t
>     AS
>        MEMBER FUNCTION set_attrs (new_file_name IN VARCHAR2,
>            new_file_type IN VARCHAR2, new_bytes IN INTEGER)
>            RETURN Image_t
>        IS
>           image_holder Image_t := SELF;
>        BEGIN
>           image_holder.file_name := new_file_name;
>           image_holder.file_type := new_file_type;
>           image_holder.bytes := new_bytes;
>           RETURN image_holder;
>        END;
>        MEMBER FUNCTION set_keywords (new_keywords IN Keyword_tab_t)
>            RETURN Image_t
>        IS
>           image_holder Image_t := SELF;
>        BEGIN
>           image_holder.keywords := new_keywords;
>           RETURN image_holder;
>        END;
>     END;

I've presented the body only for completeness; from this point forward, I'll discuss object views without regard for the details of their underlying type bodies.

At this point, there is no connection between the relational tables and the object type. They are independent organisms. It is when we build the object view that we "overlay" the object definition onto the tables.

Finally, to create the object view, we use the following statement:

>     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,
>           CAST (MULTISET (SELECT keyword
>                             FROM keywords k
>                            WHERE k.image_id = i.image_id)
>             AS Keyword_tab_t)
>          FROM images i;

Interestingly, there are only a couple of components of this statement that are unique to object views:

OF Image_t

  This means that the view will return objects of type Image_t.

WITH OBJECT OID (image_id)

  To behave like a "real" object instance, data returned by the view will need some kind of object identifier. By designating the primary key as the basis of a virtual OID, we can enjoy the benefits of referencing objects of this type from other object views.

In addition, the select-list has an important requirement unique to object views. The select-list must define the same number of columns as there are attributes in the object type Image_t. The datatype of each retrieved column matches the datatype of its corresponding object attributes.

You can use the CAST clause shown in the example in any view, not just in object views (but it does require the presence of the Oracle objects option). This subquery performs an "on-the-fly" conversion of the detail records into a collection type. For more details about the CAST and MULTISET operators, refer to Section 19.5, "Collection Pseudo-Functions" in Chapter 19.

OK, now that we've created it, what can we do with it? Well, we can retrieve data from it just as if it were an object table. First, let's put some data into the underlying tables:

>     INSERT INTO images VALUES (100001, 'smiley_face.gif', 'GIF', 813);
>     INSERT INTO images VALUES (100002, 'peace_symbol.gif', 'GIF', 972);

>     INSERT INTO KEYWORDS VALUES (100001, 'SIXTIES');
>     INSERT INTO KEYWORDS VALUES (100001, 'HAPPY FACE');

>     INSERT INTO KEYWORDS VALUES (100002, 'SIXTIES');
>     INSERT INTO KEYWORDS VALUES (100002, 'PEACE SYMBOL');
>     INSERT INTO KEYWORDS VALUES (100002, 'JERRY RUBIN');

Now, from SQL*Plus, you can make a query like the following:

>     SELECT image_id, file_name, keywords
>       FROM images_v;

Which yields:

>     IMAGE_ID FILE_NAME        KEYWORDS
>     -------- ---------------- -------------------------------------------------------
>       100001 smiley_face.gif  KEYWORD_TAB_T('HAPPY FACE', 'SIXTIES')
>       100002 peace_symbol.gif KEYWORD_TAB_T('JERRY RUBIN', 'PEACE SYMBOL', 'SIXTIES')

Or, in a PL/SQL block, we can fetch and display a row of data very easily:

>     SET SERVEROUTPUT ON SIZE 100000
>     DECLARE
>        CURSOR icur IS
>           SELECT VALUE(v)
>             FROM images_v v
>            WHERE image_id = 100001;
>        image Image_t;
>     BEGIN
>        OPEN icur;
>        FETCH icur INTO image;
>        CLOSE icur;

>        /* Print it out, just to prove that we got it */
>        DBMS_OUTPUT.PUT_LINE('Image:    ' || image.image_id);
>        DBMS_OUTPUT.PUT_LINE('File:     ' || image.file_name);
>        DBMS_OUTPUT.PUT('Keywords: ');

>        /* image.keywords is a nested table, and we can just loop
>        || through it to print each keyword.
>        */
>        FOR key_elt IN 1..image.keywords.COUNT
>        LOOP
>           DBMS_OUTPUT.PUT(image.keywords(key_elt) || '   ');
>        END LOOP;
>        DBMS_OUTPUT.NEW_LINE;
>     END;

This results in:

>     Image:    100001
>     File:     smiley_face.gif
>     Keywords: HAPPY FACE   SIXTIES

See Chapter 19 for more examples of retrieving data from an object table.

Other things you can do with object views include the following:

- Define REFs that point to "virtual" objects (discussed in detail in Section 20.4, "Differences Between Object Views and Object Tables" later in this chapter).

- Encapsulate an object view (more or less) using object methods and/or PL/SQL packages (discussed in-depth in Chapter 18).

- Write INSTEAD OF triggers that will allow direct manipulation of the view's contents (discussed in the next section).

19.9 Which Collection Type Should I Use? |  | 20.2 INSTEAD OF Triggers

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