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;

  begin

    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;
      dbms_lob.read(lob,bytes_to_write,written_sofar+1,buf);
      utl_file.put_raw(output_file,buf);
      written_sofar := written_sofar + chunk_size;

    end loop;

    bytes_to_write := lob_len-written_sofar;
    dbms_lob.read(lob,bytes_to_write,written_sofar+1,buf);
    utl_file.put_raw(output_file,buf);

    utl_file.fclose(output_file);

  end to_file;

  function from_file(dir in varchar2, file in varchar2) return blob is
    ret blob;
  begin
    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;
  begin

    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;

    utl_file.fclose(input_file);

  end from_file;

end blob_wrapper;
/

to_file

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) 

is

  some_lob blob;
  one_byte raw(1);

begin

  some_lob := empty_blob();
  dbms_lob.createTemporary(some_lob, true);
  dbms_lob.open(some_lob, 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(' ');
    else
      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);

  dbms_lob.close(some_lob);
end write_circle;
/

Executing write_circle:

begin
  write_circle(400, 300, 'LOB_TEST_DIR', 'circle.txt');
end;
/

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

from_file

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):

declare
  b blob;
begin
  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);
end;
/

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:

declare
  blob_ blob;
begin
  insert into blob_wrapper_test values (1, empty_blob() ) return blb into blob_;

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

commit;

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

declare
  blob_ blob;
begin
  for r in (select id, blb from blob_wrapper_test) loop
    dbms_output.put_line(r.id || ': ' || utl_raw.cast_to_varchar2(r.blb));
    blob_wrapper.to_file('LOB_TEST_DIR', 'saved.txt', r.blb);
  end loop;
end;
/

Finally, the directory object is dropped:

drop directory lob_test_dir;

Update

Update November 22^th 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.


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