ORA-00904 : merge (again)- Upgrade headache

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.

About these ads