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:

... 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

1 answer

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.



All Articles