Warning: While this appears to work, I do not know if the behavior is defined.
It seems that using a scalar subquery in the select list of an updatable view allows us to return the old value of a column. I’ll update this post with more details when I have time to do some additional testing.
- Can I get it to return incorrectly?
- What happens when an update restarts?
- How does this compare to pre-selecting?
- Is it possible to have latch issues, even though using dual?
Here is a sample script you can use to start your own tests.
drop table t1;
create table t1 as select n n1 ,n n2
from (select dbms_random.random() n from
(select * from dual connect by level <= 1000)
, (select * from dual connect by level <= 1000)
type n3 is record (n1 number, n2 number, n3 number);
type n3_tab is table of n3;
n1_cnt number := 0;
n2_cnt number := 0;
other_cnt number := 0;
minus_1_cnt number := 0;
update (select n1, n2, (select n2 from dual) n3 from t1)
set n2 = n2 + 1 — where rownum <= 10
returning n1, n2, n3 bulk collect into rets;
for i in rets.first .. rets.last loop
r n3 := rets(i);
when r.n3 = r.n1 then
n1_cnt := n1_cnt + 1;
when r.n3 = r.n2 then
n2_cnt := n2_cnt + 1;
other_cnt := other_cnt + 1;
end case inc_cnts;
if r.n3 = r.n2-1 then
minus_1_cnt := minus_1_cnt + 1;
dbms_output.put_line(‘returned correctly cnt: ‘ || minus_1_cnt);
dbms_output.put_line(‘==n1: ‘ || n1_cnt);
dbms_output.put_line(‘==n2: ‘ || n2_cnt);
dbms_output.put_line(‘else: ‘ || other_cnt);