dbms_xmldom.writeToFile() raises ORA-06502 when specifying 'UTF-8' as 3rd parameter

This is so because it seems there are used PL/SQL, variables of type LONG, CHAR, VARCHAR2, and VARCHAR and they are are   limited to 32K.

Alternatively you can use CLOB to store the result and after that to read and to store the CLOB in a file in smaller chunks.

Run these queries and show us the results:

select * from nls_session_parameters
/

select * from nls_database_parameters
/


Check your machine registry:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEx      NLS_LANG

Change your NLS_LANG to AMERICAN_AMERICA.UTF8  and try your PL/SQL

Already tried out following workaround but result is NOT in UTF-8 character set!!!!

dbms_xmldom.writeToClob(doc, xmlclob, 'UTF-8');
write_clob_to_fs(xmlclob, v_file_location, temp_filename);

The dbms_xmldom supports text nodes of 64K only. However, you can use the dom method appendData to create a text node and append chunks of data > 32K to the text node. All you need to do is to read CLOB >32k into chunks of 32k varchar2 and append it to the text node.

1. loop
2. Use dbms_lob.read(clob, 32K, buffer)
3. if this is the first chunk
create textNode from buffer and cast to DOMCharacterData
4. if this is not the first chunk
appendData(buffer) to DOMCharacterData
5. exit loop when no data found
6. cast DOMCharacterData to domNode
7. appendChild domNode to the domDocument
This works fine and unfortunatelly the limit is indeed 64K.

Result from select * from nls_session_parameters is

NLS_LANGUAGE                        GERMAN
NLS_TERRITORY                       GERMANY
NLS_CURRENCY                        €
NLS_ISO_CURRENCY                    GERMANY
NLS_NUMERIC_CHARACTERS              ,.
NLS_CALENDAR                        GREGORIAN
NLS_DATE_FORMAT                     DD/MM/RRRR
NLS_DATE_LANGUAGE                   GERMAN
NLS_SORT                            GERMAN
NLS_TIME_FORMAT                     HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT                DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT                  HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT             DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY                   €
NLS_COMP                            BINARY
NLS_LENGTH_SEMANTICS                BYTE
NLS_NCHAR_CONV_EXCP                 FALSE


Result from select * from nls_database_parameters is

NLS_LANGUAGE                        AMERICAN
NLS_TERRITORY                       AMERICA
NLS_CURRENCY                        $
NLS_ISO_CURRENCY                    AMERICA
NLS_NUMERIC_CHARACTERS              .,
NLS_CHARACTERSET                    WE8ISO8859P15
NLS_CALENDAR                        GREGORIAN
NLS_DATE_FORMAT                     DD-MON-RR
NLS_DATE_LANGUAGE                   AMERICAN
NLS_SORT                            BINARY
NLS_TIME_FORMAT                     HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                  HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT             DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                   $
NLS_COMP                            BINARY
NLS_LENGTH_SEMANTICS                BYTE
NLS_NCHAR_CONV_EXCP                 FALSE
NLS_NCHAR_CHARACTERSET              UTF8
NLS_RDBMS_VERSION                   9.2.0.6.0

Have you tried changing your client's NLS_LANG to UTF8

WE8ISO8859P15 is a single-byte encoding
UTF8 is a muti-byte encoding

Yes, i changed the registry setting and called the procedure from sqlplus.
No changes in bahaviour. Still not UTF8.

Did you read what I wrote?

@schwertner

If the 64kB boundary is the problem why does
    dbms_xmldom.writeToFile(doc, temp_filename)
work fine
and
    dbms_xmldom.writeToFile(doc, temp_filename, 'UTF-8')
does not?
The doc has 370kb size in both cases.

May be it internally divides data in chunks.
But when you set the parameter 'UTF-8'
instead of (let us say 32K chunks) practically you get more then 32 bytes.

I have experienced this many times using DBMS_OUTPUT which has limit to 255 characters for a line. Only working in smaller chunks can help when one uses UTF8. Be aware that every oracle package comprises many PL/SQL pieces of code. Many of them do not work correct when one uses UTF8 because they are intended to work in the model "1 character = 1 Byte". But UTF8 uses the model "1 character = 1-4 bytes".

schwertner is correct, dbms_xmldom package is bound to the UTL_FILE package limitations, such as UTL_FILE has a 32k line length limit.

Have you tried using NLS_LENGTH_SEMANTICS initialization parameter in your init.ora to be able to treat characters as characters when using unicode, not characters as bytes.

i.e.

CREATE TABLE char_col (a VARCHAR2(10 CHAR));

not

CREATE TABLE char_col (a VARCHAR2(10 BYTE));

As everybody can see Herr Sahl04 works in Germany!
Be aware that "die Umlauts" und "Scharfes S" are coded in 2 bytes in Unicode.
I am working for German company and "kann Deutsch sehr gut sprechen", so I know this for sure!
 

I found the following workaround for the problem.
First writing the document to an clob [dbms_xmldom.writeToClob(doc, xmlclob)]
and then write the clob to an file using the following procedure.
utl_file.fopen_nchar(), utl_file.put_nchar() and the buffer of type NVARCHAR2 are
the keys for the solution.

    PROCEDURE clob_to_utf8_file
        (
            i_dir  IN varchar2,
            i_file IN varchar2,
            i_clob IN clob
        )
    IS
        v_output utl_file.file_type;
        v_amt    NUMBER DEFAULT 16384;
        v_offset NUMBER DEFAULT 1;
        v_length NUMBER DEFAULT nvl(dbms_lob.getlength(i_clob),0);
        v_buffer NVARCHAR2(32760);
    BEGIN
        v_output := utl_file.fopen_nchar(i_dir, i_file, 'w', 32760);
        WHILE(v_offset < v_length)
        LOOP
            dbms_lob.read(i_clob, v_amt, v_offset, v_buffer);
            utl_file.put_nchar(v_output, v_buffer);
            utl_file.fflush(v_output);
            v_offset := v_offset + v_amt;
        END LOOP;

       utl_file.fclose(v_output);
    END;

Go to the Helpdesk and ask for refunding of your points.