解决在表中插入中文字符查询时乱码问题

当创建了某张表，向表中插入中文字符后，在查询时插入的中文出现乱码
[oracle@test1 ~]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 27 09:55:58 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table t1(name varchar2(10));
Table created.
SQL> insert into t1 values('啊');
1 row created.
SQL> select * from t1;
NAME
----------
???
原因：系统与数据库字符集不匹配
解决方法：
将系统的字符集调整为和数据库一致
查询数据库字符集
SQL> show parameter nls_lang

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_language                         string      AMERICAN
SQL> col value format a40
SQL> select * from nls_database_parameters;

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

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

20 rows selected.
SQL>
或者
SQL> col value$ format a40
SQL> select name,value$ from props$;

NAME                           VALUE$
------------------------------ ----------------------------------------
DICT.BASE                      2
DEFAULT_TEMP_TABLESPACE        TEMP
DEFAULT_PERMANENT_TABLESPACE   USERS
DEFAULT_TBS_TYPE               SMALLFILE
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK
NLS_CALENDAR                   GREGORIAN

NAME                           VALUE$
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
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

NAME                           VALUE$
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.1.0
GLOBAL_DB_NAME                 ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM
EXPORT_VIEWS_VERSION           8
DBTIMEZONE                     00:00

27 rows selected.

影响oracle数据库字符集最重要的参数是NLS_LANG参数，它的格式如下：
NLS_LANG=language_territory.characterset
它有三个组成部分：语言、地域和字符集
language指定服务器消息语言，territory指定服务器的日期和数字格式，characterset指定字符集

经上所查，数据库的NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
将其添加到系统
$export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
或者
$ echo 'NLS_LANG=AMERICAN_AMERICA.ZHS16GBK' >> ~/.bash_profile
$ source ~/.bash_profile
然后再想表中插入数据就能正常显示了

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25575732/viewspace-702912/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25575732/viewspace-702912/