Search notes:

Oracle: PL/SQL package BLOB_WRAPPER

The PL/SQL package blob_wrapper is basically a wrapper around dbms_lob.
I believe I wrote this package around 2005 when I found it too tedious to write blobs to a file or read blobs from a file.

Functions and procedures

to_file Write a blob to a file
from_file Read a blob from a file (function and procedure)
substr

Source code

Specification

create or replace package blob_wrapper as

/*
   Package blob_wrapper (spec.plsql and body.plsql)

   Copyright (C) René Nyffenegger

   This source code is provided 'as-is', without any express or implied
   warranty. In no event will the author be held liable for any damages
   arising from the use of this software.

   Permission is granted to anyone to use this software for any purpose,
   including commercial applications, and to alter it and redistribute it
   freely, subject to the following restrictions:

   1. The origin of this source code must not be misrepresented; you must not
      claim that you wrote the original source code. If you use this source code
      in a product, an acknowledgment in the product documentation would be
      appreciated but is not required.

   2. Altered source versions must be plainly marked as such, and must not be
      misrepresented as being the original source code.

   3. This notice may not be removed or altered from any source distribution.

   René Nyffenegger rene.nyffenegger@adp-gmbh.ch

*/

  procedure   to_file(dir in varchar2, file in varchar2, lob in blob);
  procedure   to_file(filepath 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);

  function  substr(b in blob, length_ in number, start_ in number, chunk_size in number := 30000) return blob;

end blob_wrapper;
/
Github repository PL-SQL-pkg-blob_wrapper, path: /spec.plsql

Body

create or replace package body blob_wrapper as

  c_temp_dir_name constant varchar2(30) := 'TQ84_TEMP_DIR';

  type file_path_r is record (
    path       varchar2(4000),
    file_name  varchar2(4000));

  function split_file_path(filepath in varchar2) return file_path_r is -- {

    ret file_path_r;
  begin

    ret.path      := regexp_replace(filepath, '(.*)(\\|/).*', '\1');
    ret.file_name := regexp_replace(filepath, '.*(\\|/)(.*)', '\2');

    return ret;

  end split_file_path; -- }

  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;/*}*/

  procedure to_file(filepath in varchar2, lob in blob) is -- {
    file_path_ file_path_r;

    procedure create_directory(dir in varchar2) is -- {
      pragma autonomous_transaction;
    begin

      execute immediate 'create directory ' || c_temp_dir_name || ' as ''' || dir || '''';

    exception when others then
      if sqlcode = -1031 then -- insufficient privileges
        raise_application_error(-20800, 'Could not create directory, needs ''grant create any directory to ... privilege''');
      end if;

    end create_directory; -- }

    procedure drop_directory is -- {
      pragma autonomous_transaction;
    begin

      execute immediate 'drop directory ' || c_temp_dir_name;

    exception when others then
      if sqlcode = -1031 then -- insufficient privileges
        raise_application_error(-20800, 'Could not drop directory, needs ''grant drop any directory to ... privilege''');
      end if;

    end drop_directory; -- }

  begin

    file_path_ := split_file_path(filepath);

    create_directory(file_path_.path);

    to_file(c_temp_dir_name, file_path_.file_name, lob);

    drop_directory;

  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;/*}*/

  function substr(b in blob, length_ in number, start_ in number, chunk_size in number := 30000) return blob is -- {
     ret       blob;

     offset_   number;
     amount_   number;

     nof_pieces number;
  begin
     dbms_lob.createTemporary(ret, true);

     nof_pieces := 1+trunc( (length_ - 1) / chunk_size);

     for piece in 0 .. nof_pieces -1 loop

--     dbms_output.put_line('  piece: ' || piece);

       offset_ := start_ + piece * chunk_size;

       if (piece+1) * chunk_size > length_ then
          amount_ := mod(length_, chunk_size);
--        dbms_output.put_line(' >: amount_: ' || amount_);
       else
          amount_ := chunk_size;
--        dbms_output.put_line(' <: amount_: ' || amount_);
       end if;

--     dbms_output.put_line('length_: ' || length_);
--     dbms_output.put_line('amount_: ' || amount_);
--     dbms_output.put_line('offset_: ' || offset_);

       dbms_lob.append(ret, dbms_lob.substr(b, amount_, offset_));

     end loop;

     return ret;

  end substr; -- }

end blob_wrapper;
/
Github repository PL-SQL-pkg-blob_wrapper, path: /body.plsql

