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!

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