使用 NLS_CHARACTERSET 时的 Oracle Unicode 问题是 WE8ISO8859P1 和 NLS_NCHAR_CHARACTERSET 是 AL16UTF16，而 ColdFusion 作为编程语言
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5086773/
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
Oracle Unicode problem when using NLS_CHARACTERSET is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is AL16UTF16, and ColdFusion as programming language
提问by tsurahman
I have 2 Oracle 10g database, XE and Enterprise
我有 2 个 Oracle 10g 数据库，XE 和 Enterprise
XE
XE
Enterprise
企业
and this are the data type I've use in the test table
这是我在测试表中使用的数据类型
and then I tried to test to insert some Unicode char from http://www.sustainablegis.com/unicode/
然后我尝试测试从http://www.sustainablegis.com/unicode/插入一些 Unicode 字符
and the results are
结果是
XE
XE
Enterprise
企业
for this test, I use ColdFusion 9 developer edition
对于这个测试，我使用 ColdFusion 9 开发者版
<cfprocessingDirective pageencoding="utf-8">
<cfset setEncoding("form","utf-8")>
<form action="" method="post">
Unicode : <br>
<textarea name="txaUnicode" id="txaUnicode" cols="50" rows="10"></textarea>
<br><br>
Language : <br>
<input type="Text" name="txtLanguage" id="txtLanguage">
<br><br>
<input type="Submit">
</form>
<cfset dsn = "theDSN">
<cfif StructKeyExists(FORM, "FIELDNAMES")>
<cfquery name="qryInsert" datasource="#dsn#">
INSERT INTO UNICODE
(
C_VARCHAR2,
C_CHAR,
C_CLOB,
C_NVARCHAR2,
LANGUAGE
)
VALUES
(
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.TXAUNICODE#">,
<cfqueryparam cfsqltype="CF_SQL_CHAR" value="#FORM.TXAUNICODE#">,
<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#FORM.TXAUNICODE#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.TXAUNICODE#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.TXTLANGUAGE#">
)
</cfquery>
</cfif>
<cfquery name="qryUnicode" datasource="#dsn#">
SELECT *
FROM UNICODE
ORDER BY LANGUAGE
</cfquery>
<table border="1">
<thead>
<tr>
<th>LANGUAGE</th>
<th>C_VARCHAR2</th>
<th>C_CHAR</th>
<th>C_CLOB</th>
<th>C_NVARCHAR2</th>
</tr>
</thead>
<tbody>
<cfoutput query="qryUnicode">
<tr>
<td>#qryUnicode.LANGUAGE#</td>
<td>#qryUnicode.C_VARCHAR2#</td>
<td>#qryUnicode.C_CHAR#</td>
<td>#qryUnicode.C_CLOB#</td>
<td>#qryUnicode.C_NVARCHAR2#</td>
</tr>
</cfoutput>
</tbody>
</table>
from this guide http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10749/ch6unicode.htm#i1007297I think for my Enterprise database it should produce same thing as XE (at least for NVARCHAR2 column) since the typical solutionfrom that guide said:
从本指南http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10749/ch6unicode.htm#i1007297我认为对于我的企业数据库它应该产生与 XE 相同的东西（至少对于 NVARCHAR2 列），因为该指南中的典型解决方案说：
- Use NCHAR and NVARCHAR2 datatypes to store Unicode characters
- Keep WE8ISO8859P1 as the database character set
- Use AL16UTF16 as the national character set
- 使用 NCHAR 和 NVARCHAR2 数据类型存储 Unicode 字符
- 保留 WE8ISO8859P1 作为数据库字符集
- 使用 AL16UTF16 作为国家字符集
So, how to make it works too in my Enterprise database?
那么，如何让它在我的企业数据库中也能正常工作呢？
Thank you :)
谢谢 ：）
回答by el vis
First database stores values in utf-8 encoding, second in iso-8859-1(besides N-datatypes), however you are writing out both values in utf-8 so first one is alright, but second one is wrong decoded.
第一个数据库以 utf-8 编码存储值，第二个存储在 iso-8859-1（除了 N 数据类型），但是您在 utf-8 中写出两个值，所以第一个没问题，但第二个解码错误。
回答by Salman Hameed
WE8ISO88591 encoding has a limited character set and can not store all unicode characters. Please refer to http://en.wikipedia.org/wiki/ISO/IEC_8859-1for list of supported characters.
WE8ISO88591 编码的字符集有限，无法存储所有 unicode 字符。有关支持的字符列表，请参阅http://en.wikipedia.org/wiki/ISO/IEC_8859-1。
回答by FerranB
The first step is to check the NLS environment variables because they determine the conversion (if needed). Also check the contents for NLS_SESSION_PARAMETERSinside a coldfussion page .
第一步是检查 NLS 环境变量，因为它们决定了转换（如果需要）。另外，检查内容为NLS_SESSION_PARAMETERS一个coldfussion页面内。
回答by schlenk
The NVARCHAR2 thing combined with WE8ISO88591 has a nasty side effect if you use literals in queries. The whole query is converted through the NLS_CHARSET encoding, so if it cannot encode your characters you might be in trouble. But that should not happen with bound paramters.
如果您在查询中使用文字，那么与 WE8ISO88591 结合的 NVARCHAR2 事物会产生令人讨厌的副作用。整个查询是通过 NLS_CHARSET 编码转换的，因此如果它无法对您的字符进行编码，您可能会遇到麻烦。但这不应该发生在绑定参数上。
