https://pastein.ru/t/X7

  скопируйте уникальную ссылку для отправки


  /****************************************************************************/
  function file2table(p_file_name varchar2) return file_result_t is
    l_dir_name constant varchar2(100) := 'RAN_TMP_FILES';
    l_res  file_result_t := file_result_t();
    l_file utl_file.file_type;
    l_line varchar2(32767);
  begin
    l_file := utl_file.fopen(l_dir_name, p_file_name, 'r', 32767);
    loop
      begin
        utl_file.get_line(l_file, l_line);
        l_line := trim(chr(10) from l_line);
        l_line := trim(chr(13) from l_line);
        l_res.extend;
        l_res(l_res.last) := l_line;
      exception
        when no_data_found then
          exit;
      end;
    end loop;
    utl_file.fclose(l_file);
  
    return l_res;
  exception
    when others then
      if utl_file.is_open(l_file)
      then
        utl_file.fclose(l_file);
      end if;
      raise;
  end;

  -----------------------------------------------------------------------------------------------
  procedure blob2file(p_file_content blob
                     ,p_file_name    varchar2
                     ,p_file_dir     varchar2 default 'DEFAULT_DIR') is
    l_file_ptr utl_file.file_type;
    l_pos      binary_integer := 1;
    l_file_length     constant binary_integer := dbms_lob.getlength(p_file_content);
    c_bytes_per_write constant binary_integer := 32767;
  begin
    l_file_ptr := utl_file.fopen(p_file_dir, p_file_name, 'wb', c_bytes_per_write);
    ---
    while l_pos < l_file_length
    loop
      utl_file.put_raw(l_file_ptr, dbms_lob.substr(p_file_content, c_bytes_per_write, l_pos), true);
      l_pos := l_pos + c_bytes_per_write;
    end loop;
    ---
    utl_file.fclose(l_file_ptr);
  exception
    when others then
      if utl_file.is_open(l_file_ptr)
      then
        utl_file.fclose(l_file_ptr);
      end if;
      ---
      raise;
  end;

procedure save_blob_to_file(p_location varchar2, p_file_name varchar2, p_blob in out nocopy blob) is
	l_file     utl_file.file_type;
	l_buffer   raw(32767);
	l_amount   binary_integer := 32767;
	l_pos      integer := 1;
	l_blob_len integer;
begin
	---
	l_blob_len := dbms_lob.getlength(p_blob);
	---
	l_file := utl_file.fopen(p_location, p_file_name, 'wb', 32767);
	---
	while l_pos < l_blob_len
	loop
		dbms_lob.read(p_blob, l_amount, l_pos, l_buffer);
		utl_file.put_raw(l_file, l_buffer, true);
		l_pos := l_pos + l_amount;
	end loop;
	---
	utl_file.fclose(l_file);
exception
	when others then
		if utl_file.is_open(l_file) then
			utl_file.fclose(l_file);
		end if;
		raise;
end;