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