DB: 23.5 FREE (VirtualBox)
When using the on matched then update clause of a merge statement, attempts to update values on a reservable column throws a confusing "ORA-55743 Reservable column update statement does not support the RETURNING or RETURN clause."
I'm not returning anything.
I assumed (ok - Bad Idea™) that the on clause of the merge statement would satisfy the where id = :id portion of the documented requirements for update statements which states that update must be in the form of:
update <table> set reservable_column = reservable_column + :val where id_col = :id
Is this a bug or [unimplemented] feature?
code
-- setup
create table show_bug (
id int generated always as identity primary key,
n int RESERVABLE not null
);
insert into show_bug (n)
select 0 from dual connect by level <= 10;
commit;
-- throws: ORA-55743: Reservable column update statement does not support the RETURNING or RETURN clause.
merge into show_bug a
using ( select level id, level n
from dual connect by level <= 10 ) b
on ( a.id = b.id)
when matched then
update set a.n = a.n + b.n -- where a.id = b.id still fails
;
-- this works as expected
merge into show_bug a
using ( select level + 10 id, level n
from dual connect by level <= 10 ) b
on ( a.id = b.id)
when not matched then insert (n) values (b.n);