You Asked

So I have a resultset I will call this ResultSetA. I iterate this resultset and for each row in the resultset I execute a UPDATE statment.

The issue is that this UPDATE statment is making the results of ResultSetA change. This is because I am updating a table that is in ResultSetA. I wish ResultSetA would stay static and not change; but this does not seem to be the case.

Any Ideas. I basically would like my resultset to be the state of the DB when it was executed.

and Connor said...

In Oracle, this will be the case without any particular coding needs, for example:

SQL> create table t
  2  as select rownum x, rownum y from dual
  3  connect by level <= 10;

Table created.

SQL>
SQL> select * from t;

         X          Y
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10

10 rows selected.

SQL>
SQL>
SQL> set serverout on
SQL> declare
  2    resultset sys_refcursor;
  3    a int;
  4    b int;
  5  begin
  6    open resultset for select * from t;
  7
  8    loop
  9      fetch resultset into a,b;
 10      exit when resultset%notfound;
 11      dbms_output.put_line('x='||a||',y='||b);
 12
 13      update t
 14      set y = y*10
 15      where x = a+1;
 16
 17      dbms_output.put_line('Updated for x='||(a+1)||' to '||(b*10))
 18
 19    end loop;
 20  end;
 21  /
x=1,y=1
Updated for x=2 to 10
x=2,y=2
Updated for x=3 to 20
x=3,y=3
Updated for x=4 to 30
x=4,y=4
Updated for x=5 to 40
x=5,y=5
Updated for x=6 to 50
x=6,y=6
Updated for x=7 to 60
x=7,y=7
Updated for x=8 to 70
x=8,y=8
Updated for x=9 to 80
x=9,y=9
Updated for x=10 to 90
x=10,y=10
Updated for x=11 to 100

PL/SQL procedure successfully completed.

You can see the result set was 'fixed' as at the point of the cursor being opened.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Multiversion Read consistency

Rajeshwaran, Jeyabal, May 17, 2016 - 12:33 pm UTC

So I have a resultset I will call this ResultSetA - Lets say at time T1 we have this resultset 'ResultSetA'

I iterate this resultset and for each row in the resultset I execute a UPDATE statment. - Let say at time T2 you do this changes.

The issue is that this UPDATE statment is making the results of ResultSetA change.This is because I am updating a table that is in ResultSetA- Updates statements will be against your Tables/views not against the resultsets, result sets are predefined, can't updated.

I wish ResultSetA would stay static and not change - Yes the result set will stay static as of T1.( This is what read consistency is all about)

but this does not seem to be the case. - Why so any examples or test cases?

Here is an example of how the result sets available (point in time queries) even after the records got deleted and committed.

demo@ORA12C> variable x refcursor
demo@ORA12C> begin open :x for select * from dept; end;
  2  /

PL/SQL procedure successfully completed.

demo@ORA12C>
demo@ORA12C> delete from dept;

4 rows deleted.

demo@ORA12C> commit;

Commit complete.

demo@ORA12C> select * from dept;

no rows selected

demo@ORA12C> print x

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

demo@ORA12C>

Resultset prefetch size

Paul Borgen, May 19, 2016 - 12:59 pm UTC

Thank You for your help. I think the reason your example are working is that that amount of data in the resultset is small and does not exceed the resultset "prefetch" size. I am going to take your example and run a test to know for sure. For what I am doing I have 30,000 rows and I am thinking that because this exceeds my prefetch size I am seeing this issue.

Thanks for your help. If there are any other ideas please let me know.

May 20, 2016 - 2:51 am UTC

prefetch should not make any difference here.

The only thing that will make a difference is if your client app resets the cursor in some way (eg close/re-open). Because opening the cursor again, establishes a new starting time for the fetch.

Hopefully the software layer you are using is not doing that automatically "under the covers"

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Multiversion Read consistency

Resultset prefetch size

More to Explore

PL/SQL demos

PL/SQL docs

Resultset iteration while DB table involved in the resultset are being updated externally

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Multiversion Read consistency

Resultset prefetch size

More to Explore

PL/SQL demos

PL/SQL docs