MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

BFILE full name function

with 5 comments

I’m working on a framework for converting the BFILE from a read-only type to a pseudo read-write type. The framework will use row-level triggers calling Java libraries. The row-level trigger passes the BFILE locator (or officially descriptor) but it only contains the virtual directory name and file name. You need to read the directory path from the database catalog.

The code is from Chapter 8 of Oracle Database 11g PL/SQL Programming. Excuse the plug but I don’t want to get in trouble with the publisher for reusing my own code.

The first function gets the physical directory related to a virtual database directory. It’s the GET_DIRECTORY_PATH function. The second function extends the idea of the GET_BFILENAME function in the prior blog. It’s the GET_CANONICAL_BFILENAME function.

You should build both functions in the SYSTEM schema. You’ll need to grant SELECT privileges on the DBA_DIRECTORIES view as SYS to SYSTEM. This is required.because the default SYSTEM privileges are provided through a role. While the role lets you query the DBA_DIRECTORY view, it disallows your implementing a cursor against the view inside a PL/SQL block. The GET_CANONICAL_BFILENAME function returns the fully qualified file name. You can then call it from other modules or internal Java libraries.

The steps and code follow below. The target user schema is PLSQL.

1. Connect as the SYS user and grant SELECT privilege on the catalog view:

SQL> GRANT SELECT ON dba_directories TO plsql;

2. Connect as the SYSTEM user and create the GET_DIRECTORY_PATH function:

CREATE OR REPLACE FUNCTION get_directory_path
( virtual_directory IN VARCHAR2 )
RETURN VARCHAR2 IS
 
  -- Define return variable.
  directory_path VARCHAR2(256) := '';
 
  -- Define dynamic cursor.
  CURSOR get_directory (virtual_directory VARCHAR2) IS
    SELECT   directory_path
    FROM     sys.dba_directories
    WHERE    directory_name = virtual_directory;
 
  -- Define a local exception for name violation.
  directory_name EXCEPTION;
  PRAGMA EXCEPTION_INIT(directory_name,-22284);
BEGIN
  OPEN  get_directory (virtual_directory);
  FETCH get_directory
  INTO  directory_path;
  CLOSE get_directory;
 
  -- Return file name.
  RETURN directory_path;
EXCEPTION
  WHEN directory_name THEN
    RETURN NULL;
END get_directory_path;
/

3. As the SYSTEM user, create the GET_CANONICAL_BFILENAME function. A note that the quotes are converted to non-readable strings by WordPress.

CREATE OR REPLACE FUNCTION get_canonical_bfilename
( table_name        IN     VARCHAR2
, bfile_column_name IN     VARCHAR2
, primary_key       IN     VARCHAR2
, primary_key_value IN     VARCHAR2
, operating_system  IN     VARCHAR2 := 'WINDOWS')
RETURN VARCHAR2 IS
 
  -- Declare default delimiter.
  delimiter         VARCHAR2(1) := '';
 
  -- Define statement variable.
  stmt              VARCHAR2(200);
 
  -- Define a locator.
  locator           BFILE;
 
  -- Define alias and file name.
  dir_alias         VARCHAR2(255);
  directory         VARCHAR2(255);
  file_name         VARCHAR2(255);
 
  -- Define a local exception for size violation.
  directory_num EXCEPTION;
  PRAGMA EXCEPTION_INIT(directory_num,-22285);
BEGIN
  -- Assign dynamic string to statement.
  stmt := 'BEGIN '
        || ' SELECT '||bfile_column_name||' '
        || ' INTO :column_value '
        || ' FROM  '||table_name||' '
        || ' WHERE '||primary_key||'='||''''||primary_key_value||''''||';'
        || 'END;';
 
  -- Run dynamic statement.
  EXECUTE IMMEDIATE stmt USING OUT locator;
 
  -- Check for available locator.
  IF locator IS NOT NULL THEN
    DBMS_LOB.filegetname(locator,dir_alias,file_name);
  END IF;
 
  -- Check operating system and swap delimiter when necessary.
  IF operating_system <> 'WINDOWS' THEN
    delimiter := '/';
  END IF;
 
  -- Create a canonical file name.
  IF locator IS NOT NULL THEN
    file_name := get_directory_path(dir_alias) || delimiter || file_name;
  END IF;
 
  -- Return file name.
  RETURN file_name;
