Oracle BFILENAME Function: A Simple Guide

The BFILENAME function in Oracle SQL is a special function used to work with external files. Its job is to create a BFILE locator, which is a pointer to a physical file (like an image, PDF, or text file) that lives on the database server's file system, outside of the database itself.

This function doesn't read the file; it just tells Oracle where to find it. You then use other functions (like TO_LOB or DBMS_LOB package) on this BFILE pointer to read or process the file's data.

What is the BFILENAME Function in Oracle?

The BFILENAME('directory_name', 'file_name') function is the first step in accessing external binary files. It returns a BFILE data type.

Before you can use this function, a Database Administrator (DBA) must first create a DIRECTORY object in Oracle. A DIRECTORY is just a safe, named alias that points to a real path on the server's hard drive (e.g., 'media_dir' could point to '/u01/app/oracle/media').

This is a security feature that prevents SQL from accessing random files all over the server.

BFILENAME Function Syntax

The syntax for BFILENAME is:

BFILENAME('directory_name', 'file_name')

Let's break that down:

• 'directory_name': This is the name of the Oracle DIRECTORY object (as a string). This argument is case-sensitive and must exactly match the name in the database dictionary.
• 'file_name': This is the name of the file you want to point to (e.g., 'logo.jpg'). This is also case-sensitive, just like a file name on a Linux or Unix server.

Oracle BFILENAME Function Examples

These examples are conceptual and show how the function is used. They require a DBA to have already created the DIRECTORY object.

Example 1: Creating a DIRECTORY and Inserting a BFILE Pointer using BFILENAME

This is the most common use case.

Step 1: A DBA creates the DIRECTORY object. (This is a one-time setup)

-- This command is run by a DBA
CREATE DIRECTORY media_dir AS '/demo/schema/product_media';

Step 2: You insert a new row, pointing to a file in that directory. (This is the developer's query)

-- This query inserts a pointer to the file 'logo.gif'
-- located in the 'media_dir' path. It does NOT insert the file itself.
INSERT INTO print_media (product_id, ad_id, ad_graphic)
VALUES (
  3000, 
  31001, 
  BFILENAME('MEDIA_DIR', 'logo.gif')
);

Example 2: Accessing a File's Data using BFILENAME

You often use BFILENAME as an input to another function. Here, we use it with TO_BLOB to read the external file's data and insert it as an internal BLOB (Binary Large Object).

Query:

-- This query finds the file 'avatar.jpg' in the 'USER_PHOTOS' directory,
-- reads its binary data, converts it to a BLOB, and inserts it.
INSERT INTO user_profiles (user_id, photo_blob)
VALUES (
  123,
  TO_BLOB(BFILENAME('USER_PHOTOS', 'avatar.jpg'))
);