We encountered the following error in a production system:
ORA-30926: unable to get a stable set of rows in the source tables
After looking for the obvious duplicates in the USING clause, I finally came across an 'on update' trigger that is causing the problem. Here is how to reproduce the error:
-- Create test table
drop table merge_test;
create table merge_test (
id number(11)
, val1 varchar2(128)
, val2 varchar2(128)
, modified_date timestamp default systimestamp
);
-- Trigger which updates modified_date column
create or replace trigger trg_merge_test
before insert or update on merge_test
for each row
begin
select systimestamp into :new.modified_date from dual;
end;
/
-- insert some test data
insert into merge_test (id, val1, val2)
select 1, 'A', 'B' from dual
union select 2, 'B', 'C' from dual
union select 3, 'C', 'D' from dual
;
-- break Oracle with ORA-30926
merge into merge_test t
using (
select 1 as id, 'AA' as val1, 'BB' as val2, 0 as deleted from dual
union
select null id, 'Z' as val1, 'ZZ' as val2, 0 as deleted from dual
union
select 2 id, 'BB' as val1, 'CC' as val2, 1 as deleted from dual
) d
on (
d.id = t.id
)
when not matched then
insert (id, val1, val2)
values (d.id, d.val1, d.val2)
when matched then
update set t.val1 = d.val1
, t.val2 = d.val2
delete where d.deleted = 1
;
--SQL Error: ORA-30926: unable to get a stable set of rows in the source tables
--30926. 00000 - "unable to get a stable set of rows in the source tables"
--*Cause: A stable set of rows could not be got because of large dml
-- activity or a non-deterministic where clause.
--*Action: Remove any non-deterministic where clauses and reissue the dml.
drop trigger trg_merge_test;
drop table merge_test;
My question here - is this a bug or expected functionality?
Thanks,
Zach