The BFILENAME Function

When working with external files or inserting locators for BFILEs into a table, you will need to call the BFILENAME function, which creates the pointer (referred to as the locator in Oracle) to the external file.

The Syntax for the BFILENAME Function

FUNCTION BFILENAME(Directory_Object IN VARCHAR2,
                         Filename IN VARCHAR2);
RETURN BFILE_Locator;

When using this function, you pass the Directory_Object, which you have previously created. Again, this object stores the path of the file. The second parameter you will pass, Filename, is the actual name of the file. The function will then return a pointer (locator) to the file, so Oracle knows how to access the file. This value can be used in PL/SQL blocks, or it can be inserted into a table with a column of type BFILE.

The COMPARE Function

If you need to compare all or part of a LOB, you can use the COMPARE function. One useful purpose of this function is to check to see if you have two external files that are exactly identical. You could write a maintenance program to check for duplicate files and then remove the duplicate files because these large files can easily waste a lot of hard drive space.

The Syntax for the COMPARE Function

FUNCTION COMPARE(
                       Lob1 IN BFILE,
                       Lob2 IN BFILE,
                       Number_Bytes_to_Compare IN INTEGER,
                       Origin_Lob1    IN INTEGER := 1,
                       Origin_Lob2    IN INTEGER := 1)
RETURN Compare_Result_Integer;

Lob1 is the first LOB you are comparing to Lob2, the second LOB. Number_Bytes_to_Compare is the total number of bytes you want to compare from the first LOB to the second LOB. Origin_Lob1 is the starting location in the file for where you want to compare. The value of 1 starts from the beginning of the LOB, or you could enter 100 to start comparing from the 100th byte. Origin_Lob2 is the starting location of the second LOB you want to compare. The value returned will be 0 if the data is identical, non-zero if the data is not identical, or NULL if any of the parameters are invalid, such as an incorrect starting origin, comparing bytes past the end of the LOB, or any other invalid parameter.

The FILECLOSE Procedure

When you have finished reading a BFILE, you should always close the file not only to free up resources, but also so that you don't exceed the maximum number of allowable files that can be open. The FILECLOSE procedure closes a single file that is open.

The Syntax for the FILECLOSE Function

PROCEDURE FILECLOSE(BFILE_Locator);

The parameter BFILE_Locator is the BFILE locator assigned to the file from the BFILENAME function.

The FILECLOSEALL Procedure

If you are done processing all BFILEs and you want to end your session, you can use the procedure FILECLOSEALL to close every open BFILE. The format for the FILECLOSEALL procedure is as follows:

PROCEDURE FILECLOSEALL;

The FILEEXISTS Function

As stated previously, Oracle does not know if a file has been moved, deleted, or changed in size. Therefore, it is always good coding practice to see if the file exists before performing any operations on the file. Oracle provides the function FILEEXISTS to see if the file is physically at the location specified by the directory object and the filename.

The Syntax for the Function FILEEXISTS

FUNCTION FILEEXISTS (BFILE_Locator)
     RETURN Status_Integer;

The parameter BFILE_Locator is the BFILE locator assigned to the file from the BFILENAME function. The function returns an INTEGER with a value of 1 if the file exists at that specific location, a value of 0 if the file does not exist, or a value of NULL if there is an operating system error, if you lack the privileges to access that file or path, or if the value of the locator is NULL.

The FILEGETNAME Procedure

Although you will probably never use FILEGETNAME in an anonymous block of PL/SQL because you will define the directory object and the filename, this is a useful procedure for BFILE locators stored in a table. The FILEGETNAME procedure returns the directory object and the filename associated with the locator. The directory object has a maximum size of 30 characters, and the filename has a maximum size of 2000 characters.

The Syntax for the FILEGETNAME Procedure

PROCEDURE FILEGETNAME(BFILE_Locator,
                            Directory_Object OUT VARCHAR2,
                            Filename OUT VARCHAR2);

The parameter BFILE_Locator is the BFILE locator assigned to the file from the BFILENAME function. Directory_Object is the directory object associated with the path created with the CREATE DIRECTORY command. The Filename parameter is the name of the file associated with the BFILE locator.

The FILEISOPEN Function

Before opening a BFILE, you should check to make sure that the file is not already opened by using the FILEISOPEN function.