3

Prepare the schema to reproduce the issue (on db<>fiddle):

create table t (id int, val int, modified timestamp default systimestamp)
/
create or replace trigger trigg_on_t  
before update on t for each row enable  
begin  
    :new.modified := systimestamp;  
end;
/
insert into t (id, val) values (1, 1);
commit;

The error ocures in a merge stataement with delete clause and a trigger defined on the target table:

merge into t  
using (  
    select 1 id, 10 val, 1 flag from dual  
) s on (t.id = s.id)  
when matched then  
    update set t.val=s.val  
    delete where s.flag=1

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.

What goes wrong here? Or rather, where is non-deterministic Where-clause?

Explicitly setting the timestamp value or even disabling the trigger will work:

merge into t  
using (  
    select 1 id, 1 val, 1 flag from dual  
) s on (t.id = s.id)  
when matched then  
    update set t.val=s.val, modified=systimestamp    
    delete where s.flag=1
/
1 row merged.

rollback;
alter trigger trigg_on_t disable;

merge into t  
using (  
    select 1 id, 1 val, 1 flag from dual  
) s on (t.id = s.id)  
when matched then  
    update set t.val=s.val  
    delete where s.flag=1
/
1 row merged.
0xdb
  • 3,539
  • 1
  • 21
  • 37
  • 1
    Truly surprisingly, Getting rid of the DEFAULT clause in create table statement makes it perfectly working- https://dbfiddle.uk/?rdbms=oracle_21&fiddle=6d27f5c8c580f04e93bced8175d7b7c5 – Ankit Bajpai Jun 26 '22 at 16:32
  • @AnkitBajpai Without a default clause it's not quite perfect, see [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=5f6894d43e6d456ddc5771e8ff3aeb1a) – 0xdb Jun 26 '22 at 17:53

1 Answers1

0

It seems that the problem is when you are trying to update and delete the same record in the same time. It is a strange error message I admit. You asked where is non-deterministic Where-clause - there is no such clause and that is the problem. Lets say you have 3 records in your taable:

insert into t (id, val) values (1, 1);
insert into t (id, val) values (2, 2);
insert into t (id, val) values (3, 3);
commit;
--
-- ID VAL   MODIFIED
--  1   1   30-JUN-22 06.55.45.683595000
--  2   2   30-JUN-22 06.55.45.690624000
--  3   3   30-JUN-22 06.55.45.693622000

Now, your command is:

merge into t  
using (  
    select 1 id, 10 val, 1 flag from dual  
) s on (t.id = s.id)  
when matched then  
    update set t.val=s.val  
    delete where s.flag=1
--  Result is SQL Error: ORA-30926

Update should be done on record with id=1 and delete should be done with s.flag=1 which means s.id=1 and therefore the record to delete is id=1. The same record.

Now lets see this:

merge into t  
using (  
    select 1 id, 10 val, 1 flag from dual union all 
    select 2 id, 20 val, 2 flag from dual union all 
    select 3 id, 30 val, 3 flag from dual
) s on (t.id = s.id)  
when matched then  
    update set t.val=s.val  
    delete where s.flag=1
--  Result is SQL Error: ORA-30926

... above is trying to do the same as in your question but on all three records
But if you change the where clause of delete statement and add the where clause to update statement to be deterministic there will be no error messages

merge into t  
using (  
    select 1 id, 10 val, 1 flag from dual union all 
    select 2 id, 20 val, 2 flag from dual union all 
    select 3 id, 30 val, 3 flag from dual
) s on (t.id = s.id)  
when matched then  
    update set t.val=s.val where s.flag = 1 
    delete where s.flag != 1

Finaly, lets go back to your command. There are some data from dual merged to the record with id=1. Update should be done over that record and delete command should have deterministic where clause that excludes deletion of the record that is being updated. That deterministic where clause shoud be like here:

merge into t  
using (  
    select 1 id, 10 val, 1 flag from dual  
) s on (t.id = s.id)  
when matched then  
    update set t.val=s.val  
    delete where s.flag != 1
--  1 rows merged

The problem disapears if you disable the before update trigger. If it is enabled then the trigger demands this determinism. And if you have an active after update trigger there is no problem because after update has been done there is no conflict and the record will be deleted.

CONCLUSION:
In this case when there is a table with BEFORE UPDATE trigger enabled when you try to update and delete the record simultaneously using merge there are two transactions that should be imposed. As the update transaction is interrupted by a db trigger changing the record data the second transaction (delete) gets the flag that the record that should be deleted is changed by another transaction and demands for determinism. If before update trigger do something else (updating some other table or record) and not change the record itself then the command as it is does not raise the error. You can try to change the trigger to update some value in any other table and run your command as it is ---> there will be no error.

d r
  • 3,848
  • 2
  • 4
  • 15
  • _It seems that the problem is when you are trying to update and delete the same record in the same time_, why do you think it's a problem? This is expected and well-documented behavior (see [merge_update_clause](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/MERGE.html#GUID-5692CCB7-24D9-4C0E-81A7-A22436DC968F)). – 0xdb Jun 30 '22 at 11:00
  • I explained it in Conclusion, maybe this is stated in a bad way, sorry. The problem is when you have a db trigger (before update) that changes the very same record. After update trigger does not cause the error. Even before update is not a problem if it didn't change the very same record. You can test it if you change the trigger not to put new systimestamp in the record being processed but in, lets say, some other table. There wwill be no error. So, merge+update+(dbTrigger_beforeUpdateThatIsChangingTheSameRecord)+delete=ERR – d r Jun 30 '22 at 11:59