Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Monday, April 06, 2009

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:

    8 comments:

    Laurent Schneider said...

    amazing! one more reason to Oracle V6 and never use new features!

    thanks for sharing this

    Paweł Barut said...

    Bug id is 7306422 if you want to track this issue.

    Gary Myers said...

    Nice warning. This would end up as one of those annoying things where some rows just appear to be randomly skipped (especially if the select didn't have an ORDER BY).
    Wouldn't be surprised if, when run through a debugger, the plsql optimization is off and the issue doesn't reproduce.

    Сергей said...

    THANK YOU VERY MUCH!!!
    You have seen this too!
    I wrote:
    declare
    rowsCounter number(9) := 0;
    begin
    for r in ( select rownum rn from All_Objects where rownum <= 100 ) loop
    rowsCounter := rowsCounter + 1;
    dopl('rn=' || r.rn);
    continue when mod(rowsCounter, 3) = 0;
    end loop;
    dopl('rowsCounter=' || rowsCounter);
    end;

    and... After 20 years of programming on wide range of langs I have opened the First Programmer Primer to learn about loops.

    Uff! Thank you!

    Douglass said...

    Thanks for this. I knew I wasn't crazy :-)

    Marty said...

    thank you! now i can sleep again.. :)

    Venkat said...

    Thank you very much for describing the Continue Functinality very beiefly. Thanks for letting us know the Bug. :)

    Kannan said...

    Looks like this bug has been fixed in subsequent releases of Oracle 11g ... tried it in 11.2.0.3 DB ... works flawlessly!

     

    Copyright © Paweł Barut
    Printing from DOS to USB Printer