A cursor is a pointer to a result set. Opening a cursor marks your intent to view data as of that point in time. It doesn't have the values the query returns. It's only when you fetch data from the cursor that you get the values.
This is why opening a cursor is instant: it doesn't get any data!
For example, if you create a function that sleeps for 1s and a 10 row table:
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;
A query that calls f for each row of t takes 10s:
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
But opening the cursor for this query is instant:
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
It's only when you fetch the cursor that Oracle Database executes the query and gets the values:
SQL> print :c;
F(X)
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
Elapsed: 00:00:10.31
So if at
fetch time Oracle Database is unable to reconstruct how the data looked at the time you
opened the cursor (because the undo was overwritten or whatever) you get ORA-01555.