Creating a BFILE locator

BFILE locators are trivial to create; you simply invoke the BFILENAME function and pass it a directory alias and a filename. Unlike locators for other LOB types, you don't need to store a BFILE locator in the database prior to using it. In the following example we create a BFILE locator for the HTML file containing the Tannery Falls web page. We then store that locator into the waterfalls table.

DECLARE web_page BFILE;BEGIN --Delete row for Tannery Falls so this example can --be executed multiple times DELETE FROM waterfalls WHERE falls_name='Tannery Falls'; --Invoke BFILENAME to create a BFILE locator web_page:= BFILENAME('BFILE_DATA','Tannery Falls.htm'); --Save our new locator in the waterfalls table INSERT INTO waterfalls (falls_name, falls_web_page) VALUES ('Tannery Falls',web_page);END;

A BFILE locator is simply a combination of directory alias and filename. The actual file and directory don't even need to exist. That is, Oracle allows you to create directory aliases for directories that do not yet exist, and BFILENAME allows you to create BFILE locators for files that do not yet exist. There are times when it's convenient to do these things.

The directory name you specify in calls to BFILENAME is case-sensitive, and its case must match that shown by the ALL_DIRECTORIES data dictionary view. We first used lowercase bfile_data in our example, only to be greatly frustrated by errors when we tried to access our external BFILE data (as in the next section). In most cases, you'll want to use all-uppercase for the directory name in a call to BFILENAME.