Failed to write large data using UTL_FILE.PUT_LINE

Question

I created an xml that contains a lot of data. Now I'm trying to write the created xml to a file.

Declaration:

prodFeed_file  UTL_FILE.FILE_TYPE;
prodFeed_file := UTL_FILE.FOPEN ('CSV_DIR', 'feed.xml', 'w', 32767); 

LETTER TO FILE:

UTL_FILE.PUT_LINE(prodFeed_file,l_xmltype.getClobVal);

UTL_FILE.FCLOSE(prodFeed_file);

If it l_xmltype.getClobValreturns a limited record, then this is a working file, but if the size l_xmltype.getClobValexceeds the size (almost 35 KB), it gives an error:

ORA-06502: PL/SQL: numeric or value error

Answer 1

The UTL_FILE documentation says :

"The maximum size of a buffer parameter is 32,767 bytes unless you specify a smaller size in FOPEN."

You will need to write a piece of CLOB with a piece. Something like that:

DECLARE
  v_clob CLOB;
  v_clob_length INTEGER;
  pos INTEGER := 1;
  buffer VARCHAR2(32767);
  amount BINARY_INTEGER := 32760;
  prodFeed_file utl_file.file_type;
BEGIN
  prodFeed_file := UTL_FILE.FOPEN ('CSV_DIR', 'productFeedLargo.xml', 'w', 32767);
  v_clob := l_xmltype.getClobVal;
  v_clob_length := length(v_clob);

  WHILE pos < v_clob_length LOOP
    dbms_lob.read(v_clob, amount, pos, buffer);
    utl_file.put(prodFeed_file , char_buffer);
    utl_file.fflush(prodFeed_file);
    pos := pos + amount;
  END LOOP;

  utl_file.fclose(prodFeed_file);

END;
/

Answer 2

32767, " ", : , .

, newline. .