Object Subview

In the case where I want to treat certain images differently from others, I might want to create a subtype. In my example, I'm going to create a subtype for those images that originate from suppliers. I'd like the subtype to include a REF to a supplier object, which is defined by:

CREATE TYPE supplier_t AS OBJECT (id INTEGER, name VARCHAR2(400));

and by a simple object view:

CREATE VIEW suppliers_v OF supplier_t WITH OBJECT IDENTIFIER (id)AS SELECT id, name FROM suppliers;

I will need to alter or re-create the base type to be NOT FINAL:

ALTER TYPE image_t NOT FINAL CASCADE;

so that I can create the subtype under it:

CREATE TYPE supplied_images_t UNDER image_t (supplier_ref REF supplier_t, supplier_rights_descriptor VARCHAR2(256));

After all this preparation, I make the subview of this subtype and declare it to be UNDER the images_v view using the following syntax:

CREATE VIEW supplied_images_v OF supplied_images_t UNDER images_v 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), MAKE_REF(suppliers_v, supplier_id), supplier_rights_descriptor FROM images i WHERE supplier_id IS NOT NULL;

Oracle won't let a subview query through the superview, so this view queries the base table, adding the WHERE clause to restrict the records retrieved. Also notice that subviews don't use the WITH OBJECT IDENTIFIER clause because they inherit the same OID as their superview.

I have introduced the MAKE_REF function in this query, which Oracle provides as a way to compute a REF to a virtual object. Here, the virtual object is the supplier, as conveyed through suppliers_v. The specification of MAKE_REF is:

FUNCTION MAKE_REF ( view , value_list ) RETURN ref ;

where:

 

view

Is the object view to which you want ref to point.

 

value_list

Is a comma-separated list of column values whose datatypes must match one for one with the OID attributes of view.

You should realize that MAKE_REF does not actually select through the view; it merely applies an internal Oracle algorithm to derive a REF. And, as with "real" REFs, virtual REFs may not point to actual objects.

Now I come to a surprising result. Although it seems that I have not changed the superview, images from suppliers now appear twice in the superview—that is, as duplicates:

SQL> SELECT COUNT(*), image_id FROM images_v GROUP BY image_id; COUNT(*) IMAGE_ID---------- ---------- 2 100001 2 100002 1 100003

Oracle is returning a logical UNION ALL of the query in the superview and that in the subview. This does sort of make sense; an image from a supplier is still an image. To eliminate the duplicates, add a WHERE clause on the parent that excludes records returned in the subview:

CREATE OR REPLACE VIEW images_v AS... WHERE supplier_id IS NULL;