20.8 Postscript: Using the BFILE Datatype

A better abstraction for the Planetary Pages datatype Image_t would include a BFILE datatype rather than a VARCHAR2 file_name attribute. A BFILE is a file in the external operating system that Oracle can retrieve, but not store, using built-in packages. (We discuss the BFILE datatype in Chapter 4, Variables and Program Data .) We chose not to include this alternate representation in the core part of the chapter to avoid detracting from the basic themes of object views. However, let's look at it now.

The DDL follows for this alternate representation, with the changes highlighted in bold. First, here is the new version of the object type itself:

CREATE TYPE Image_t AS OBJECT (     image_id INTEGER,    
 image_file BFILE,
     file_type VARCHAR2(12),     bytes INTEGER,     keywords Keyword_tab_t,     MEMBER FUNCTION set_attrs (new_image_file IN BFILE,        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) );  CREATE TYPE BODY Image_t AS    MEMBER FUNCTION set_attrs (
new_image_file IN BFILE
,        new_file_type IN VARCHAR2, new_bytes IN INTEGER)        RETURN Image_t    IS       image_holder Image_t := SELF;    BEGIN       
image_holder.image_file := new_image_file;
       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;

Now we need to create an "alias" known to Oracle for the directory that will contain the images. In this case, the alias is "webpix."

CREATE DIRECTORY webpix AS    '/files/web/pix';

The new version of the view uses the built-in BFILENAME to convert the filename in the underlying table into an Oracle BFILE datatype:

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

The INSTEAD OF trigger will need to make the inverse conversion -- that is, accept a BFILE and extract a filename. This is easy to do using the built-in procedure DBMS_LOB.FILEGETNAME:

CREATE OR REPLACE TRIGGER images_v_insert INSTEAD OF INSERT ON images_v FOR EACH ROW 
DECLARE
    
l_file_name images.file_name%TYPE;
    
l_directory VARCHAR2(30);
 BEGIN    
/* Determine the directory name */
    
DBMS_LOB.FILEGETNAME (file_loc => :NEW.image_file,
       
dir_alias => l_directory,
       
filename => l_file_name);
     /* This will fail with DUP_VAL_ON_INDEX if the images table    || already contains a record with the new image_id.    */    INSERT INTO images       VALUES (:NEW.image_id, 
l_file_name
, :NEW.file_type,               :NEW.bytes);     IF :NEW.keywords IS NOT NULL THEN       DECLARE          /* Note: apparent bug prevents use of :NEW.keywords.LAST.          || The workaround is to store :NEW.keywords as a local          || variable (in this case keywords_holder.)          */          keywords_holder Keyword_tab_t := :NEW.keywords;       BEGIN          FOR the_keyword IN 1..keywords_holder.LAST          LOOP             INSERT INTO keywords             VALUES (:NEW.image_id, keywords_holder(the_keyword));          END LOOP;        END;    END IF; END;

And finally, we can demonstrate how an insert would be made using the object view:

INSERT INTO images_v VALUES    (Image_t (1002, 
BFILENAME('WEBPIX','abc.gif')
, 'GIF', 1024,              Keyword_tab_t('ALPHABET', 'LETTERS')));

Appendix C, Built-In Packages , contains information about these built-in packages.