Recently, we upgraded one of our Oracle data bases from 10.2.0.4.0 to 10.2.0.5.0 and suddenly few of our packages become invalid with the following error:
SQL Error: ORA-00904: "E1"."EMPNO": invalid identifier
A close look to those package shows that this error is coming from a special merge command.
Let’s first model the situation
SQL> select * from v$version where rownum = 1; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi SQL> merge into 2 t_emp t1 3 using 4 (select 5 empno 6 ,ename 7 ,job 8 ,mgr 9 ,hiredate 10 ,sal 11 ,comm 12 ,deptno 13 from emp) e1 14 on 15 (e1.empno = t1.empno) 16 when not matched then 17 insert 18 values 19 (e1.empno 20 ,e1.ename 21 ,e1.job 22 ,e1.mgr 23 ,e1.hiredate 24 ,(select max(e2.sal) keep (dense_rank first order by e2.sal desc) over (partition by e2.deptno) 25 from emp e2 26 where e2.empno = e1.empno 27 ) 28 ,e1.comm 29 ,e1.deptno 30 ); 14 rows merged. SQL> rollback; Rollback complete.
So far so good.
But when we try the same merge command in the upgraded 10.2.0.5.0 database we observe this:
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
SQL> cl scr
SQL> merge into
2 t_emp t1
3 using
4 (select
5 empno
6 ,ename
7 ,job
8 ,mgr
9 ,hiredate
10 ,sal
11 ,comm
12 ,deptno
13 from emp) e1
14 on
15 (e1.empno = t1.empno)
16 when not matched then
17 insert
18 values
19 (e1.empno
20 ,e1.ename
21 ,e1.job
22 ,e1.mgr
23 ,e1.hiredate
24 ,(select max(e2.sal) keep (dense_rank first order by e2.sal desc) over (partition by e2.deptno)
25 from emp e2
26 where e2.empno = e1.empno
27 )
28 ,e1.comm
29 ,e1.deptno
30 );
where e2.empno = e1.empno
*
ERROR at line 26:
ORA-00904: "E1"."EMPNO": invalid identifier
We have to rewrite the merge in the 10.0.2.5 database so that there will be no select from the table used in the using clause (emp) during the insert part of the merge command. Something like:
SQL> merge into 2 t_emp t1 3 using 4 (select 5 empno 6 ,ename 7 ,job 8 ,mgr 9 ,hiredate 10 ,max(sal) keep (dense_rank first order by sal desc) over (partition by deptno, empno) max_sal 11 ,comm 12 ,deptno 13 from emp) e1 14 on 15 (e1.empno = t1.empno) 16 when not matched then 17 insert 18 values 19 (e1.empno 20 ,e1.ename 21 ,e1.job 22 ,e1.mgr 23 ,e1.hiredate 24 ,e1.max_sal 25 ,e1.comm 26 ,e1.deptno 27 ); 14 rows merged.
And it seems that if you are confronted to, this error occurs also in newer oracle releases. So, add this to your checklist if you are planning an upgrade.
Mohamed Houri’s Oracle Notes 