如何處理Oracle用戶端查詢亂碼問題

1，查Oracle資料庫建立時候的字元集：
Oracle伺服器端執行

SQL> select name, value$ from sys.props$ where name like 'NLS%';

NAME VALUE$
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

NAME VALUE$
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.4.0

20 rows selected.

確認資料庫建立的時候選擇的字元集是 UTF-8
.執行sql ：select userenv('language') from dual; 擷取oracle服務端字元集X

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.UTF8

2. 檢查執行 sqlldr 的用戶端的 NLS_LANG 的設定
Oracle 用戶端執行

[oracle@localhost hx]$ echo $NLS_LANG
AMERICAN_AMERICA.UTF8

3. 盡量讓以上3個步驟檢查出的字元集設定都一致，開始通過 sqlldr 匯入文本，並檢查結果。

通常出現問題的原因，可分為三種：

1. 伺服器指定字元集與客戶字元集不同，而與載入資料字元集一致。

解決方案：對於這種情況，只需要設定用戶端字元集與伺服器端字元集一致就可以了

oracle10g UTF8編碼：AMERICAN_AMERICA.AL32UTF8

GBK編碼：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

用戶端修改為中文是：在linux的終端上設定環境變數
1.LANG=zh_CN
2.NLS_LANG=zhs16gbk

2. 伺服器指定字元集與客戶字元集相同,與載入資料字元集不一致。

解決方案：強制載入資料字元集與伺服器端字元集一致。

3. 伺服器指定字元集與客戶字元集不同,與輸入資料字元集不一致。

對於這種情況，目前為止都還沒有太好的解決方案。

其中有的時候可以嘗試通過 iconv -f utf-8 -t gbk filename
從原字元集 utf-8 轉換成 gbk

修改資料庫端字元集的方法：
　　1.伺服器端字元集的設定和修改:
1.1 建立資料庫的時候直接選擇正確的字元集(顯示漢字的字元集主要有ZHS16CGB231280，US7ASCII，WE8ISO8859P1,ZHS16GBK等)
1.2如果建立的時候沒有選擇字元集,那麼字元集將使用預設的字元集.可以使用如下命令查看資料庫的字元集.用oracle的合法使用者登入.select * from V$NLS_PARAMETERS.
SQL > select * from V$NLS_PARAMETERS
parameter value
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
…. ….
NLS_CHARACTERSET WE8ISO8859P1
NLS_SORT BINARY
NLS_NCHAR_CHARACTERSET WE8ISO8859P1
---- 從上述資訊看出ORACLE 資料庫的字元集為' WE8ISO8859P1'。
可以用update 命令修改資料庫的字元集,但是注意:修改字元集可能會對原有資料造成破壞,修改之前一定要先備份資料庫.命令如下:
使用者sys 以sysdba的身份登入oracle. 字串9
SQL>update props$ set value$=’ZHS16GBK’ where name=’NLS_CHARACTERSET’
如果用戶端的字元集和伺服器端不相同,就必須修改字元集.否則不能正確的顯示漢字.一般建議修改用戶端的字元集.
1.3不同字元集資料庫之間的資料匯入
a）重新安裝資料庫或者是用update命令修改字元集。
b）強行修改oracle資料庫當前字元集。以sysdba的身份登入oracle
SQL > create database character set ZHS16GBK （註：這裡的字元集為匯出資料的字元集）
* create database character set ZHS16GBK
ERROR at line 1:
ORA-01031: insufficient privileges
不用理會這個錯誤，用imp 裝入資料。資料裝完後，重啟動oracle 資料，select * from V$NLS_PARAMETERS 此時，你會發現，資料庫字元集又回到原來的字元集。
c）用第三方工具繞開字元集。如powerbuild的pipeline，delphi的datadump，MS access的資料匯入匯出工具。

用戶端字元集

1、 用戶端字元集含義
用戶端字元集定義了用戶端字元資料的編碼方式，任何發自或發往用戶端的字元資料均使用用戶端定義的字元集編碼,用戶端可以看作是能與資料庫直接連接的各種應用，例如sqlplus,exp/imp等。用戶端字元集是通過設定NLS_LANG參數來設定的。

2、NLS_LANG 參數格式
NLS_LANG= Language_ Territory. Client character se
Language: 顯示oracle訊息、校正、日期命名
Territory ：指定預設日期、數字、貨幣等格式
Client character set ：指定用戶端將使用的字元集
例如： NLS_LANG=AMERICAN_AMERICA.US7ASCII
AMERICAN是語言，AMERICA是地區，US7ASCII是用戶端字元集
3、 用戶端字元集設定方法
1)UNIX 環境
$NLS_LANG=“simplified chinese”_china.zhs16gbk
$export NLS_LANG
編輯oracle使用者的.profile檔案(或.bash_profile linux)
$ export NLS_LANG="SIMPLIFIED CHINESE"_CHINA.UTF8
2)Windows 環境
編輯註冊表
Regedit.exe HKEY_LOCAL_MACHINE---SOFTWARE---ORACLE—HOME0

