Showing posts with label DBA.SQL. Show all posts
Showing posts with label DBA.SQL. Show all posts

Tuesday, February 23, 2021

Use UTL_FILE to Write BLOBs Stored Inside the Database Out to Files

Create a precedure as following
create or replace procedure  save_blob_to_file (a_directory varchar2, a_filename varchar2, a_data blob)
is
  p_blob_length  integer;
  p_file         utl_file.file_type;
  p_offset       integer := 1;
  p_chunksize    pls_integer := 32767;
  p_buffer       raw(32767);
begin
  /* Determine size of BLOB */
  p_blob_length := dbms_lob.getlength(a_data);
  /* open a file handle */
  p_file := utl_file.fopen(a_directory, a_filename, 'wb', p_chunksize);
  begin
    /* write BLOB to file in chunks */
    while p_offset <= p_blob_length loop
      if p_offset + p_chunksize - 1 > p_blob_length then
         p_chunksize := p_blob_length - p_offset + 1;
      end if;
      dbms_lob.read(a_data, p_chunksize, p_offset, p_buffer);
      utl_file.put_raw(p_file, p_buffer, true);
      p_offset := p_offset + p_chunksize;
    end loop;
  exception
    when others then
      /* Close the file handle in case of any exceptions*/
      utl_file.fclose(p_file);
      raise;
  end;
  /* Close the file handle after all data is written out*/
  utl_file.fclose(p_file);
end;
/

Sample code to save BLOB to file with procedure 'save_blob_to_file'
declare
  p_blob       blob;
  p_directory  varchar2(30) := 'BLOB_DIR';
  p_filename   varchar2(255):= 'first_blob_file.tst';
begin
  select blob_data into p_blob from tb_blob_file where rownum=1;
  save_blob_to_file(p_directory,p_filename,p_blob);
end;
/

Here, directory 'BLOB_DIR' has to be created before calling the procedure. Example for creating directory,
system@db01> create directory BLOB_DIR as '/u01/blob_files';
Directory created.

Make sure OS directory '/u01/blob_files' exists and Oracle home owner (normally oracle) has read/write under this directory
[oracle@host01]$ ls -ld /u01/blob_files
drwxr-xr-x    2 oracle   oinstall       8192 Feb 20 20:33 /u01/blob_files