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.