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.

3 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

  3. CAUSE
    The cause of this problem has not yet been identified, the Bug 19587421 has been created for this.
    SOLUTION
    This is not fixed yet. Please monitor the Bug 19587421 to check on which release will be fixed.

    Workaround:

    Set “_subquery_pruning_enabled” to false, i.e.:
    SQL> alter session set “_subquery_pruning_enabled”=false;

    Comment by Bug 19587421 — November 6, 2015 @ 1:03 pm | 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

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

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

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

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

%d bloggers like this: