ORA-904 After Upgrade To 10.2.0.5 Or 11.2.0.1 for a statement having a Subquery
(Doc ID 1190423.1)
Last updated on MARCH 10, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.5 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Oracle Database - 10.2.0.5 & higher
Oracle Database - 11.2.0.1 & higher
Symptoms
MERGE statement fails with ORA-904 after upgarding to 10.2.0.5 or 11.2.0.1. The same query works fine in lower releases such as 10.2.0.4 or 11.1.0.7.
-- Example
create table TABLE1 (F1 INTEGER, F2 INTEGER);
create table TABLE2 (F1 INTEGER, F2 INTEGER);
create table TABLE3 (f1 INTEGER, f2 INTEGER);
insert into TABLE1 values (11, 11);
insert into TABLE2 values (21, 21);
insert into TABLE3 values (31, 31);
commit;
select * from TABLE1;
select * from TABLE2;
select * from TABLE3;
-- Below SQL works in 10.2.0.4 but not in 11.2
MERGE into TABLE3 t3
using TABLE2 T2
on (T3.f1 = T2.f1)
when matched then
update set t3.f2=
(select T1.F2 from TABLE1 T1
where T1.f1=T2.f1-10)
when not matched then
insert (f1, f2) values (T2.F1,
(select T2.F2 from DUAL));
select T2.F2 from DUAL
*
ERROR at line 14:
ORA-00904: "T2"."F2": invalid identifier
Changes
Upgrade to 10.2.0.5 or 11.2.0.1
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |