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;

  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 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.