The Syntax for the FILEISOPEN Function

FUNCTION FILEISOPEN (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 is open or any other integer value if the file is closed. An exception is raised if the file doesn't exist, if you have insufficient privileges, or the directory does not exist.

The FILEOPEN Procedure

Before you can access an external file, you need to first open the file with the FILEOPEN procedure.

The Syntax for the FILEOPEN Procedure

PROCEDURE FILEOPEN(BFILE_Locator,
                         DBMS_LOB.FILE_READONLY);

The parameter BFILE_Locator is the BFILE locator assigned to the file from the BFILENAME function. The second parameter, DBMS_LOB.FILE_READONLY, is currently the only mode to open files.

The GETLENGTH Function

The GETLENGTH function returns the actual length of the objects in bytes.

The Syntax for the GETLENGTH Function

FUNCTION GETLENGTH (BFILE_Locator)
     RETURN Length_Integer;

The parameter BFILE_Locator is the BFILE locator assigned to the file from the BFILENAME function. The function returns an INTEGER of the length of the file, or NULL if the locator is NULL, if the file is not open, if there is an operating system error, or if you do not have the appropriate privileges to access the file.

The INSTR Function

The INSTR function allows you to match a pattern against the nth occurrence in the LOB starting from the offset specified.

The Syntax for the INSTR Function

FUNCTION INSTR(BFILE_Locator,
                     Pattern IN RAW,
                       Starting_Location IN INTEGER := 1,
                     Nth_Occurrence IN INTEGER := 1)
RETURN Status_Integer;

The parameter BFILE_Locator is the BFILE locator assigned to the file from the BFILENAME function. The Pattern of type RAW is the pattern you want to match. Starting_Location is

the position in the file where you want to start your search for a match. Nth_Occurrence is the nth time a match has been made. The function returns a value of 0 if the pattern is not found, it returns the offset from the start of the file where the match was found, or it returns a value of NULL if any of the parameters are NULL or invalid.

The READ Procedure

The READ procedure allows you to read part or all of a file into a buffer.

The Syntax for the READ Procedure

PROCEDURE READ(BFILE_Locator,
                     Read_Amount IN BINARY_INTEGER,
                        Starting_Location IN INTEGER,
                     Buffer OUT RAW);

The parameter BFILE_Locator is the BFILE locator assigned to the file from the BFILENAME function. The second parameter, Read_Amount, is the number of bytes you will read from the file into the buffer. Starting_Location is the location you want to start reading from the file. For instance, you could read up to 32,768 bytes at a time from a file, then store these in a BLOB in the Oracle database, and change the starting location by 32,768 each time you read the file. Buffer is the location to store the contents of the file just read.

The VALUE_ERROR exception is raised if any of the parameters are NULL. The INVALID_ARGVAL exception is raised if any of the arguments are invalid. NO_DATA_FOUND is raised if you have reached the end of the file. If the file has not been opened, you will receive the UNOPENED_FILE exception.

The SUBSTR Function

The SUBSTR function allows you to extract a specified amount of bytes from a file.

The Syntax for the SUBSTR Function

FUNCTION SUBSTR(BFILE_Locator,
                       Read_Amount IN BINARY_INTEGER,
                           Starting_Location IN INTEGER := 1)
RETURN RAW;

The parameter BFILE_Locator is the BFILE locator assigned to the file from the BFILENAME function. Read_Amount is the number of bytes you want to extract from the file. Starting_Location is the position in the file where you want to start your extraction. The function will return a RAW value if successful.

Examples of BFILEs Using the DBMS_LOB
Package

You can now test all these functions and procedures with an anonymous PL/SQL block in the next several examples. The functions and procedures common to internal and external LOBs, such as INSTR and SUBSTR, will be demonstrated later in this chapter in the section "Examples of Internal LOBs Using the DBMS_LOB Package."

Accessing BFILEs

This first example demonstrates how to open files, close files, and do some minor error checking. Before you run any of these examples, make sure you have entered SET SERVEROUTPUT ON at the SQL*Plus prompt so that you can see that the examples are working as they execute. Execute the code in Listing 15.4.

INPUT
Listing 15.4. BFILE file operations.

DECLARE

/* This Anonymous PL/SQL block will demonstrate how to
     open a BFILE, close the BFILE, and do some error checking
      with FILEEXISTS, FILEISOPEN, and retrieve the Directory
     Object and Path with GETFILENAME  */

       v_BOOKFILE BFILE; -- BFILE to access
      v_DIRNAME VARCHAR2(30); -- Holds Directory Object for FILEGETNAME
     v_LOCATION VARCHAR2(2000); -- Holds filename for FILEGETNAME
       v_FILEISOPEN INTEGER; -- Holds status to check if the file is open
        v_FILEEXISTS INTEGER; -- Holds status if the file actually exists

BEGIN
     v_BOOKFILE := BFILENAME(`BOOKS_DIR','BOOK1.GIF'); -- Create Locator
     v_FILEISOPEN := DBMS_LOB.FILEISOPEN(v_BOOKFILE); -- Check if file open

     v_FILEEXISTS := DBMS_LOB.FILEEXISTS(v_BOOKFILE);

     IF v_FILEEXISTS = 1 THEN
           DBMS_OUTPUT.PUT_LINE(`The file exists');
     ELSE
           DBMS_OUTPUT.PUT_LINE(`The file cannot be found');
     END IF;

       IF v_FILEISOPEN = 1 THEN  --Determine actions if file is opened or not
           DBMS_OUTPUT.PUT_LINE(`The file is open');
     ELSE
           DBMS_OUTPUT.PUT_LINE(`Opening the file');
         DBMS_LOB.FILEOPEN(v_BOOKFILE);
     END IF;
    DBMS_LOB.FILEGETNAME(v_BOOKFILE,v_DIRNAME,v_LOCATION);
      DBMS_OUTPUT.PUT_LINE(`The Directory Object is: ` || v_DIRNAME ||
          ` The File Name is: ` || v_LOCATION);

     DBMS_LOB.FILECLOSE(v_BOOKFILE); -- Close the BFILE

END;

After executing the code in Listing 15.4, your output will appear as

OUTPUT

The file exists
Opening the file
The Directory Object is: BOOKS_DIR The File Name is: BOOK1.GIF

ANALYSIS
In the DECLARE section, a BFILE locator is defined as v_BOOKFILE. The v_DIRNAME and v_LOCATION variables hold the results of the FILEGETNAME function. The v_FILEISOPEN and v_FILEEXISTS variables hold the status if the file is open and if the file exists. The first step in the execution is to create the BFILE locator to the BOOKS_DIR path with a filename of BOOK1.GIF. Both v_FILEISOPEN and v_FILEEXISTS are assigned values to see if the file is open and if the file exists. Because the FILEOPEN procedure was not called, this value will be 0. If you have created the path and copied the files to the BOOKS directory, you should receive a value of 1, which states that the file does exist, and you should see output stating that the file exists.

Because the FILEOPEN procedure returned a value of 0, the file is not open. A message is then displayed, "Opening the file," and then the FILEOPEN procedure executes and opens the file for access from Oracle. Next, the FILEGETNAME procedure returns the directory object and the filename. Finally, the file is closed, and the execution ends.

Comparing Files

You can now practice comparing files and also getting the length of these files by executing the code in Listing 15.5.

INPUT
Listing 15.5. BFILE comparisons.

DECLARE

/* The purpose of this anonymous PL/SQl block is to compare
   the contents of three files completely.  The size of the
   files is determined by the GETLENGTH function */

     v_FILE1 BFILE;
     v_FILE2 BFILE;
     v_FILE3 BFILE;
     v_GETLENGTH1 INTEGER; -- Hold length of the file
     v_GETLENGTH2 INTEGER; -- Hold length of the file
     v_GETLENGTH3 INTEGER; -- Hold length of the file
     v_COMPARELENGTH INTEGER; -- Holds smallest of two values
     v_COMPARERESULT INTEGER; -- Hold result of comparing files

BEGIN

                                                continues