Loading LOB 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, and ‘wc’ tells us it is 75 bytes in size 



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 TMP 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( 'TMP', '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 is), 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 you could load it piecewise:


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( 'TMP', '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.

Or you could use LOADCLOBFROMFILE with the extra parameters


SQL> declare
  2    v_lob     clob;
  3    l_bfile   bfile;
  4    amt       number;
  5
  6    d_offset number := 1;
  7    s_offset number := 1;
  8    csid     number := 0;
  9    lang     number := 0;
 10    warning  number;
 11  begin
 12    l_bfile := bfilename( 'TMP', 'lobfile' );
 13
 14    insert into lob_tab (the_lob)
 15    values ( empty_clob() )
 16    returning the_lob into v_lob;
 17
 18    amt := dbms_lob.getlength( l_bfile );
 19    dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
 20    dbms_lob.LOADCLOBFROMFILE( v_lob, l_bfile ,amt, d_offset,s_offset,csid, lang,warning );
 21
 22    dbms_output.put_line('File length is: '||dbms_lob.getlength( l_bfile ));
 23    dbms_output.put_line('Loaded length is: '||dbms_lob.getlength(v_lob));
 24    dbms_lob.fileclose( l_bfile );
 25  end;
 26  /
File length is: 75
Loaded length is: 75

PL/SQL procedure successfully completed.

It might look like a bug, but it’s not really – if you look carefully, you’ll see that this behaviour is documented in the DBMS_LOB manual.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s