Monday, March 12, 2012

Oracle BLOB to File Extract...

If you are using TOAD, then you don't need to look any further to export BLOB column data into a file.
Just write a simple select statement (select blob_column, reg_col as file_name from blob_table;) in Editor and execute the statement. Right Click in the Data Grid Once the records are fetched and chose option "Export Blobs" and follow the instructions you are good to go.

Well, depending on the resources available on your PC, TOAD might throw an error with Low Memory or other issues when the exporting BLOB entries are huge or number of records to fetch are more.

Here is the simple proc that can be utilized instead and store all these files on the database server and get'em later.
Make sure that you have write access to Oracle Directory that you are going to use.


CREATE OR REPLACE procedure blob2file
is
 t_blob   blob;
 vstart  number := 1;
 bytelen number := 32000;
 t_blob_len     number;
 my_vr   raw(32000);
 x       number;
 t_file_name varchar2(200);
 l_output utl_file.file_type;
begin

  for i in (
  select reg_col file_name,
         dbms_lob.getlength(blob_column) blob_len,
         blob_column blob_val
    from blob_table
  ) loop

  begin
      -- save blob length
      x := i.blob_len;
     
      -- define output directory
      l_output := utl_file.fopen('ORACLE_DIRECTORY_NAME_HERE', i.file_name, 'WB', 32760);

      -- if small enough for a single write
      if i.blob_len < 32760 then
        utl_file.put_raw(l_output,i.blob_val);
        utl_file.fflush(l_output);
      else -- write in pieces
        vstart := 1;
        while vstart < i.blob_len
        loop
          dbms_lob.read(i.blob_val,bytelen,vstart,my_vr);

          utl_file.put_raw(l_output,my_vr);
          utl_file.fflush(l_output);

          -- set the start position for the next cut
          vstart := vstart + bytelen;

          -- set the end position if less than 32000 bytes
          x := x - bytelen;
          if x < 32000 then
            bytelen := x;
          end if;
        end loop;
      end if;
      utl_file.fclose(l_output);
 
  end;
 
  end loop;

end blob2file;
/

No comments:

Post a Comment