Write a BLOB into a file

This is an example on how blob_wrapper can be used to write a blob into a file.
This example 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 spaceses) 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).
Finally, the blob is written into a file (blob_wrapper.to_file(…))
declare
  out_width  number       := 400;
  radius     number       := 300;
  dir        varchar2(30) :='LOB_TEST_DIR';
  file       varchar2(30) :='circle.txt';

  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;
/

-- Should be 161'202 (=401*402) bytes:
-- $dir c:\temp\circle.txt

$ &test_dir\compare_files c:\temp\circle.txt &test_dir\expected\circle.txt
Github repository PL-SQL-pkg-blob_wrapper, path: /_test/01_create_circle.txt.sql

Read content from a file into a blob

This is an example on how blob_wrapper can be used to read the content of a file into a blob. (blob_wrapper.from_file).
The file that was created with the first example (circle.txt) is read into a blob. If the size (i.e. length) of the blob is different from the epxected size, a warning is printed.
Finally, the blob is written to a new file: circle_new.txt.
declare
  b blob;
begin
  b := blob_wrapper.from_file('LOB_TEST_DIR', 'circle.txt');

  if dbms_lob.getlength(b) != 161202 then
     dbms_output.put_line('Expected size of circle.txt is 161202');
  end if;

  blob_wrapper.to_file('LOB_TEST_DIR', 'circle_new.txt', b);
end;
/

$ &test_dir\compare_files c:\temp\circle.txt c:\temp\circle_new.txt
Github repository PL-SQL-pkg-blob_wrapper, path: /_test/02_read_write_circle.txt.sql

Read content from a file into a table

This is an example to demonstrate how blob_wrapper can be used to read the content of a file into a table.
create table tq84_blob_wrapper_test (
  id  number primary key,
  blb blob
);


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

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

commit;
Github repository PL-SQL-pkg-blob_wrapper, path: /_test/03_read_circle.txt_into_table.sql

Write content of a table to a file

This is an example on how blob_wrapper can be used to write the content of a blob in a table into a file.
declare
  blob_ blob;
begin

  select blb into blob_ from tq84_blob_wrapper_test;
  blob_wrapper.to_file('LOB_TEST_DIR', 'circle_from_table.txt', blob_);

end;
/

$ &test_dir\compare_files &test_dir\expected\circle.txt c:\temp\circle_from_table.txt

drop table tq84_blob_wrapper_test;
Github repository PL-SQL-pkg-blob_wrapper, path: /_test/04_table_to_file.sql

Writing a blob to a file with a single line of code

exec blob_wrapper.to_file('c:\temp\two_params.txt', utl_raw.cast_to_raw('foo bar baz'))

$ &test_dir\compare_files.bat c:\temp\two_params.txt &test_dir\expected\two_params.txt
Github repository PL-SQL-pkg-blob_wrapper, path: /_test/05_to_file_2_params.sql

substring

declare
  b       blob;

  procedure t(b_ in blob, length_ in number, start_ in number, filename in varchar2) is -- {

    b_expected blob;
    b_gotten   blob;

  begin

    b_expected := dbms_lob    .substr(b_, length_, start_);
    b_gotten   := blob_wrapper.substr(b_, length_, start_, chunk_size => 5);

    blob_wrapper.to_file('LOB_TEST_DIR', 'substr_' || filename || '_expected.txt', b_expected);
    blob_wrapper.to_file('LOB_TEST_DIR', 'substr_' || filename || '_gotten.txt'  , b_gotten  );

    dbms_lob.freeTemporary(b_gotten);

  end t; -- }

begin

  b := utl_raw.cast_to_raw('abcde12345ABCDE');

  t(b,  5, 3, 'de123');
  t(b,  3, 7, '234');
  t(b, 11, 2, 'bcde12345AB');

end;
/

$ &test_dir\compare_files.bat c:\temp\substr_de123_expected.txt       c:\temp\substr_de123_gotten.txt
$ &test_dir\compare_files.bat c:\temp\substr_234_expected.txt         c:\temp\substr_234_gotten.txt
$ &test_dir\compare_files.bat c:\temp\substr_bcde12345AB_expected.txt c:\temp\substr_bcde12345AB_gotten.txt
Github repository PL-SQL-pkg-blob_wrapper, path: /_test/10_substr.sql

See also

blob_wrapper is used in the example that demonstrates the creation of bitmaps (BMPs) with PL/SQL.

Index