Writing CLOB to file using UTL_FILE
DaljitFeb 26 2008 — edited Jun 19 2008I am having problem while writing clob to file using utl_file package. The clob size is around 70k and I know the problem is we have to put a new line character after 32k amount of data but in my case I want some other way around because I dont want to change the data in any way (business requirement). So I am looking for some other way like converting it to raw and writing the whole string as raw, i am not sure if that works without new line character but still wanna try it or if somebody else has already solved this problem then it will be great. I just need the same clob string to be written to the OS file without changing/adding even a single character. Here is the code I am using:
declare
clob_len integer;
pos integer :=1;
buffer raw(32767);
char_buffer varchar2(32767);
amount binary_integer := 32760;
clob_loc clob;
bin_output utl_file.file_type;
dir_name varchar2(100) default 'ARCHIVE';
bin_file varchar2(1000);
datetime varchar2(1000);
counter number := 0;
begin
select to_char(sysdate,'ddmonyyyyhh24miss') into datetime from dual;
select COMPLETE_EXCEPTION into clob_loc from application_exceptions where id = 3020566;
clob_len := dbms_lob.getlength(clob_loc);
dbms_output.put_line(clob_len);
bin_file := 'nhinid-application_exceptions-3020566-' || datetime || '.dat';
bin_output := utl_file.fopen( dir_name, bin_file, 'w', max_linesize => 32767 );
while pos < clob_len loop
counter := counter + 1;
dbms_lob.read(clob_loc, amount, pos, char_buffer);
-- dbms_lob.read(clob_loc, amount, pos, buffer);
-- utl_file.put_raw(bin_output, buffer, true);
utl_file.put(bin_output, char_buffer);
--utl_file.new_line(bin_output);
utl_file.fflush(bin_output);
pos := pos + amount;
end loop;
end;
/
As you can see I tried UTL_FILE.PUT_RAW but it didn't work, the above code is working if I uncommect UTL_FILE.NEW_LINE.
Database Version: 10.2.0.2 on Linux SLES 9.
Thanks
Daljit Singh