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