The Oracle BFILENAME function is a useful file manipulation function. Learn what it is and how to use it in this article.
Purpose of the Oracle BFILENAME Function
The BFILENAME function returns an object called a “BFILE locator” from a specified directory and filename.
This function is often used in PL/SQL to access the data within the BFILE.
Syntax
The syntax of the Oracle BFILENAME function is:
BFILENAME ( 'directory', 'filename' )
Parameters
The parameters of the BFILENAME function are:
- directory (mandatory): This is the directory database object that is an alias for the actual directory on the database server.
- filename (mandatory): This is the filename string, which is just the name of the file to refer to.
Using the BFILENAME Function
One thing to remember with this function is that the directory parameter needs to be a directory object on the database. It can’t just be a string that represents a directory on the server.
I’ve made this mistake many times when I was first working with directories, so it’s something to keep in mind.
How Can You Create a Directory in Oracle?
I mentioned in the previous section that the directory parameter needs to be a directory object, not a string that represents a directory.
So how do you create a directory object in Oracle?
It follows the same format as creating other objects
CREATE DIRECTORY dirname AS 'path';So, for example:
CREATE DIRECTORY image_folder AS '/content/images';This creates a new object called image_folder, which you can then use in functions such as BFILENAME.
BFILENAME Function in Oracle 11g
The BFILENAME function in Oracle 11g works the same way as it does in Oracle 12c.
See the Examples section for more information on how it is used.
Examples of the BFILENAME Function
Here are some examples of the BFILENAME function. I find that examples are the best way for me to learn about code, even with the explanation above.
Example 1
This example creates a directory object, and then inserts a BFILENAME into a table.
CREATE DIRECTORY image_folder AS '/content/images';
INSERT INTO website_images (image_id, image_name)
VALUES (1, BFILENAME(image_folder, 'large_footer.png'));This website_images table has an image_name field which can hold the BFILENAME object.
Example 2
This is an example of using BFILENAME in a SELECT statement.
SELECT BFILENAME(image_folder, 'small_footer.png')
FROM dual;
Similar Functions
There aren’t really any functions similar to the BFILENAME function.
You can find a full list of Oracle SQL functions here.