How do I change the character set of an XMLTYPE variable?
I currently have a non-utf-8 DB, but I need to create a utf-8 encoded XMLType variable. I have a workaround, but there seems to be a bug in Oracle, see the following link: https://forums.oracle.com/forums/thread.jspa?messageID=10238641
... and Oracle support error: 7698684
The error causes a random
ORA-1482: unsupported character set
ORA-6512: at "SYS.XMLTYPE", line 107
First of all, I am getting XMLType with dbms_xmlgen package. This XMLType is encoded using the DB character set.
To convert it to utf-8 character set I like the following:
- I am converting an XMLType variable to a BLOB variable using the getBlobVal method using NLS_CHARSET_ID ('UTF8') as a parameter
- I am converting BLOB variable back to XMLType using XMLType constructor method using BLOB variable as first parameter and NLS_CHARSET_ID ('UTF8') as second parameter. This results in a random error :(
Does anyone know of an 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);
source to share
I managed to do this using the convert function. Failed to transform the entire XML document (even its value), but only the element values.
This doesn't work (XMLType constructor doesn't work):
l_xml := XMLType(convert(l_xml.getClobVal, 'UTF8'));
So I had to put the transformation in 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 that reads a dictionary and loops through all the columns of a given table / view and generates a select statement line where all columns are converted separately to UTF8. This string can then be passed as a parameter to the dbms_xmlgen.newContext function.
source to share