Object View with a Collection Attribute

Before creating an underlying type for the first view, we need a collection type to hold the keywords. Use of a nested table makes sense here, because keyword ordering is unimportant and because there is no logical maximum number of keywords.^[12]

^[12] 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.

CREATE TYPE keyword_tab_t AS TABLE OF VARCHAR2(45);

At this point, it's a simple matter to define the image object type:

CREATE TYPE image_t AS OBJECT ( image_id INTEGER, image_file BFILE, file_type VARCHAR2(12), bytes INTEGER, keywords keyword_tab_t);

Assuming that the image files and the database server are on the same machine, I can use an Oracle BFILE datatype rather than the filename. I'll need to create a "directory," that is, an alias by which Oracle will know the directory that contains the images. In this case, I use the root directory (on the target Unix system, this is represented by a single forward slash), because I happen to know that the file_name column includes full pathnames.

CREATE DIRECTORY rootdir AS '/';

So far, I have not defined a connection between the relational tables and the object type. They are independent organisms. It is in building the object view that we overlay the object definition onto the tables, as the next statement illustrates:

CREATE VIEW images_v OF image_t WITH OBJECT IDENTIFIER (image_id)AS SELECT i.image_id, BFILENAME('ROOTDIR', 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;

There are two 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 IDENTIFIER (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 REF-based navigation to objects in the view.

In addition, the select list of an object view must correspond in number and datatype with the attributes in the associated object type.

OK, now that we've created an object view, what can we do with it? Most significantly, we can retrieve data from it just as if it were an object table.

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

SQL> SELECT image_id, keywords FROM images_v;

yields:

IMAGE_ID KEYWORDS---------- ------------------------------------------------------- 100003 KEYWORD_TAB_T( ) 100001 KEYWORD_TAB_T('HAPPY FACE', 'SIXTIES') 100002 KEYWORD_TAB_T('JERRY RUBIN', 'PEACE SYMBOL', 'SIXTIES')

In the interest of deepening the object appearance, I could also add methods to the type definition. Here, for example, is aprint( ) method:

ALTER TYPE image_t ADD MEMBER FUNCTION print RETURN VARCHAR2 CASCADE; CREATE OR REPLACE TYPE BODY image_tAS MEMBER FUNCTION print RETURN VARCHAR2 IS filename images.file_name%TYPE; dirname VARCHAR2(30); keyword_list VARCHAR2(32767); BEGIN DBMS_LOB.FILEGETNAME(SELF.image_file, dirname, filename); IF SELF.keywords IS NOT NULL THEN FOR key_elt IN 1..SELF.keywords.COUNT LOOP keyword_list := keyword_list || ', ' || SELF.keywords(key_elt); END LOOP; END IF; RETURN 'Id=' || SELF.image_id || '; File=' || filename || '; keywords=' || SUBSTR(keyword_list, 3); END;END;

This example illustrates a way to "flatten" the keyword list by iterating over the virtual collection of keywords.

Is It Null, or Is It Not? A null collection is not the same thing as an initialized collection with zero elements. Image 100003 has no keywords, but the object view is mistakenly returning an empty but initialized collection. To get a true NULL instead, I can use a DECODE to test the number of keywords: CREATE OR REPLACE VIEW images_v OF image_t WITH OBJECT IDENTIFIER (image_id)AS SELECT i.image_id, BFILENAME('ROOTDIR', i.file_name), i.file_type, i.bytes, DECODE((SELECT COUNT(*) FROM keywords k2 WHERE k2.image_id = i.image_id), 0, NULL, CAST (MULTISET (SELECT keyword FROM keywords k WHERE k.image_id = i.image_id) AS keyword_tab_t)) FROM images i; In other words, if there are no keywords, return NULL; otherwise, return the CAST/MULTISET expression. From this view, "SELECT ... WHERE image_id=100003" properly yields the following: IMAGE_ID KEYWORDS---------- ------------------------------------------------------- 100003 But you might conclude that this amount of conceptual purity is not worth the extra I/O (or having to look at the convoluted SELECT statement).

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

Use virtual REFs

These are pointers to virtual objects. They are discussed in detail in the later section Section 21.3.6.

Write INSTEAD OF triggers

These will allow direct manipulation of the view's contents. You can read more about this topic in the later section, Section 21.3.6.