将国家字符插入oracle NCHAR或NVARCHAR列不起作用 [英] Inserting national characters into an oracle NCHAR or NVARCHAR column does not work
问题描述
在oracle数据库中插入字符串时，一些国家字符会被问号替换，即使它们是在NCHAR或NVARCHAR列中插入的
- 应该能够处理所有Unicode字符。
When inserting strings in an oracle database, some national characters are replaced with question marks, even though they are inserted in an NCHAR or NVARCHAR column - that should be able to handle all Unicode characters.
使用Oracle的SQL Developer，sqlplus或使用JDBC驱动程序会发生这种情况。
This happens using either Oracle's SQL Developer, sqlplus or using the JDBC driver.
数据库NLS_CHARACTERSET设置为WE8ISO8859P1（西部欧洲iso-8859-1）
用于NCHAR列的NLS_NCHAR_CHARACTERSET设置为AL16UTF16。 （UTF-16）
The database NLS_CHARACTERSET is set to WE8ISO8859P1 (western european iso-8859-1) The NLS_NCHAR_CHARACTERSET used for NCHAR columns is set to AL16UTF16. (UTF-16)
任何不在NLS_CHARACTERSET中的字符似乎都被替换为倒置的问号。
Any character not in the NLS_CHARACTERSET seems to be replaced with a inverted question mark.
推荐答案
编辑：请注意，在Oracle上处理UTF的最佳方法是使用数据库字符集AL32UTF8创建数据库，并使用普通的varchar2列。使用nchar列的一个问题是，当默认情况下将参数作为nchar发送时，oracle不能使用普通char / varchar2列的索引。
Note that the best way to handle UTF on Oracle is to create the database using the database character set AL32UTF8, and use ordinary varchar2 columns. One of the problems with using nchar columns is that oracle can't use indexes for ordinary char/varchar2 columns when arguments are sent as nchar by default.
无论如何：如果你无法转换数据库：
Anyway: If you can't convert the database:
首先，unicode文字需要以'n'为前缀，如下所示：
First, unicode literals needs to be prefixed with an 'n', like this:
select n'Language - Språk - Język' from dual;
*）8位编码无法处理此文本
*) 8-bit encodings can't handle this text
不幸的是，这还不够。
Unfortunately, that is not enough.
由于某种原因，数据库客户端的默认行为是将所有字符串文字转换为数据库字符集，
意味着值甚至会在数据库可以看到字符串。
For some reason, the default behaviour for database clients is to translate all string literals to the database character set, meaning that values will be changed even before the database gets to see the string.
客户端需要一些配置才能将unicode字符插入NCHAR或NVARCHAR列：
The clients need some configuration in order to be able to insert a unicode character into an NCHAR or NVARCHAR column:
Unix上的SQL Plus
这些environemnet变量设置unix环境，sqlplus设置为使用UTF-8文件，
并配置sqlplus以在unicode中发送字符串文字。
These environemnet variables sets up the unix environment and sqlplus to use UTF-8 files, and also configure sqlplus to send string literals in unicode.
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
LC_CTYPE="en_US.UTF-8"
ORA_NCHAR_LITERAL_REPLACE=true
（en_US.UTF-8适用于Solaris - Linux或其他系统可能需要不同的字符串，使用 locale -a 列出支持的语言环境。）
(en_US.UTF-8 is for Solaris - Linux or other systems may need different strings, use locale -a to list supported locales.)
JDBC驱动程序
使用Oracles JDBC驱动程序的应用程序需要定义以下系统属性以在unicode中发送字符串文字。
Applications using Oracles JDBC driver needs to have the following system property defined to send strings literals in unicode.
-Doracle.jdbc.defaultNChar=true
-Doracle.jdbc.convertNcharLiterals=true
SQL Developer
找到sqldeveloper.conf，并添加以下行：
Locate sqldeveloper.conf, and add the following lines:
AddVMOption -Doracle.jdbc.defaultNChar=true
AddVMOption -Doracle.jdbc.convertNcharLiterals=true
Microsoft Windows上的SQL Plus
如果在Microso上使用SQLplus，我还没试过ft Windows或Toad完全处理utf-8。
Sqlplusw.exe可以这样做，并且以下注册表设置可以解决这个问题。
I haven't tried if SQLplus on Microsoft Windows or Toad handles utf-8 at all. Sqlplusw.exe may do that, and the following registry settings may do the trick.
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
ORA_NCHAR_LITERAL_REPLACE=true
这篇关于将国家字符插入oracle NCHAR或NVARCHAR列不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
