The process will always finish with a NO_DATA_FOUND exception when the end of the CLOB is reached. For simplicity I've not trapped any of the possible UTL_FILE exceptions.
DECLARE l_file UTL_FILE.FILE_TYPE; l_clob CLOB; l_buffer VARCHAR2(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; BEGIN SELECT col1 INTO l_clob FROM tab1 WHERE rownum = 1; l_file := UTL_FILE.fopen('DOCUMENTS', 'Sample2.txt', 'w', 32767); LOOP DBMS_LOB.read (l_clob, l_amount, l_pos, l_buffer); UTL_FILE.put(l_file, l_buffer); l_pos := l_pos + l_amount; END LOOP; EXCEPTION WHEN OTHERS THEN -- Do not really use WHEN OTHERS exceptions, this is just as an example. DBMS_OUTPUT.put_line(SQLERRM); UTL_FILE.fclose(l_file); RAISE; END;
You need to reinitialize l_amount also (I saw you have reinitialized the other two variables).
while l_pos < l_blob_len loop dbms_lob.read(l_blob, l_amount, l_pos, l_buffer); utl_file.put_raw(l_file, l_buffer, true); l_pos := l_pos + l_amount; end loop; -- Close the file. utl_file.fclose(l_file);
pj**** wrote:No, there's no 32K limit, only that UTL_FILE can only write out in 32K chunks at any one time, so you have to loop and keep writing out chunks until it's all written out.
Is there any 32K limitation while saving clob to file?
The content in my clob is larger than 32K.