ORA-08006

I have observed a lack information about ORA-08006 in world wide web.
I am want to show yet another one case with situation that produces such error.
You can trap the ORA-08006 if your code meet following conditions:

• you have got some kind of ETL engine and it work pattern is: insert row if it new (exists in source and does not exist in target), update row if it already exists and delete row in target if it does`t exist in source;
• you use merge statement with delete clause;
• you forget to use primary key constraint on target table with fields participating in "on clause" of merge statement;

Here is simple test-case:

SQL> create table source (id number, data varchar2(10));
Table created.
SQL> create table target (id number, data varchar2(10));
Table created.
SQL> insert into source values (1,'data1');
1 row created.
SQL> insert into target values (1,'data1');
1 row created.
SQL> insert into target values (2,'data2');
1 row created.
SQL> insert into target values (2,'data2'); --Make a duplicate row in target table
1 row created.
SQL> commit;
Commit complete.

SQL> merge into target t
2  using (
3  select 0 for_delete, source.* from source
4  union all
5  select 1 for_delete, target.* from target
6    where target.id not in (select id from source)
7  ) s
8  on (t.id=s.id)
9  when matched then update set
10    t.data=s.data
11   delete where s.for_delete=1
12  when not matched then insert (t.id,t.data)
13 values (s.id,s.data);
merge into target t
*
ERROR at line 1:
ORA-08006: specified row no longer exists

So, see carefully on “source” subquery

SQL> select 0 for_delete, source.* from source
2  union all
3  select 1 for_delete, target.* from target
4    where target.id not in (select id from source);
FOR_DELETE         ID DATA
---------- ---------- ----------
         0          1      data1
         1          2      data2
         1          2      data2

It return 2 rows with flag “for_delete”=TRUE for ID=2. Consequently merge try to delete row with ID=2 two times.

I have discovered this kind of conditions in my production system. Rows had been duplicated in target table accidentally. And it would not occurred if primary or unique constraint existed on ID field on TARGET table.