Data Loading and Unloading - Expert Oracle Database Architecture - page 743

I mentioned earlier in this section that LoadCLOBFromFile allows us to tell the database that the file it is about
to load is in a character set different from the one the database is using, and that it should perform the required
character set conversion. If you run the prior examples and get this for your output:
DBMS_LOB.GETLENGTH(THECLOB) THECLOB
--------------------------- ------------------------------
6 ??????
then you most likely are running into a character set mismatch between your database and the encoding used for the
file. This next example uses the LoadCLOBFromFile to account for a file encoded in WE8ISO8859P1 :
EODA@ORA12CR1> declare
2 l_clob clob;
3 l_bfile bfile;
4 dest_offset integer := 1;
5 src_offset integer := 1;
6 src_csid number := NLS_CHARSET_ID('WE8ISO8859P1');
7 lang_context integer := dbms_lob.default_lang_ctx;
8 warning integer;
9 begin
10 insert into demo values ( 1, empty_clob() )
11 returning theclob into l_clob;
12 l_bfile := bfilename( 'dir2', 'test.txt' );
13 dbms_lob.fileopen( l_bfile );
14 dbms_lob.loadclobfromfile( l_clob, l_bfile, dbms_lob.getlength( l_bfile ), dest_offset,
src_offset,
src_csid, lang_context,warning );
15 dbms_lob.fileclose( l_bfile );
16 end;
17 /
PL/SQL procedure successfully completed.
Now selecting from the table we see this in the output:
EODA@ORA12CR1> select dbms_lob.getlength(theClob), theClob from demo;
DBMS_LOB.GETLENGTH(THECLOB) THECLOB
--------------------------- ------------------------------
13 Hello World!
There are methods other than the load from file routines by which you can populate a LOB using PL/SQL. Using
DBMS_LOB and its supplied routines is by far the easiest if you are going to load the entire file. If you need to process the
contents of the file while loading it, you may also use DBMS_LOB.READ on the BFILE to read the data. The use of
UTL_RAW.CAST_TO_VARCHAR2 is handy here if the data you are reading is text, not RAW . You may then use DBMS_LOB.WRITE
or WRITEAPPEND to place the data into a CLOB or BLOB .