Listing 15.5. continued

-- Create three locators for each of the files to compare

     v_FILE1 := BFILENAME(`BOOKS_DIR','BOOK1.GIF');
     v_FILE2 := BFILENAME(`BOOKS_DIR','BOOK2.GIF');
     v_FILE3 := BFILENAME(`BOOKS_DIR','BOOK5.GIF');

-- Open the files for access

     DBMS_LOB.FILEOPEN(v_FILE1);
     DBMS_LOB.FILEOPEN(v_FILE2);
     DBMS_LOB.FILEOPEN(v_FILE3);

     v_GETLENGTH1 := DBMS_LOB.GETLENGTH(v_FILE1);
     v_GETLENGTH2 := DBMS_LOB.GETLENGTH(v_FILE2);
     v_GETLENGTH3 := DBMS_LOB.GETLENGTH(v_FILE3);

-- Compare 1st and 2nd File
     IF v_GETLENGTH1 < v_GETLENGTH2 THEN
          v_COMPARELENGTH := v_GETLENGTH1;
     ELSE
          v_COMPARELENGTH := v_GETLENGTH2;
     END IF;

     v_COMPARERESULT := DBMS_LOB.COMPARE(v_FILE1,v_FILE2,
          v_COMPARELENGTH,1,1);

     IF v_COMPARERESULT = 0 THEN
          DBMS_OUTPUT.PUT_LINE(`Both Files Are Identical');
     ELSE
          DBMS_OUTPUT.PUT_LINE(`Both Files Are Different');
     END IF;

-- Compare 1st and 3rd file
     IF v_GETLENGTH1 < v_GETLENGTH3 THEN
          v_COMPARELENGTH := v_GETLENGTH1;
     ELSE
          v_COMPARELENGTH := v_GETLENGTH3;
     END IF;

     v_COMPARERESULT := DBMS_LOB.COMPARE(v_FILE1,v_FILE3,
          v_COMPARELENGTH,1,1);

     IF v_COMPARERESULT = 0 THEN
          DBMS_OUTPUT.PUT_LINE(`Both Files Are Identical');
     ELSE
          DBMS_OUTPUT.PUT_LINE(`Both Files Are Different');
     END IF;

     DBMS_LOB.FILECLOSEALL;

END;

After the code executes, your output should appear as

OUTPUT

Both Files Are Different
Both Files Are Identical

ANALYSIS
Listing 15.5 first defines several variables for each of the three files to be compared. Three locators are defined as v_FILE1, v_FILE2, and v_FILE3. The file lengths of each of these files are stored in v_GETLENGTH1, v_GETLENGTH2, and v_GETLENGTH3. Both v_COMPARERESULT and v_COMPARELENGTH are used for storing the length of the files.

When the code starts to execute, the three locators are assigned values of three files. These three files are then opened, and the length is retrieved. The lengths of the first two files are compared. The smallest value is stored in v_COMPARELENGTH. If the length to compare was used for the larger file, an exception would be raised because you would attempt to compare the smaller file after the end of the file has been reached. The variable v_COMPARERESULT is assigned the result of comparing the first two files. These files are not identical, and the output correctly states this. The process is repeated for the first and third files, which are identical. The FILECLOSEALL procedure is executed to close all three files and free up the resources.

Working with Locators

Locators are stored in the large object column and point to the location of where the actual data is stored. It is important for you to understand what occurs at a transactional level when using Oracle locators. When copying LOBs from one row to the next, a new locator is created and the entire data is copied and stored from the source row. This is necessary because if you were to delete one row and you did not copy the entire contents to the new row, all the data of the LOB would be lost. When deleting internal LOBs, the locator and the contents of the LOB are both deleted. If you're deleting external BFILEs, the file remains but the locator is deleted. When adding internal LOBs to a table, you need to create the locator either by assigning data to the LOB column or using the function EMPTY_BLOB or EMPTY_CLOB. When adding a BFILE to the table, you would use BFILENAME to assign a locator to the column. The last issue applies to internal LOBs only. It is a good idea to lock the LOB when working with the LOB to prevent other users from accessing the LOB.

The DBMS_LOB Package with Internal
LOBs

The DBMS_LOB package provided by Oracle allows you to manipulate all types of LOBs. Because the BFILE datatype is of type RAW, there are additional files to work with the database character set. Table 15.4 summarizes the functions and procedures that work with internal LOBs.

Table 15.4. Functions and procedures used with internal LOBs.

The APPEND Procedure

The APPEND procedure allows you to append one LOB to another LOB.

The Syntax for the APPEND Procedure for BLOBs and CLOBs

PROCEDURE APPEND(Dest_Locator IN OUT BLOB,
                 Source_Locator  IN BLOB);


PROCEDURE APPEND(Dest_Locator IN OUT CLOB CHARACTER SET Set_Desired,
                 Source_Locator  IN CLOB CHARACTER SET Dest_Locator%CHARSET);

Dest_Locator is the locator for the destination LOB that is appended by the source LOB identified by Source_Locator. When working with CLOBs, you can additionally specify the character set with the parameter Set_Desired.

The COMPARE Function

If you need to compare all or part of a LOB, you can use the COMPARE function.

The Syntax for the COMPARE Function for Both BLOBs and
LOBs

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

FUNCTION COMPARE(
                 Lob1 IN CLOB CHARACTER SET Set_Desired,
                 Lob2 IN CLOB CHARACTER SET LOB1%CHARSET,,
                 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. The Set_Desired parameter is the character set you want to use. If no character set is specified, the Oracle server's character set is used. 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 LOB 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 either 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 COPY Procedure

The COPY procedure allows you to copy all or part of a LOB from one row to another. The entire LOB is copied with a new locator entered into the table, which points to the copy of the original LOB.

The Syntax for the COPY Procedure

PROCEDURE COPY(Dest_Locator IN OUT BLOB,
              Source_Locator  IN BLOB,
           Amount IN OUT INTEGER,
              Dest_Start_Position IN INTEGER := 1,
              Source_Start_Position IN INTEGER := 1);


PROCEDURE APPEND(Dest_Locator IN OUT CLOB CHARACTER SET Set_Desired,
              Source_Locator  IN CLOB CHARACTER SET Dest_Locator%CHARSET,
              Amount IN OUT INTEGER,
                 Dest_Start_Position IN INTEGER := 1,
                 Source_Start_Position IN INTEGER := 1);

Dest_Locator is the locator for the destination LOB that is being copied. The Set_Desired parameter is the character set you want to use. If no character set is specified, the Oracle