MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

BFILE file name function

without comments

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

Written by maclochlainn

July 22nd, 2008 at 4:14 am

Posted in Uncategorized

Leave a Reply