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
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:
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.
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 ]
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:
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;
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:
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.