ORA-904 After Upgrade To 10.2.0.5 Or 11.2.0.1 for a statement having a Subquery

Last updated on DECEMBER 06, 2016

Applies to:

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 KENTYC1 (F1 INTEGER, F2 INTEGER);
create table KENTYC2 (F1 INTEGER, F2 INTEGER);
create table KENTYC3 (f1 INTEGER, f2 INTEGER);

insert into KENTYC2 values (11, 11);
insert into KENTYC2 values (21, 21);
insert into KENTYC3 values (31, 31);
commit;

select * from KENTYC1;
select * from KENTYC2;
select * from KENTYC3;

-- Below SQL works in 10.2.0.4 but not in 11.2
MERGE into KENTYC3 t3
using KENTYC2 T2
on (T3.f1 = T2.f1)
when matched then
update set t3.f2=
(select T1.F2 from KENTYC1 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