Sebastiano, according to the Oracle PL/SQL documentation, Oracle only identifies the rows the meet the criteria when the
OPEN is executed, it does not retrieve them until the
FETCH. In the example above, the
OPEN is performed, then the same rows are deleted and committed. Where is the
FETCH getting its data?
The following sample highlights that the retrieval of data is only done at the FETCH:
SQL> set serveroutput on size 1000000
SQL> DECLARE
2 CURSOR v_cur IS
3 SELECT name FROM all_source;
4 v_rec v_cur%ROWTYPE;
5 v_rowcount NUMBER DEFAULT 0;
6 BEGIN
7 dbms_output.put_line( 'Start time: ' || to_char(sysdate,'hh24:mi:ss') );
8 OPEN v_cur;
9 dbms_output.put_line( 'After OPEN: ' || to_char(sysdate,'hh24:mi:ss') );
10
11 LOOP
12 FETCH v_cur INTO v_rec;
13 IF v_cur%NOTFOUND THEN
14 EXIT;
15 END IF;
16 v_rowcount := v_rowcount + 1;
17 END LOOP;
18
19 dbms_output.put_line( 'After FETCHes: ' || to_char(sysdate,'hh24:mi:ss') );
20
21 CLOSE v_cur;
22
23 dbms_output.put_line( 'After CLOSE: ' || to_char(sysdate,'hh24:mi:ss') );
24 dbms_output.put_line( 'Rows retrieved: ' || to_char( v_rowcount ) );
25
26 END;
27 /
Start time: 08:56:14
After OPEN: 08:56:14
After FETCHes: 08:56:46
After CLOSE: 08:56:46
Rows retrieved: 52517
PL/SQL procedure successfully completed.
In this case, Oracle identified the rows at the OPEN, but didn't retrieve them. The DELETE/COMMIT removed them from the database. Yet the FETCH is able to retrieve rows that no longer exist. The timings highlight where the work is being done. There's no way OPEN could move 50,000+ rows in about a second (not on my system, anyway).
To quote from the Oracle docs: "Rows in the result set are not retrieved when the OPEN statement is executed. Rather, the FETCH statement retrieves the rows."
