环境变量导致查询结果不一致

 

同事刚刚安装了11G的客户端,用PL SQL DEVELOPER连到到数据库中查询结果缺和另一个同事结果不一致。

另一个同事查询如下的SQL是有记录的：

SQL>  select dbcode from dborg where orgtype='2' and orgname like'%北京%';

dbcode
--------------
A010100

而这个同事查询结果缺没有这条记录：

SQL> select dbcode from dborg where orgtype='2' and orgname like'%北京%';

no rows selected

SQL>

刚开始还以为是数据库 BUG之类的错误。

后来想了想可能是环境变量的问题导致解析不了，中文“北京”2个字体。

 

试验了一下果然如此：

 

C:\>SET NLS_LANG=american_america.ZHS16GBK

C:\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 29 16:47:31 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> select dbcode from dborg where orgtype='2' and orgname like'%北京%';

dbcode
--------------
A010100

C:\>SET NLS_LANG=american_CHINA.UTF8

C:\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 29 16:46:44 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> select dbcode from dborg where orgtype='2' and orgname like'%北京%';

no rows selected

SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options