My Oracle Support Banner

ORA-904 After Upgrade To Or for a statement having a Subquery (Doc ID 1190423.1)

Last updated on MARCH 10, 2019

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle 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 - & higher
Oracle Database - & higher


MERGE statement fails with ORA-904 after upgarding to or The same query works fine in lower releases such as or

-- 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);

select * from TABLE1;
select * from TABLE2;
select * from TABLE3;

-- Below SQL works in 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


Upgrade to or


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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.