Do database cursors pick up changes to the underlying data?

Question

Quick question about cursors (in particular Oracle cursors).

Let's say I have a table called "my_table" which has two columns, an ID and a name. There are millions of rows, but the name column is always the string 'test'.

I then run this PL/SQL script:

declare
 cursor cur is
  select t.id, t.name
    from my_table t
   order by 1;
 begin
   for cur_row in cur loop
     if (cur_row.name = 'test') then
        dbms_output.put_line('everything is fine!');
     else
        dbms_output.put_line('error error error!!!!!');
        exit;
     end if;
   end loop;
 end; 
 /

if I, while this is running, run this SQL:

 update my_table 
   set name = 'error'
  where id = <max id>;
commit;

will the cursor in the PL/SQL block pick up that change and print out "error error error" and exit? or will it not pick up the change at all ... or will it even allow the update to my_table?

thanks!

Erwinery

You've got some code, why don't you run it and see? – Linstock

Answer 1

A cursor effectively runs a SELECT and then lets you iterate over the result set, which is kept in a snapshot of the DB state. Because your result set has already been fetched, it won't be affected by the UPDATE statement. (Handling things otherwise would require you to re-run the query every time you advanced your cursor!)

See:

http://www.techonthenet.com/oracle/cursors/declare.php

Retreat

While it's true that the cursor won't be affected by the update statement, it isn't the case that all the data the cursor will access has been pre-fetched into memory. If that were the case, it would often take a very long time to get the first row from the cursor. What happens is that the cursor selects data "as at" the SCN that was current when it was opened. That's why long-running queries can sometimes faile with a "snapshot too old" error. – Libbylibeccio

Excellent clarification, Tony. Thank you. I will update my answer accordingly. – Retreat

For SYBASE ASE, starting with 15.7, you can declare the cursor INSENSITIVE which: "specifies that the data changes made independently of the cursor are not visible to the cursor result set. If you do not specify this argument, the default is semi_sensitive. You cannot update an insensitive cursor." – Spevek