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);



Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: