oracle 如何更改 XMLTYPE 变量的字符集？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9907001/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to change character set of the XMLTYPE variable?
提问by Jokke Heikkil?
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
我目前拥有非 utf-8 数据库，但我需要使用 utf-8 编码生成 XMLType 变量。我有一个解决方法，但 Oracle 中似乎存在错误，请参阅以下链接：https: //forums.oracle.com/forums/thread.jspa?messageID=10238641
...and Oracle Support bug: 7698684
...和 Oracle 支持错误：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.
首先，我使用 dbms_xmlgen 包获取 XMLType。该 XMLType 使用 DB 字符集进行编码。
To convert it to utf-8 character set I do like this:
要将其转换为 utf-8 字符集，我这样做：
- 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 :(
- 我使用 NLS_CHARSET_ID ('UTF8') 作为参数使用 getBlobVal 方法将 XMLType 变量转换为 BLOB 变量
- 我使用 BLOB 变量作为第一个参数和 NLS_CHARSET_ID ('UTF8') 作为第二个参数使用 XMLType 构造函数方法将 BLOB 变量转换回 XMLType。这会导致随机错误:(
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);
采纳答案by Jokke Heikkil?
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.
我设法用转换功能做到了这一点。不可能转换整个 xml 文档（甚至是它的 clob 值），而只能转换元素值。
This was not working (XMLType constructor fails):
这不起作用（XMLType 构造函数失败）：
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.
最后我做了一个函数，它读取字典并循环遍历给定表/视图的所有列，并生成选择语句字符串，其中所有列都分别转换为 UTF8。然后可以将此字符串作为参数传递给 dbms_xmlgen.newContext 函数。
