title: How to create a complete text file from clob column in table
body: <p>I have the problem that when I create the text file it only saves the first 32768 bytes but the file is larger and the rest of the information does not appear in the file.</p>
  <p>This is the code in plsql that I am using, I think I am missing something so that the rest of the information is saved in the file.</p>
  <p>The generated file</p>
  <p><a href="https://i.stack.imgur.com/ycgtl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ycgtl.png" alt="enter image description here" /></a></p>
  <pre><code>create table tab1 (
    col1 clob
  );
  </code></pre>
  <pre><code>CREATE OR REPLACE DIRECTORY DOCUMENTS AS '/process/files';
  </code></pre>
  <pre><code>SET SERVEROUTPUT ON
  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;
    UTL_FILE.fclose(l_file);
    
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line(SQLERRM);
      UTL_FILE.fclose(l_file);
  END;
  /
  </code></pre>