René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -

November 20, 2005: On reading blobs from and writing blobs to a file

Writing blobs to a file and reading them from a file is too tedious for my liking. Therefore, I have written a package to faciliate these tasks. It comes with a procedure (to_file) which writes a blob to a file, a function (from_file) which reads a file into a blob and returns it and a procedure (from_file) that copies a file into the blob passed as parameter:
create or replace package blob_wrapper as

  procedure   to_file(dir in varchar2, file in varchar2, lob in blob);

  function  from_file(dir in varchar2, file in varchar2) return blob;
  procedure from_file(dir in varchar2, file in varchar2, b in out blob);

end blob_wrapper;
The package body:
create or replace package body blob_wrapper as

  procedure to_file(dir in varchar2, file in varchar2, lob in blob) is
    output_file    utl_file.file_type;
    chunk_size     constant pls_integer := 4096;
    buf            raw                    (4096); -- Must be equal to chunk_size 
    written_sofar  pls_integer := 0;              --(avoid PLS-00491: numeric literal required)
    bytes_to_write pls_integer;
    lob_len        pls_integer;


    lob_len := dbms_lob.getlength(lob);

    output_file := utl_file.fopen(dir, file, 'WB');
    while written_sofar + chunk_size < lob_len loop 

      bytes_to_write := chunk_size;,bytes_to_write,written_sofar+1,buf);
      written_sofar := written_sofar + chunk_size;

    end loop;

    bytes_to_write := lob_len-written_sofar;,bytes_to_write,written_sofar+1,buf);


  end to_file;

  function from_file(dir in varchar2, file in varchar2) return blob is
    ret blob;
    dbms_lob.createTemporary(ret, true);
    from_file(dir, file, ret);  
    return ret;
  end from_file;

  procedure from_file(dir in varchar2, file in varchar2, b in out blob) is 
    input_file    utl_file.file_type;
    chunk_size    constant pls_integer := 4096;
    buf           raw                    (4096); -- Must be equal to chunk_size 
    read_sofar    pls_integer := 0;              --(avoid PLS-00491: numeric literal required)
    bytes_to_read pls_integer;

    input_file := utl_file.fopen(dir, file, 'RB');

    begin loop

      utl_file.get_raw(input_file, buf, chunk_size);
      bytes_to_read := length(buf) / 2; -- strange and unexplanable!
      dbms_lob.write(b, bytes_to_read, read_sofar+1, buf);
      read_sofar := read_sofar + bytes_to_read;

    -- utl_file raises no_data_found when unable to read
    end loop; exception when no_data_found then null; end;


  end from_file;

end blob_wrapper;


Testing to_file.
A directory object must be created. It points to the OS directory in which the file will be created:
create directory lob_test_dir as 'c:\temp';
The following simple procedure creates an (ascii) blob that contains a circle (made of 'X'es) whose radius is specified through the parameter radius. The circle is printed into a rectangle (made of ' 'es) whose width and heigth are controlled through the parameter out_width. The blob is then written into a file whose name is specifed through the parameter file:
Note: the resulting width and height can differ by 1 from the size passed. In the following case, I specify 400 for the width, but the resulting file will have 401 lines with 402 characters each (401 'X'es or ' 'es plus chr(10) as line terminator).
create or replace procedure write_circle(
  out_width in number, 
  radius    in number, 
  dir       in varchar2, 
  file      in varchar2) 


  some_lob blob;
  one_byte raw(1);


  some_lob := empty_blob();
  dbms_lob.createTemporary(some_lob, true);, dbms_lob.lob_readwrite);

  for x in -out_width/2 .. out_width/2 loop 
  for y in -out_width/2 .. out_width/2 loop

    if sqrt(x*x + y*y) > radius/2 then
      one_byte := utl_raw.cast_to_raw(' ');
      one_byte := utl_raw.cast_to_raw('X');
    end if;
    dbms_lob.append(some_lob, one_byte);

  end loop; 
  one_byte := utl_raw.cast_to_raw(chr(10));
  dbms_lob.append(some_lob, one_byte);
  end loop;

  blob_wrapper.to_file(dir, file, some_lob);

end write_circle;
Executing write_circle:
  write_circle(400, 300, 'LOB_TEST_DIR', 'circle.txt');
host dir c:\temp\circle.txt
The created file's size is 161,202 bytes (which corresponds to 401*402):
 Directory of c:\temp

11/20/2005  01:19 AM           161,202 circle.txt
               1 File(s)        161,202 bytes
               0 Dir(s)   3,117,477,888 bytes free


Testing from_file. The created file is now read into a blob, the blob's size printed and then again written into a new file (called circle_new.txt):
  b blob;
  b := blob_wrapper.from_file('LOB_TEST_DIR', 'circle.txt');
  dbms_output.put_line('size of circle.txt: ' || dbms_lob.getlength(b));
  blob_wrapper.to_file('LOB_TEST_DIR', 'circle_new.txt', b);
161,202 bytes read:
size of circle.txt: 161202
As a sanity check, both files are compared with fc for file compare (on Windows, that is; on Unix, the command would be diff):
host fc c:\temp\circle.txt c:\temp\circle_new.txt
Yep, no difference found:
Comparing files C:\TEMP\circle.txt and C:\TEMP\CIRCLE_NEW.TXT
FC: no differences encountered

Inserting a blob from a file into a table

A table with a blob:
create table blob_wrapper_test (
  id   number primary key,
  blb  blob 
This table's blob is populated with the content of file found in c:\temp\some.txt:
  blob_ blob;
  insert into blob_wrapper_test values (1, empty_blob() ) return blb into blob_;

  blob_wrapper.from_file('LOB_TEST_DIR', 'some.txt', blob_);

Then, the blob is read from the table and printed to SQL*Plus with dbms_output as well as saved into a new file (c:\temp\saved.txt) with to_file:
set serveroutput on size 1000000 format wrapped

  blob_ blob;
  for r in (select id, blb from blob_wrapper_test) loop
    dbms_output.put_line( || ': ' || utl_raw.cast_to_varchar2(r.blb));
    blob_wrapper.to_file('LOB_TEST_DIR', 'saved.txt', r.blb);
  end loop;
Finally, the directory object is dropped:
drop directory lob_test_dir;


Update November 22th 2005: This package is used in On creating bitmaps with pure PL/SQL .

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.