Loading LOBs from a file


I observed this idiosyncracy recently when loading some lob from external files using PL/SQL:

First we'll create a file to be loaded

SQL> !echo "This is line 1" > /tmp/lobfile
SQL> !echo "This is line 2" >> /tmp/lobfile
SQL> !echo "This is line 3" >> /tmp/lobfile
SQL> !echo "This is line 4" >> /tmp/lobfile
SQL> !echo "This is line 5" >> /tmp/lobfile
SQL> !wc /tmp/lobfile
       5      20      75 /tmp/lobfile

Then create a standard routine to load it into the database

SQL> create or replace directory LOB_SOURCE as '/tmp';

Directory created.

SQL> drop table lob_tab;

Table dropped.

SQL> create table lob_tab ( the_lob clob );

Table created.

SQL> declare
  2    v_lob     clob;
  3    l_bfile   bfile;
  4    amt       number;
  5  begin
  6    l_bfile := bfilename( 'LOB_SOURCE', 'lobfile' );
  7
  8    insert into lob_tab (the_lob)
  9    values ( empty_clob() )
 10    returning the_lob into v_lob;
 11
 12    amt := dbms_lob.getlength( l_bfile );
 13    dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
 14    dbms_lob.loadfromfile( v_lob, l_bfile ,amt);
 15
 16    dbms_output.put_line('File length is: '||dbms_lob.getlength( l_bfile ));
 17    dbms_output.put_line('Loaded length is: '||dbms_lob.getlength(v_lob));
 18    dbms_lob.fileclose( l_bfile );
 19  end;
 20  /
File length is: 75
Loaded length is: 37   <==== !!!!

PL/SQL procedure successfully completed.

So what has happened to the second half of the LOB ?!?!?!?!

It turns out to be related to the character set. If you're using UTF8 (as this database was since it was running Internet Directory), then loadfromfile must assume the possibility of multibyte characters (since the bfile could be binary). Thus you get two bytes per character and hence "garbage" in the lob.

The workaround is to use sqlldr, or load the lob without using loadfromfile, eg

SQL> declare
  2    v_lob     clob;
  3    l_bfile   bfile;
  4    buf       raw(32767);
  5    vc        varchar2(32767);
  6    amt       number;
  7    v_offset  number := 1;
  8  begin
  9    l_bfile := bfilename( 'LOB_SOURCE', 'lobfile' );
 10
 11    insert into lob_tab (the_lob)
 12    values ( empty_clob() )
 13    returning the_lob into v_lob;
 14
 15    amt := dbms_lob.getlength( l_bfile );
 16    dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
 17    dbms_lob.read(l_bfile,amt,v_offset,buf);
 18    vc := utl_raw.cast_to_varchar2(buf);
 19    dbms_lob.writeappend(v_lob,amt,vc);
 20
 21    dbms_output.put_line('File length is: '||dbms_lob.getlength( l_bfile ));
 22    dbms_output.put_line('Loaded length is: '||dbms_lob.getlength(v_lob));
 23    dbms_lob.fileclose( l_bfile );
 24  end;
 25  /
File length is: 75
Loaded length is: 75

PL/SQL procedure successfully completed.

Only after I played about with my code (which I was sure was the culprit) for 'n' hours did I discover Metalink "bug" 1576747 as the true cause. Its not a bug really - if you look carefully, you'll see that this behaviour is documented in the DBMS_LOB manual.