Oracle Procedure to Write a CLOB to a File

I've seen several ways to do this but this way works nicely for me. (Using Oracle 11g)

CREATE OR REPLACE PROCEDURE clob_to_file ( p_directory IN VARCHAR2, p_filename IN VARCHAR2, p_clob IN CLOB ) IS c_chunk CONSTANT PLS_INTEGER := 32767; l_fHandler UTL_FILE.FILE_TYPE; l_pos PLS_INTEGER := 1; BEGIN l_fHandler := UTL_FILE.FOPEN(p_directory, p_filename, 'W', c_chunk); WHILE l_pos < DBMS_LOB.GETLENGTH(p_clob) LOOP UTL_FILE.PUT(l_fHandler, DBMS_LOB.SUBSTR(p_clob, c_chunk, l_pos)); l_pos := l_pos + c_chunk; UTL_FILE.FFLUSH(l_fHandler); END LOOP; UTL_FILE.FCLOSE(l_fHandler); END; /

The UTL_FILE package writes the clob in 32767 byte chunks. This has a limitation that each 32K chunk has to have a carriage return (I've not come across an output without one, so not captured and processed the error here but you could easily), otherwise you get the following error:

ORA-29285: file write error ORA-06512: at "SYS.UTL_FILE", line 183 ORA-06512: at "SYS.UTL_FILE", line 1169 ORA-06512: at "MYSCHEMA.CLOB_TO_FILE", line 16 ORA-06512: at line 8

Written.