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

Hi all,

i am trying to create an XML-File from within PL/SQL but there is a big problem when
filesize begins to grow.

Using dbms_xmldom.writeToFile(doc, temp_filename)
(Version with 2 parameters) the file is created without any problems (370kB) but when
a 3rd parameter is specified [dbms_xmldom.writeToFile(doc, temp_filename, 'UTF-8')]
oracle raises exception
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
A creation of an small XML-file (130 Bytes) works fine with the 3rd parameter specified.

Any ideas?

sahl04Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

schwertnerCommented:
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.
0
paquicubaCommented:
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
0
sahl04Author Commented:
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);
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

schwertnerCommented:
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.
0
sahl04Author Commented:
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
0
paquicubaCommented:
Have you tried changing your client's NLS_LANG to UTF8

WE8ISO8859P15 is a single-byte encoding
UTF8 is a muti-byte encoding
0
sahl04Author Commented:
Yes, i changed the registry setting and called the procedure from sqlplus.
No changes in bahaviour. Still not UTF8.

0
schwertnerCommented:
Did you read what I wrote?
0
sahl04Author Commented:
@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.
0
schwertnerCommented:
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".

0
paquicubaCommented:
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));
0
schwertnerCommented:
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!
 
0
sahl04Author Commented:
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;
0
schwertnerCommented:
Go to the Helpdesk and ask for refunding of your points.
0
DarthModCommented:
PAQed with no points refunded (of 500)

DarthMod
Community Support Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.