Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Writing CLOB to file using UTL_FILE

DaljitFeb 26 2008 — edited Jun 19 2008
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 17 2008
Added on Feb 26 2008
16 comments
54,889 views