Mohamed Houri’s Oracle Notes

June 8, 2012

ORA-00904 : merge (again)- Upgrade headache

Filed under: Oracle — hourim @ 2:34 pm

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

2 Comments »

  1. Thanks for posting this,The problem remains with oracle 11g version also

    Comment by Manu — July 17, 2012 @ 11:08 pm | Reply

  2. Hi Manu,

    Yes, I have already been told by one of my colleagues that the same problem is still occuring in 11g

    Best Regards

    Comment by hourim — July 18, 2012 @ 9:41 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Oracle Database 11g

Oracle Database

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Oracle SQL Tuning Tools and Tips

SQLTXPLAIN (SQLT), TRCANLZR (TRCA), SQL Health-Check (SQLHC) and SQL Tuning Topics

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Follow

Get every new post delivered to your Inbox.

Join 64 other followers

%d bloggers like this: