Using BFILEs to load LOB columns

In addition to allowing you to access binary file data created outside the Oracle database environment, BFILEs provide a convenient means to load data from external files into internal LOB columns. Up through Oracle9i Release 1, you can use the DBMS_LOB.LOADFROMFILE function to read binary data from a BFILE and store it into a BLOB column. Oracle9i Release 2 introduced the following, much improved, functions:

DBMS_LOB.LOADCLOBFROMFILE

Loads CLOBs from BFILEs. Takes care of any needed character set translation.

DBMS_LOB.LOADBLOBFROMFILE

Loads BLOBs from BFILEs. Does the same thing as DBMS_LOB.LOADFROMFILE, but with an interface that is consistent with that of LOADCLOBFROMFILE.

Imagine that we had directions to Tannery Falls in an external text file named TanneryFalls.directions in a directory pointed to by the BFILE_DATA directory alias. The following example shows how we could use DBMS_LOB.LOADCLOBFROMFILE to load the directions into the falls_directions CLOB column in the waterfalls table:

DECLARE Tannery_Falls_Directions BFILE := BFILENAME('BFILE_DATA','TanneryFalls.directions'); directions CLOB; destination_offset INTEGER := 1; source_offset INTEGER := 1; language_context INTEGER := DBMS_LOB.default_lang_ctx; warning_message INTEGER;BEGIN --Delete row for Tannery Falls, so this example --can run multiple times. DELETE FROM waterfalls WHERE falls_name='Tannery Falls'; --Insert a new row using EMPTY_CLOB( ) to create a LOB locator INSERT INTO waterfalls (falls_name,falls_directions) VALUES ('Tannery Falls',EMPTY_CLOB( )); --Retrieve the LOB locator created by the previous INSERT statement SELECT falls_directions INTO directions FROM waterfalls WHERE falls_name='Tannery Falls'; --Open the target CLOB and the source BFILE DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE); DBMS_LOB.OPEN(Tannery_Falls_Directions); --Load the contents of the BFILE into the CLOB column DBMS_LOB.LOADCLOBFROMFILE(directions, Tannery_Falls_Directions, DBMS_LOB.LOBMAXSIZE, destination_offset, source_offset, NLS_CHARSET_ID('US7ASCII'), language_context, warning_message); --Check for the only possible warning message. IF warning_message = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN dbms_output.put_line( 'Warning! Some characters couldn''t be converted.'); END IF; --Close both LOBs DBMS_LOB.CLOSE(directions); DBMS_LOB.CLOSE(Tannery_Falls_Directions);END;

The real work in this snippet of code is done by the call to DBMS_LOB.LOADCLOBFROMFILE. That procedure reads data from the external file, performs any character set translation that's necessary, and writes the data to the CLOB column. We use the new DBMS_LOB.LOBMAXSIZE constant to specify the amount of data to load. We really want all the data from the external file, and DBMS_LOB.LOBMAXSIZE is as much as a CLOB will hold.

The destination and source offsets both begin at 1. We want to begin reading with the first character in the BFILE, and we want to begin writing to the first character of the CLOB. To facilitate multiple, sequential calls to LOADCLOBFROMFILE, the procedure will update both of these offsets to point one character past the most recently read character. Because they are IN OUT parameters, we must use variables and not constants in our procedure call.

The call to NLS_CHARSET_ID returns the character set ID number for the character set used by the external file. The LOADCLOBFROMFILE procedure will then convert the data being loaded from that character set to the database character set. The only possible warning message that LOADCLOBFROMFILE can return is that some characters were not convertible from the source to the target character set. We check for this warning in the IF statement following the load.