Re: keep select in transaction

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 18 Apr 2005 02:34:46 -0700
Message-ID: <1113816886.150066.196160@o13g2000cwo.googlegroups.com>

<snip>

> DECLARE
>
> CURSOR acur IS
> SELECT * FROM t1;
>
> CURSOR bcur IS
> SELECT * FROM t2;
>
> BEGIN
> OPEN acur;
> OPEN bcur;
> -- do stuff with acur
> -- bcur will never see it even in the same session
> END;
> /
>
> I suspect there is no disagreement.
> --

Not sure if I understand 100% what you are doing in your example but if the following is what you mean... then you can still see data as far as visibility in the same session goes without a commit.

SQL> create table foo1 (id number);

Table created

SQL> create table foo2 (id number);

Table created

SQL> insert into foo1 values (1);

1 row inserted

SQL> insert into foo2 values (1);

1 row inserted

SQL> commit;

Commit complete

SQL> set serverout on
SQL> declare

  2     id1_  number;
  3     id2_  number;
  4     cursor cur1 is select id from foo1 where id = 1 for update;
  5     cursor cur2 is select id from foo2 where id = 1 for update;
  6  begin
  7     open cur1;
  8        fetch cur1 into id1_;
  9        dbms_output.put_line('Before Update:'||id1_);
 10     --
 11     open cur2;
 12        fetch cur2 into id2_;
 13        dbms_output.put_line('Before Update:'||id2_);
 14        --
 15        -- Now update rows in both cursors. NO COMMITS
 16        update foo1 set id = 2 where id = id1_;
 17        update foo2 set id = 2 where id = id2_;
 18        -- Select rows from both cursors now and see changes
 19        -- For test... we know there is only one row
 20        select id into id1_ from foo1;
 21        select id into id2_ from foo2;
 22        dbms_output.put_line('After Update:'||id1_);
 23        dbms_output.put_line('After Update:'||id2_);
 24     close cur1;
 25     close cur2;

 26 end;
 27 /
Before Update:1
Before Update:1
After Update:2
After Update:2

PL/SQL procedure successfully completed.

If this is not what you wanted to show in your example... then please clarify further.

Regards
/Rauf Received on Mon Apr 18 2005 - 04:34:46 CDT