6

I created a xml which includes a big amount of data. Now I am trying to write that generated xml into a file.

Declaration:

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

WRITING INTO FILE: 

UTL_FILE.PUT_LINE(prodFeed_file,l_xmltype.getClobVal);

UTL_FILE.FCLOSE(prodFeed_file);

If l_xmltype.getClobVal returns limited record then it is working file but if the l_xmltype.getClobVal exceeds the size(almost 35 KB) it is giving an error:

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

Ram Dutt Shukla
  • 1,351
  • 7
  • 28
  • 55

1 Answers1

13

The UTL_FILE documentation says:

"The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. "

You will have to write the CLOB chunk by chunk. Something like this:

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;
/
APC
  • 144,005
  • 19
  • 170
  • 281
pablomatico
  • 2,222
  • 20
  • 25
  • 2
    I posted an answer which just linked to the doc. But as you've posted a workaround I've deleted my answer and added the link to yours. – APC Mar 26 '14 at 07:34
  • Hi, would you please help me with http://stackoverflow.com/questions/25279365/utl-file-write-error-when-calling-utl-file-put-in-a-loop? – JaskeyLam Aug 13 '14 at 06:44