bj-jn

8 积分	1 好友	20 主题

发消息

[升级] sqlplus下执行select语句报ora-01008

1^#

发表于 2013-10-5 16:24:41 | 查看: 4602| 回复: 7

环境描述：OS/win7(64bit)    DB/10.2.0.1.0(32bit)
故障描述：在sqlplus环境下执行一条带有绑定变量的sql语句报ora-01008错误
操作步骤：
SQL> variable n number
SQL> exec :n := 7521

PL/SQL procedure successfully completed.

SQL> print :n

      N
----------
   7521

SQL> select * from emp where empno = :n;
select * from emp where empno = :n
            *
ERROR at line 1:
ORA-01008: not all variables bound

但是执行如下sql却不报错
SQL>  select * from emp where empno = (select :n from dual);

   EMPNO ENAME
---------- --------------------
   7521 WARD

1 row selected.

疑问：请问这个是bug吗还是其他原因，感谢！

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2013-10-5 19:02:42

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
      14

SQL>  variable n number
SQL> exec :n := 7521

PL/SQL procedure successfully completed.

SQL>  print :n

      N
----------
   7521

SQL>  select * from scott.emp where empno = :n;

   EMPNO ENAME    JOB             MGR HIREDATE       SAL    COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
   7521 WARD    SALESMAN       7698 22-FEB-81    1250       500
      30

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

10.2.0.5 没有该问题

回复只看该作者道具举报

Maclean Liu(刘相兵

3^#

发表于 2013-10-5 19:11:10

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0    Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>
SQL>
SQL> select count(*) from scott.emp;

  COUNT(*)
----------
      14

SQL> variable n number
SQL>  exec :n := 7521

PL/SQL procedure successfully completed.

SQL>  print :n

      N
----------
   7521

SQL> select * from scott.emp where empno = :n;

   EMPNO ENAME    JOB             MGR HIREDATE       SAL    COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
   7521 WARD    SALESMAN       7698 22-FEB-81    1250       500
      30

linux + 10.2.0.1 也没有该问题，怀疑为 bug 或者你的某些配置造成了该问题

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2013-10-5 19:16:24

找到问题了

export NLS_LANG="SIMPLIFIED CHINESE_CHINA".ZHS16GBK

SQL> variable n number
SQL>  exec :n := 7521

PL/SQL procedure successfully completed.

SQL>  print :n

      N
----------
   7521

SQL>  select * from scott.emp where empno = :n;
select * from scott.emp where empno = :n
                  *
ERROR at line 1:
ORA-01008: not all variables bound

[oracle@vrh8 ~]$ unset NLS_LANG
[oracle@vrh8 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Oct 5 19:15:41 2013

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> variable n number
SQL>  exec :n := 7521

PL/SQL procedure successfully completed.

SQL>  print :n

      N
----------
   7521

SQL>  select * from scott.emp where empno = :n;

   EMPNO ENAME    JOB             MGR HIREDATE       SAL    COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
   7521 WARD    SALESMAN       7698 22-FEB-81    1250       500
      30

是因为 NLS_LANG造成的该问题

回复只看该作者道具举报

huqianhao

5^#

发表于 2013-10-5 20:18:39

11.2.0.3貌似没这个问题哦。

[oracle@rac1 ~]$ export NLS_LANG="SIMPLIFIED CHINESE_CHINA".ZHS16GBK
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on чǚ¹ 10Ղ 5 20:14:29 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> variable n number
SQL> exec :n := 7521
PL/SQL procedure successfully completed.
SQL> print :n
N
----------
7521
SQL> select * from scott.emp where empno = :n;
EMPNO ENAME JOB MGR
---------- -------------------- ------------------ ----------
HIREDATE SAL COMM DEPTNO
------------------- ---------- ---------- ----------
7521 WARD SALESMAN 7698
1981:02:22 00:00:00 1250 500 30
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac1 ~]$ unset NLS_LANG

复制代码

回复只看该作者道具举报

bj-jn

6^#

发表于 2013-10-7 12:46:31

本帖最后由 bj-jn 于 2013-10-7 12:48 编辑

Maclean Liu(刘相兵发表于 2013-10-5 19:16
找到问题了

首先十分感谢刘大，在linux环境下确实如您所说通过重置nls_lang可以解决此问题，但是我在windows环境却发现问题依然存在，故请教之！
将环境变量设置为空(貌似win下没有unset此类命令）
C:\Users\Administrator>set NLS_LANG=''

C:\Users\Administrator>sqlplus test/test

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 7 12:39:48 2013

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

ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified

尝试如下设置
C:\Users\Administrator>set NLS_LANG=
C:\Users\Administrator>sqlplus test/test

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 10月 7 12:38:38 2013

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

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> conn test/test
已连接。
SQL> variable n number
SQL> exec :n :=7521

PL/SQL 过程已成功完成。

SQL> select * from t where empno=:n;
select * from t where empno=:n
            *
第 1 行出现错误:
ORA-01008: 并非所有变量都已绑定

回复只看该作者道具举报

Maclean Liu(刘相兵

7^#

发表于 2013-10-7 12:57:59

Where to set the NLS_LANG in Windows

In the Registry:

On Windows systems, you should make sure that you have set an NLS_LANG registry subkey for each of your Oracle Homes:

You can easily modify this subkey with the Windows Registry Editor:

Start -> Run...

Type "regedit", and click "ok"

Edit the following registry entry:

For Oracle version 7:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

For Oracle Database versions 8, 8i and 9i:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEx\

where "x" is the unique number identifying the Oracle home.

HOME0 is the first installation

For Oracle Database 10g:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_<oracle_home_name>

There you have an entry with name NLS_LANG

When starting an Oracle tools, like SQL*Plusw, it will read the content of the oracle.key file located in the same directory to determine which registry tree will be used, therefore which NLS_LANG subkey will be used.

Note:

Some people are confused by finding a NLS_LANG set to "NA" in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE when no version 7 was installed. This is used for backwards compatibility, and can be ignored.

回复只看该作者道具举报

bj-jn

8^#

发表于 2013-10-10 19:55:33

Maclean Liu(刘相兵发表于 2013-10-7 12:57
Where to set the NLS_LANG in Windows

In the Registry:

刘大好，根据你的提示我通过注册表编辑器修改NLS_LANG这个串值不过依然有点小问题，要么报ora-01008错误，要么就是出现字符集乱码，可以既不报错又没乱码吗，请教刘大！
我的修改方式如下：
1.将NLS_LANG的值设置为空或者直接删除这个串（ORA-01008错误消失，不过有乱码）
SQL> conn test/test
????

SQL> variable n number
SQL> exec :n :=7521

PL/SQL ????????

SQL> print :n

      N
----------
   7521

2.尝试设置NLS_LANG为American_America.ZHS16GBK和American_America.AL32UTF8则ora-01008错误依旧（原始默认NLS_LANG设置为SIMPLIFIED CHINESE_CHINA.ZHS16GBK）
SQL> conn test/test
Connected.
SQL> variable n number
SQL> exec :n :=7521

PL/SQL procedure successfully completed.

SQL> select * from t where empno=:n

select * from t where empno=:n
            *
ERROR at line 1:
ORA-01008: not all variables bound

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册