Caution: FOR loop and CONTINUE in Oracle 11g

Written by Paweł Barut
Friend of mine showed me interesting issue of using CONTINUE Statement. CONTINUE is an new statement in Oracle 11; it allow to skip processing of current iteration of look, and go to begging of next iteration. So here is working sample:

SQL> set serveroutput on
SQL> begin
  2    dbms_output.put_line('=== START');
  3    for i in 1..15 loop
  4      dbms_output.put_line('before:'||i);
  5      if mod(i, 5) = 0 then
  6        dbms_output.put_line('CONTINUE');
  7        continue;
  8      end if;
  9      dbms_output.put_line('after:'||i);
 10    end loop;
 11    dbms_output.put_line('=== STOP');
 12  end;
 13  /
=== START
before:1
after:1
before:2
after:2
before:3
after:3
before:4
after:4
before:5
CONTINUE
before:6
after:6
before:7
after:7
before:8
after:8
before:9
after:9
before:10
CONTINUE
before:11
after:11
before:12
after:12
before:13
after:13
before:14
after:14
before:15
CONTINUE
=== STOP

But when we use CONTINUE in loop, that is based on implicit cursor, it gives wrong results:

SQL> begin
  2    dbms_output.put_line('=== START');
  3    for r in (select level num from dual connect by level <= 115) loop
  4      dbms_output.put_line('before:'||r.num);
  5      if mod(r.num, 5) = 0 then
  6        dbms_output.put_line('CONTINUE');
  7        continue;
  8      end if;
  9      dbms_output.put_line('after:'||r.num);
 10    end loop;
 11    dbms_output.put_line('=== STOP');
 12  end;
 13  /
=== START
before:1
after:1
before:2
after:2
before:3
after:3
before:4
after:4
before:5
CONTINUE
before:101
after:101
before:102
after:102
before:103
after:103
before:104
after:104
before:105
CONTINUE
=== STOP

In that case CONTINUE statement goes to iteration no 101 instead of going to iteration 6, as expected!!
To make the long story short, I've experiment with few setting and found that setting PLSQL_OPTIMIZE_LEVEL to 1 this block runs as expected:

SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;
SQL> begin
  2    dbms_output.put_line('=== START');
  3     for r in (select level num from dual connect by level <= 115) loop
  4       dbms_output.put_line('before:'||r.num);
  5       if mod(r.num, 5) = 0 then
  6         dbms_output.put_line('CONTINUE');
  7         continue;
  8       end if;
  9       dbms_output.put_line('after:'||r.num);
 10     end loop;
 11     dbms_output.put_line('=== STOP');
 12  end;
 13  /
=== START
before:1
after:1
before:2
after:2
before:3
after:3
before:4
after:4
before:5
CONTINUE
before:6
after:6
[... many lines cutted out from output...]
after:99
before:100
CONTINUE
before:101
after:101
before:102
after:102
before:103
after:103
before:104
after:104
before:105
CONTINUE
before:106
[... many lines cutted out from output...]
after:114
before:115
CONTINUE
=== STOP

For me it looks as bug in PL/SQL Optimizer on level 2. In that case PL/SQL does bulk collect for 100 rows when FOR LOOP with cursor is used. Seems that CONTINUE in that case causes to fetch next portion of records instead of taking next record from buffer. I've reported bug to Oracle Support, but until it will get fixed, be aware of possible wrong results.
This was tested on Oracle 11.1.0.6 and 11.1.0.7 Enterprise Edition.

Keep reading,
Paweł

--
Related Articles on Paweł Barut blog:

• Comments in SQL*Plus
• Dynamically set name of spool file in SQL*Plus
• Table that cannot be deleted

• ORA-03297 on empty datafile
• Flashback Table and Materialized View – not working together