How to change character set of the XMLTYPE variable?

oracle plsql blob xmltype
Jokke Heikkilä picture Jokke Heikkilä · Mar 28, 2012 · Viewed 24.5k times · Source

I'm currently having non-utf-8 DB but I need to produce XMLType variable with utf-8 encoding. I'm having a workaround but there seems to be bug in the Oracle, see the following link: https://forums.oracle.com/forums/thread.jspa?messageID=10238641

...and Oracle Support bug: 7698684

The bug causes random 

ORA-1482: unsupported character set
ORA-6512: at "SYS.XMLTYPE", line 107

First of all I'm getting XMLType with dbms_xmlgen package. That XMLType is encoded with DB character set.

To convert it to utf-8 character set I do like this:

  • I convert XMLType variable to BLOB variable with getBlobVal method using NLS_CHARSET_ID ('UTF8') as parameter
  • I convert BLOB variable back to XMLType with XMLType constructor method using BLOB variable as first parameter and NLS_CHARSET_ID ('UTF8') as second parameter. This causes random error :(

Does anybody know any alternative solution for this?

  l_xml   := dbms_xmlgen.getXMLType(l_ctx);
  l_xml_b := l_xml.getBlobVal(C_UTF8_CHARSET_ID);
  l_xml   := XMLType(l_xml_b, C_UTF8_CHARSET_ID);

Answer

Jokke Heikkilä picture Jokke Heikkilä · Apr 4, 2012

I managed to do this with convert function. It was not possible to convert the whole xml document (even the clob value of it) but only element values.

This was not working (XMLType constructor fails):

l_xml := XMLType(convert(l_xml.getClobVal, 'UTF8'));

So I had to put convert to the query string (this is just an example):

select dbms_xmlgen.getXMLType(
  q'{select convert('ä', 'UTF8') myValue from dual}')
from dual

Finally I made a function which reads dictionary and loops through all columns of the given table/view and generates select statement string where all columns are converted separately to UTF8. This string can then be passed as parameter to the dbms_xmlgen.newContext function.

Related questions

SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end... undefine numero set serveroutput on accept numero …

Read More