在DOS名下執行：

Set NLS_LANG=“simplified chinese”_china.zhs16gbk
4、 NLS 參數查詢
Oracle 提供若干NLS參數定製資料庫和使用者機以適應本地格式，例如有NLS_LANGUAGE,NLS_DATE_FORMAT,NLS_CALENDER等，可以通過查詢以下資料字典或v$視圖查看。
NLS_DATABASE_PARAMETERS-- 顯示資料庫當前NLS參數取值，包括資料庫字元集取值
NLS_SESSION_PARAMETERS-- 顯示由NLS_LANG 設定的參數，或經過alter session 改變後的參數值（不包括由NLS_LANG 設定的用戶端字元集）
NLS_INSTANCE_PARAMETE-- 顯示由參數檔案init.ora 定義的參數V$NLS_PARAMETERS--顯示資料庫當前NLS參數取值

圖1-3 查詢NLS參數值

5、 修改NLS參數
使用下列方法可以修改NLS參數
（1）修改執行個體啟動時使用的初始化參數檔案
（2）修改環境變數 NLS_LANG
（3）使用ALTER SESSION語句，在oracle會話中修改
（4）使用某些SQL函數
NLS 作用優先順序別：Sql function>alter session>環境變數或註冊表>參數檔案>資料庫預設

SQL*Loader的字元集轉換

使用SQL*Loader向資料庫裝載資料，有兩種轉換字元集的模式

常規路徑： 資料被轉換為 NLS_LANG 指定的會話字元集或者控制檔案指定的字元集。

控制檔案可以指定字元集

SQL*Loader control file:

LOAD DATA

CHARACTERSET UTF16

INFILE ulcase11.dat

REPLACE …

如果資料檔案的字元集與NLS_LANG環境變數的字元集不一致，SQL*Loader以NLS_LANG指定的字元集編碼存入資料庫。

直接路徑： 資料使用用戶端指示被轉換.

這樣要求資料庫的字元集是資料檔案字元集的超級，否則有可能出現亂碼。

亂碼問題及解決辦法

要在用戶端正確顯示ORACLE資料庫中的漢字資訊，首先必須使用戶端的字元集與伺服器端的字元集一致；其次是載入到ORACLE資料庫的資料字元集必須與伺服器字元集一致。據此，漢字顯示亂碼的問題大致可以分為以下幾種情況：
1、用戶端字元集與伺服器端字元集不同，伺服器端字元集與載入資料字元集一致。

如：

以system使用者登入，建立表，插入資料。

圖1-4 亂碼問題-正確的用戶端字元集

圖1-5 亂碼問題-不一致的用戶端字元集

這種情況是最常見的，只要把用戶端的字元集設定正確即可。具體解決方案：
第一步：查詢V$NLS_DATABASE_PARAMETERS得到服務端的字元集：
SQL>SELECT * FROM V$NLS_DATABASE_PARAMETERS WHERE PARAMETER=’ NLS_CHARACTERSET;
PARAMETER VALUE
-----------------------------------------------------
NLS_CHARACTERSET ZHS16GBK

第二步：根據服務端的字元集設定用戶端的字元集，設定方法參見用戶端的字元集的設定方式。以LINUX系統為例，可在目前使用者的.bash_profile檔案中增加如下兩行：
NLS_LANG=SIMPLIFIED Chinese_CHINA.ZHS16GBK
export NLS_LANG
2、用戶端字元集與伺服器端字元集相同，伺服器端字元集與載入資料字元集不一致。這種情況一般發生在ORACLE版本升級或重新安裝資料庫時選擇了與原來資料庫不同的字元集，而恢複載入的備份資料仍是按原字元集卸出的場合。另一種情況是載入從其它使用不同字元集的ORACLE資料庫卸出的資料。在這兩種情況中，不管用戶端字元集與伺服器端字元集是否一致都無法正確顯示漢字。如：

具體解決方案：
方案一：按服務端字元集的修改方法修改服務端字元集與載入資料字元集一致，然後匯入資料。
方案二：利用資料格式轉儲，避開字元集帶來的問題。即先將載入資料倒入到與其字元集一致的資料庫中，然後再將資料要麼按文字格式設定匯出（資料量較小的情況下），要麼通過第三方工具（如POWER BUILDER，ACCESS，FOXPRO等）倒出資料，最後將倒出的資料匯入到目標資料庫中。
3、用戶端字元集與伺服器端字元集不同，服務端字元集與輸入資料字元集不同。這種情況是在用戶端字元集與伺服器端字元集不一致時，從用戶端輸入了漢字資訊。輸入的這些資訊即便是把用戶端字元集更改正確，也無法顯示漢字。

如：

圖1-6 亂碼問題—錯誤的用戶端字元集

圖1-7 亂碼問題—正確的用戶端字元集

解決方案：修改用戶端字元集與服務端字元集一致後，重新輸入資料。