Llayland’s Food, Oracle, and Haskell

May 5, 2013

Update returning old values, possible solution

Filed under: Oracle — llayland @ 5:41 am

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.

  • ¬†Correctness
    • Can I get it to return incorrectly?
    • What happens when an update restarts?
  • Performance
    • 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;
rets n3_tab;

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);
<<inc_cnts>> case
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;
end if;
end loop;
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);



Create a free website or blog at WordPress.com.