CODEWITHSUNDEEP
xmldatabaseoracle-databaseplsql
WORK
WORK

Reputation: 29

ORA-06502: PL/SQL: numeric or value error when trying to save XML into file

I'm trying to save XMLTYPE data that created using function GENERATE_XML_FROM_TABLE into file. When I try to save little number of characters it's all working. But for bigger sizes it's causes error " numeric or value error when trying to save XML into file".

    PROCEDURE SAVE_XML_TO_FILE (TARGET_TABLE VARCHAR2)
IS
   FILE_TO_SAVE   UTL_FILE.FILE_TYPE;
   XMLCLOB        CLOB;
BEGIN
   XMLCLOB := GENERATE_XML_FROM_TABLE (TARGET_TABLE).GETCLOBVAL ();
   FILE_TO_SAVE := UTL_FILE.FOPEN ('DATA', 'CLASSIF.xml', 'W');
   UTL_FILE.PUT (FILE_TO_SAVE, XMLCLOB);
   UTL_FILE.FCLOSE (FILE_TO_SAVE);
END;

Upvotes: 0

Views: 1823

Answers (1)

Alex Poole
Alex Poole

Reputation: 191520

The maximum size of the buffer for put() is 32767, unless a smaller size is specified in fopen(); and the default max_line_size for fopen() is 1024 characters.

Essentially you need to write the CLOB out in chunks, and you might as well use larger buffers:

PROCEDURE SAVE_XML_TO_FILE (TARGET_TABLE VARCHAR2)
IS
   FILE_TO_SAVE   UTL_FILE.FILE_TYPE;
   XMLCLOB        CLOB;
   POSITION       PLS_INTEGER := 1;
   CHARS          PLS_INTEGER := 32767;
   BUFFER         VARCHAR2(32767);
BEGIN
   XMLCLOB := GENERATE_XML_FROM_TABLE (TARGET_TABLE).GETCLOBVAL ();

   FILE_TO_SAVE := UTL_FILE.FOPEN ('DATA', 'CLASSIF.xml', 'W', CHARS);

   WHILE POSITION < DBMS_LOB.GETLENGTH (XMLCLOB) LOOP
      DBMS_LOB.READ (XMLCLOB, CHARS, POSITION, BUFFER);
      UTL_FILE.PUT (FILE_TO_SAVE, BUFFER);
      UTL_FILE.FFLUSH (FILE_TO_SAVE);
      POSITION := POSITION + CHARS;
   END LOOP;

   UTL_FILE.FCLOSE (FILE_TO_SAVE);
END;
/

I've added CHARS to the ``fopen()` call, so that will be 32767. Then I'm reading the CLOB into the buffer in chunks of up to 32767 characters, and writing and - importantly - flushing each buffer.

You'll still have a problem if the XML in the CLOB has no line breaks, but hopefully it's prettified; if not you can use XMLSerialise to achieve that before writing.

Upvotes: 1

Related Questions