?
Solved

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

Posted on 2005-03-21
17
Medium Priority
?
2,835 Views
Last Modified: 2012-05-05
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?

0
Comment
Question by:sahl04
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +1
17 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 13591780
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 13592033
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
 

Author Comment

by:sahl04
ID: 13592037
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 48

Expert Comment

by:schwertner
ID: 13592109
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
 

Author Comment

by:sahl04
ID: 13592224
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 13592665
Have you tried changing your client's NLS_LANG to UTF8

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

Author Comment

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

0
 
LVL 48

Expert Comment

by:schwertner
ID: 13598732
Did you read what I wrote?
0
 

Author Comment

by:sahl04
ID: 13599061
@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
 
LVL 48

Expert Comment

by:schwertner
ID: 13599135
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 13612052
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
 
LVL 48

Expert Comment

by:schwertner
ID: 13612652
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
 

Author Comment

by:sahl04
ID: 13651747
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
 
LVL 48

Expert Comment

by:schwertner
ID: 13659302
Go to the Helpdesk and ask for refunding of your points.
0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
ID: 14493674
PAQed with no points refunded (of 500)

DarthMod
Community Support Moderator
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question