SQL & PL/SQL

Hello,

I'm trying to export a CLOB field to a txt file. It's a xml string in the CLOB. I have used different methods but the only which i can use is PUT, PUT_LINE.
But:
- PUT does only export the 32565 characters. I see that the output of l_pos is 4202084 but these are not exported. I have tried FFLUSH, but this gives a error or my syntax is not correct.
- PUT_LINE is exporting the complete CLOB, but after every 32656 is new line is added and if I want to look with altove then I have to remove 129 times the new line. Thats not the way I want to go.

Is it an idea, if it is possible, that I write 32565 chars to the buffer (file), read these 32565 from the buffer minus one (=without \r\n), delete the last 32565 chars from buffer and append/put the chars 32565 minus the \r\n to the buffer again. I hope there will be a better idea but....

Has anybody an idea how I can solve this? Or what do I wrong?

Any help would be appreciated.

Beneath my code.

Nico

CREATE OR REPLACE DIRECTORY documents AS 'D:\TEST';
SET SERVEROUTPUT ON

DECLARE
  l_file    UTL_FILE.FILE_TYPE;
  l_clob    CLOB;
  l_buffer  VARCHAR2(32767);
  l_amount  BINARY_INTEGER := 32565;
  l_pos     INTEGER := 1;
  l_lengte  number;
  l_lineCount number := 0;
BEGIN
  SELECT PAYLOAD
  INTO   l_clob
  FROM   icepayloadext ext 
  WHERE  EXT.PAYLOADTYPE in ('POST')
    AND  substr(payload, 0, 2000) like '%181015%'
    AND  ext.LSTIME between to_date('10-10-2012 00:00:00', 'dd-mm-rrrr HH24:mi:ss') and to_date('10-10-2012 17:30:00', 'dd-mm-rrrr HH24:mi:ss');
  
  l_lengte := DBMS_LOB.GETLENGTH (l_clob); 
  DBMS_OUTPUT.PUT_LINE('lengte clob veld: ' || l_lengte);
  
  l_file := UTL_FILE.fopen('DOCUMENTS', 'Sample1.txt', 'w', 32767);

  LOOP
    DBMS_LOB.read (l_clob, l_amount, l_pos, l_buffer);
    UTL_FILE.PUT(l_file, l_buffer);
    UTL_FILE.FFLUSH (l_file);
    --UTL_FILE.PUT_LINE(l_file,'alles geschreven');
    l_pos := l_pos + l_amount;
    
    DBMS_OUTPUT.PUT_LINE('Offset position: ' || l_pos);
    
    if (l_pos > l_lengte) then 
        exit;
    end if;
    
  END LOOP;
  
  UTL_FILE.fclose(l_file);
--EXCEPTION
  --WHEN OTHERS THEN
  --  DBMS_OUTPUT.put_line(SQLERRM);
    --UTL_FILE.fclose(l_file);
END;
/