SQL多层嵌套引起的ORA-00904

最新推荐文章于 2022-07-14 11:03:35 发布

原创最新推荐文章于 2022-07-14 11:03:35 发布 · 4.8k 阅读

1 ·

CC 4.0 BY-SA版权

SQL语句学习专栏收录该内容

48 篇文章

订阅专栏

本文通过实验对比两个不同版本的Oracle数据库，在执行复杂嵌套查询时出现ORA-00904错误的情况。揭示了Oracle版本更新中对于标识符解析规则的变化，并提供了解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

近期在开发环境上运行一条SQL是正常的，换到测试环境上就出问题了，报ORA-00904 invalid identifier 。查询程序先处理内层子查询，它并不知道外层表的别名。故报错。ORACLE认为是个BUG, 后来就改掉了。下面来做一个实验：

C:\Documents and Settings\guogang>sqlplus test/test@10.10.15.110
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 7月 10 14:51:30 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.

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

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> create table test as select * from dba_objects where 1<>1;
表已创建。

SQL> create table test1 as select * from test where 1<>1;
表已创建。

SQL> SELECT (select *
2            from (SELECT *
3                    FROM (SELECT N.OWNER
4                            FROM test1 N
5                           WHERE n.object_id = A.Object_Id) C))
6    FROM TEST A
7   WHERE object_id = 100;
                         WHERE n.object_id = A.Object_Id) C))
                                             *
第 5 行出现错误:
ORA-00904: "A"."OBJECT_ID": 标识符无效

C:\Documents and Settings\guogang>sqlplus sqlplus test/test@10.10.15.111

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 7月 10 14:53:48 2013

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

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create table test as select * from dba_objects where 1<>1;
表已创建。

SQL> create table test1 as select * from test where 1<>1;
表已创建。

SQL> SELECT (select *
2            from (SELECT *
3                    FROM (SELECT N.OWNER
4                            FROM test1 N
5                           WHERE n.object_id = A.Object_Id) C))
6    FROM TEST A
7   WHERE object_id = 100;

未选定行

可以参考ask tom中的文章：http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1853075500346799932