Oracle 合并和dbms_errlog行为与ORA-30926

大家好，
我有一个合并语句，当源表有重复的合并键时，有时会失败。
为了节省时间，我尝试使用dbms_errlog包，并让它保存coulript行，而不失败语句本身。

我在使用dbms_errlog之前得到的错误是自说自话的:

ORA-30926: unable to get a stable set of rows in the source tables

使用提到的包，合并成功。
我的问题是，我意识到即使在源表中具有多个对应行的目标行也正在更新。
我本来希望该语句仅在源上使用单个相应记录更新目标行，在其他情况下跳过并记录错误。

这是测试脚本

<--test dml error logging

-- target table
drop table dest_testlog;
create table dest_testlog (c1 number, c2 varchar2(20));
insert into dest_testlog
select level , 'DEST'   from dual connect by level <= 5;

--source table
drop table src_testlog2 ;
create table src_testlog2(c1 number, c2 varchar2(20));
insert into src_testlog2
select level c1, 'SOURCE' as c2 from dual connect by level <= 5;

-- duplicate key on source table
update src_testlog2 set c1 = 1, c2 = '????'
where c1 = 2;
select * from src_testlog2 ;

-- ORA-30926 error:  unable to get a stable set of rows in the source tables
merge into dest_testlog l
using src_testlog2 ll
on (l.c1 = ll.c1)
when matched then update set
 l.c2 = ll.c2;
   
-- create error log table
drop table err$_dest_testlog;
begin
   dbms_errlog.create_error_log(dml_table_name => 'dest_testlog');
end;
/

select * from err$_dest_testlog;

-- retry merge with log errors into clause....
-- merge is now successful, 
merge into dest_testlog l
using src_testlog2 ll
on (l.c1 = ll.c1)
when matched then update set
 l.c2 = ll.c2
log errors into err$_dest_testlog ('have a look!!!') reject limit unlimited;

-- check log for errors errore
select * from err$_dest_testlog;

select * from dest_testlog where rowid in (select elog.ora_err_rowid$ from err$_dest_testlog elog);
select * from dest_testlog;

C1 C2
1 ????   <<-- this was not desired, should be left "DEST" instead.
2 DEST
3 SOURCE
4 SOURCE
5 SOURCE


--- WOW!! update has been done even for row with unstable source dataset

--drop table dest_testlog;
--drop table src_testlog2;
---drop table err$_dest_testlog;

有没有办法避免这种行为，并使合并只更新可以安全 (即明确) 更新的行？

谢谢
MatteoP

问题是数据库不知道从源-> 目标有> 1行映射，直到它第二次更新目标行。

但是有一个解决方法。

计算源表对联接列有多少行。并且仅在 <2时执行更新:

merge into dest_testlog l
using (select c1, c2, 
              count(*) over (partition by c1) ct 
       from   src_testlog2
      ) ll
on (l.c1 = ll.c1)
when matched then update set
 l.c2 = ll.c2
 where  ct < 2;

select * from dest_testlog;

C1   C2       
   1 DEST     
   2 DEST     
   3 SOURCE   
   4 SOURCE   
   5 SOURCE

当然，如果您还插入:

rollback;

delete dest_testlog
where  c1 = 1;

select * from dest_testlog;

C1   C2     
   2 DEST   
   3 DEST   
   4 DEST   
   5 DEST 

merge into dest_testlog l
using (select c1, c2, 
              count(*) over (partition by c1) ct 
       from   src_testlog2
      ) ll
on (l.c1 = ll.c1)
when matched then update set
 l.c2 = ll.c2
 where  ct < 2
when not matched then insert values (ll.c1, ll.c2);

select * from dest_testlog;

C1   C2       
   2 DEST     
   3 SOURCE   
   4 SOURCE   
   5 SOURCE   
   1 SOURCE   
   1 ????