Maclochlainn's Weblog

A shortfall of the DBMS_LOB package is that you can’t call a function to get a file name. This shows you how to write a wrapper function to the DBMS_LOB.FILEGETNAME. The following are the steps to build a test case for the function:

1. Create a virtual directory:

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

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

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


3. Create the GET_BFILENAME function:

CREATE OR REPLACE FUNCTION get_bfilename
( table_name VARCHAR2
, column_name VARCHAR2
, primary_key_name VARCHAR2
, primary_key_value VARCHAR2)
RETURN VARCHAR2 IS
-- Define a locator.
locator BFILE;
-- Define alias and file name.
dir_alias VARCHAR2(255);
directory VARCHAR2(255);
file_name VARCHAR2(255);
-- Define local variable for Native Dynamic SQL.
stmt VARCHAR2(2000);
-- Define a local exception for size violation.
directory_num EXCEPTION;
PRAGMA EXCEPTION_INIT(directory_num,-22285);
BEGIN
-- Wrap the statement in an anonymous block to create and OUT mode variable.
stmt := 'BEGIN '
|| 'SELECT '||column_name||' '
|| 'INTO :locator '
|| 'FROM '||table_name||' '
|| 'WHERE '||primary_key_name||' = '||''''||primary_key_value||''';'
|| 'END;';
-- Return a scalar query result from a dynamic SQL 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;
-- Return file name.
RETURN file_name;
EXCEPTION
WHEN directory_num THEN
RETURN NULL;
END get_bfilename;
/

The function is limited to a single column primary key but you can extend it if you want.

4. Test the function:

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