Hi,
I was going through Tom's book and came across "snapshot too old" section. In the section small undo tablespace is created and below block is run.
Below block generates error message "snapshot too old". As per my understanding,the column values are in the cursor c after the first query. In the loop the fetching of the values are done. As the values are old and already stored in cursor, the loop will simply fetch the values stored in cursor. How the error is raised. Please explain.


ops$tkyte%ORA11GR2> declare
cursor c is
select /*+ first_rows */ object_name
from t
order by object_id;

l_object_name t.object_name%type;
l_rowcnt number := 0;
begin
open c;
loop
fetch c into l_object_name;
exit when c%notfound;
dbms_lock.sleep( 0.01 );
l_rowcnt := l_rowcnt+1;
end loop;
close c;
exception
when others then
dbms_output.put_line( 'rows fetched = ' || l_rowcnt );
raise;
end;
/
rows fetched = 301
declare
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 46 with name
"_SYSSMU46_2555494716$" too small
ORA-06512: at line 21


Before the above block this block is run from other session

begin
for x in ( select rowid rid from t )
loop
update t set object_name = lower(object_name) where rowid = x.rid;
commit;
end loop;
end;
/

create or replace function f (v int)
  return int as 
begin
  dbms_lock.sleep(1);
  return v;
end;
/

drop table t purge;
create table t as
  select rownum x from dual connect by level <= 10;

SQL> set timing on
SQL> select f(x) from t;

      F(X)
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Elapsed: 00:00:10.46

SQL> var c refcursor;
SQL> begin
  2    open :c for
  3      select f(x) from t;
  4  end;
  5  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31

SQL> print :c;

      F(X)
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Elapsed: 00:00:10.31