write CLOB data to a file

I had some problems with the function described here : http://www.dbforums.com/oracle/1624851-clob-blob.html and here : http://www.dba-oracle.com/t_read_blob.htm




The error was :
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SYS.DBMS_LOB", line 696

The solution was very simple (see note 358641.1   ):

create or replace PROCEDURE clob_to_file (p_dir IN VARCHAR2, p_file IN VARCHAR2, p_clob IN CLOB) IS
 l_output UTL_FILE.file_type;
 l_amt NUMBER DEFAULT 32000;
 l_offset NUMBER DEFAULT 1;
 l_length NUMBER DEFAULT NVL (DBMS_LOB.getlength (p_clob), 0);
 x varchar2(32760);
BEGIN
 l_output := UTL_FILE.fopen (p_dir, p_file, 'w', 32760);
 WHILE (l_offset < l_length) LOOP
  dbms_lob.read (p_clob, l_amt, l_offset, x);
  UTL_FILE.put (l_output, x);
  UTL_FILE.fflush (l_output);
  UTL_FILE.new_line (l_output);
  l_offset := l_offset + l_amt;
 END LOOP;
 UTL_FILE.fclose (l_output);
END clob_to_file;
/