Thanks for the question, Rakesh.
Asked: November 21, 2016 - 4:28 pm UTC
Last updated: November 21, 2016 - 5:09 pm UTC
Version: 11.2.0.1.0
Viewed 1000+ times
You Asked
Hello,
Below Merge statement is giving internal error why? whats wrong with the code? what is the issue with rowid here?
[If I use ID instead of rowid, it is giving valid error(unable to get stable set of rows)]
---Error:
merge into t1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkmupsViewDestFro_4], [81124],
[81123], [], [], [], [], [], [], [], [], []
----- setup -------
drop table t1;
drop table t2;
create table t1(id number,name varchar2(200));
insert into t1 values(100,'Name');
create table t2(id number,name varchar2(200));
insert into t2 values(100,'Name1');
insert into t2 values(100,null);
commit;
merge into t1
using (select distinct t1.rowid rid,t2.name
from t1,t2
where t1.id=t2.id
) temp
on (t1.rowid=temp.rid)
when matched then
update set t1.name=temp.name;
and Chris said...
There's a fundamental flaw in your merge statement:
You can have two rows in t2 matching one in t1:
drop table t1;
drop table t2;
create table t1
( id number ,name varchar2 ( 200 )
) ;
insert into t1 values
( 100,'Name'
) ;
create table t2
( id number,name varchar2 ( 200 )
) ;
insert into t2 values
( 100,'Name1'
) ;
insert into t2 values
(
100,null
) ;
commit;
select t1.rowid, t2.*
from t1,t2
where t1.id=t2.id;
ROWID ID NAME
AAATSkAABAAAcTkAAA 100 Name1
AAATSkAABAAAcTkAAA 100
So, which value to merge into T1? Name1 or null?
As the docs say:
MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement. http://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606 So you need to fix your query so that you have at most row from t2 for each in t1. You'll need to give us more details about your tables for us to help you with that!
FWIW, I don't get an ORA-600 (in 11.2.0.4), but an ORA-30926:
merge into t1
using (
select distinct t1.rowid rid,t2.name
from t1,t2
where t1.id=t2.id
) temp
on (t1.rowid=temp.rid)
when matched then
update set t1.name=temp.name;
SQL Error: ORA-30926: unable to get a stable set of rows in the source tables