title: Write a CLOB to File in Oracle
body: <p>my student_t1 table consists of a blob column called image. I wrote a base64 function that correctly converts the BLOB into a CLOB. Now, I need to write this CLOB to my <code>IO_DIR</code> on my server <code>/u01/app/oracle/io_dir</code>. </p>
  
  <p>When the following PL/SQL code is executed, the CLOB gets written to the directory as a text file. I believe the entire CLOB is not outputted though because when I try to decode the image is corrupted (only the upper portion of the image comes in). I ran it on a small image (11k) and it worked fine. I also monitored that l_pos is increasing correctly so it seems like it is reading each chunk of the CLOB (32kB) What am I doing wrong? </p>
  
  <p>I am using Oracle 11g Express Edition (XE) and SQL Developer. Here is the error and the code</p>
  
  <pre><code>create or replace PROCEDURE c2F
   IS
     p_filename       VARCHAR2(100);
     p_dir            VARCHAR2(100) := 'IO_DIR';
     c_amount         CONSTANT BINARY_INTEGER := 32767;
     l_buffer         VARCHAR2(32767);
     l_chr10          PLS_INTEGER;
     l_clobLen        PLS_INTEGER;
     l_fHandler       sys.UTL_FILE.FILE_TYPE;
     l_pos            PLS_INTEGER    := 1;
     v_blob           BLOB;
     p_clob           CLOB;
  
   BEGIN
    dbms_output.put_line('Start Time: ' || TO_CHAR(sysdate,('YYYY/MM/DD hh24:Mi:ssss')));
  
    FOR i IN
      (SELECT student_no,
              image v_blob,
              encdec_base64.encode_base64(image) p_clob 
      FROM student_t1
      WHERE student_no =200601022
    ) loop
  
     IF (dbms_lob.isopen(i.p_clob) = 0) THEN
        dbms_lob.open(i.p_clob,dbms_lob.lob_readonly);
      END IF;
     l_pos      := 1;
     p_filename := i.student_no || '.txt';
     l_clobLen  := DBMS_LOB.GETLENGTH(i.p_clob);
     l_fHandler := sys.UTL_FILE.FOPEN(p_dir, p_fileName,'WB',c_amount);
     l_buffer := DBMS_LOB.SUBSTR(i.p_clob, c_amount, l_pos);
  
    WHILE l_pos &lt; l_clobLen LOOP
       l_buffer := DBMS_LOB.SUBSTR(i.p_clob, c_amount, l_pos);
  
       EXIT WHEN l_buffer IS NULL;
       UTL_FILE.put_raw(l_fHandler,utl_raw.cast_to_raw(l_buffer));
       l_pos := l_pos + LEAST(LENGTH(l_buffer)+1,c_amount);
       UTL_FILE.FFLUSH(l_fHandler);
       END LOOP;
    END LOOP;
     sys.UTL_FILE.FCLOSE(l_fHandler);
  
   EXCEPTION
    WHEN OTHERS THEN
     IF UTL_FILE.IS_OPEN(l_fHandler) THEN
     UTL_FILE.FCLOSE(l_fHandler);
     END IF;
     RAISE;
  dbms_output.put_line('End Time: ' || TO_CHAR(sysdate,('YYYY/MM/DD hh24:Mi:ssss')));
   END; 
  </code></pre>