EXCEPTION
  WHEN directory_num THEN
    RETURN NULL;
END get_canonical_bfilename;
/

4. Create a virtual directory as the SYSTEM user:

SQL> CREATE DIRECTORY images AS 'C:\Data\Images';

5. Create a table with a BFILE column, and insert a row with a LOB locator:

SQL> CREATE TABLE image (image_id NUMBER, image_file VARCHAR2(255));
SQL> INSERT INTO image VALUES (1,BFILENAME('IMAGES','Raiders4.png');

6. Test the function:

SQL> VARIABLE file_name VARCHAR2(255)
SQL> CALL get_canonical_bfilename('image','image_file','image_id','1') INTO :file_name;
SQL> PRINT file_name

You should see the following in Windows and a mount point and forward slashes in Linux or Unix:

C:\DATA\Images\Raiders4.png

After writing these samples, I realized there was a better way. You can use this function in a SELECT statement to get all fully qualified file names.

CREATE OR REPLACE FUNCTION get_canonical_local_bfilename
( locator           IN     BFILE
, operating_system  IN     VARCHAR2 := 'WINDOWS')
RETURN VARCHAR2 IS
 
  -- Declare default delimiter.
  delimiter         VARCHAR2(1) := '';
 
  -- Define statement variable.
  stmt              VARCHAR2(200);
 
  -- Define alias and file name.
  dir_alias         VARCHAR2(255);
  directory         VARCHAR2(255);
  file_name         VARCHAR2(255);
 
  -- Define a local exception for size violation.
  directory_num EXCEPTION;
  PRAGMA EXCEPTION_INIT(directory_num,-22285);
BEGIN
  -- Check for available locator.
  IF locator IS NOT NULL THEN
    DBMS_LOB.filegetname(locator,dir_alias,file_name);
  END IF;
 
  -- Check operating system and swap delimiter when necessary.
  IF operating_system <> 'WINDOWS' THEN
    delimiter := '/';
  END IF;
 
  -- Create a canonical file name.
  file_name := get_directory_path(dir_alias) || delimiter || file_name;
 
  -- Return file name.
  RETURN file_name;
EXCEPTION
  WHEN directory_num THEN
    RETURN NULL;
END get_canonical_local_bfilename;
/

This new function lets you select the fully qualified file name from a BFILE column, like:

SELECT   get_canonical_local_bfilename(item_photo) AS file_name
FROM     item
WHERE    item_id = 1021;

In my test environment, it returns:

FILE_NAME
------------------------------------------
C:\JavaDev\BFileFramework\HarryPotter1.png

I’ll refer to this when I start posting the internal Java libraries to synchronize BFILE column values with the external file system.

Written by maclochlainn

July 23rd, 2008 at 6:24 am

Posted in Uncategorized

5 Responses to 'BFILE full name function'

Subscribe to comments with RSS or TrackBack to 'BFILE full name function'.

  1. I found your site on technorati and read a few of your other posts. Keep up the good work. I just added your RSS feed to my Google News Reader. Looking forward to reading more from you down the road!

    Alex

    12 Aug 08 at 2:07 pm

  2. I can’t seem to get your RSS feed to work with my program. Do you mind telling me the URL so I can read your posts on my own program?

    George Hahn

    4 Jun 09 at 7:03 pm

  3. I’m not sure that this is a valid question because of the email and website but here’s the information. By the way, I’ve removed the return email and web site from your comment just in case it’s spam.

    https://blog.mclaughlinsoftware.comfeed/

    maclochlainn

    4 Jun 09 at 7:10 pm

  4. I created a table with BFile field. I am having a problem with inserting & fetching with BFile Field. Help me by giving oracle 10g statements as a example?

    vishweshwar

    12 Sep 09 at 1:15 am

  5. I’d be happy to provide you with an example but what language are you working in? Is it PHP, Java, or something else?

    maclochlainn

    12 Sep 09 at 1:53 pm

Leave a Reply