start page | rating of books | rating of authors | reviews | copyrights

Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 19.9 Which Collection Type Should I Use? Chapter 20 Next: 20.2 INSTEAD OF Triggers

20. Object Views

Example: Using Object Views
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:

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

Figure 20.1

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.


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:


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:

Previous: 19.9 Which Collection Type Should I Use? Oracle PL/SQL Programming, 2nd Edition Next: 20.2 INSTEAD OF Triggers
19.9 Which Collection Type Should I Use? Book Index 20.2 INSTEAD OF Triggers

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference