How to return a fully qualified BFILE file name from a function

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.