ТЕМА:

create table a
(id number,
text clob);

create or replace procedure clobs2file(
                  p_dir  IN VARCHAR2,
                  p_file       IN VARCHAR2) AS
   f_file     utl_file.file_type;
   c_clob     CLOB;
    buff   VARCHAR2(32767);
    offset NUMBER := 1;    
        
 BEGIN
 
 f_file     := utl_file.fopen(p_dir, p_file, 'w',32767);
 
SELECT text INTO c_clob FROM A where id=1;
                  
                  LOOP
                     buff :=dbms_lob.substr(c_clob,32767,offset);
                     EXIT WHEN buff IS NULL;
                     EXIT WHEN length(buff) = 0;
                     offset      := offset + length(buff);
                     utl_file.put(f_file, buff);
                     utl_file.fflush(f_file);
                  END LOOP;
                  
                  
                  utl_file.fclose(f_file);

END;

CREATE OR REPLACE PROCEDURE WRITE_CLOB (l_clob      IN CLOB,
                                           l_file_dir  IN VARCHAR2,
                                           l_file_name IN VARCHAR2)
IS
  l_file      UTL_FILE.FILE_TYPE;
  l_buffer    VARCHAR2(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_clob_len  INTEGER;
BEGIN
  l_clob_len := DBMS_LOB.getlength(l_clob);

  l_file := UTL_FILE.fopen(l_file_dir, l_file_name, 'w', 32767);

  WHILE l_pos < l_clob_len LOOP
    DBMS_LOB.read(l_clob, l_amount, l_pos, l_buffer);
    UTL_FILE.put(l_file, l_buffer);
    UTL_FILE.fflush(l_file);
    l_pos := l_pos + l_amount;
  END LOOP;

  UTL_FILE.fclose(l_file);

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
       UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;