Re: cursor loop and continue statement : unexpected behaviour

Pawel Barut wrote:
http://pbarut.blogspot.com/2009/04/caution-for-loop-and-continue-in-oracle.html

Workaround :
SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1; Regards,
Rob
> Hi all,
>
> I might be overlooking something due to deadline stress. But this
> behaviour amazes me. It looks as if the cursor caches 100 rows and the
> continue statement flushes the cache and begins with the first record of
> a new cache fetch.
>
> I narrowed it down to the following script:
>
> drop table test1;
>
> create table test1 (test1_id NUMBER);
>
> begin
> for i in 1..300
> loop
> insert into test1 values (i);
> end loop;
> end;
> /
>
> declare
> cursor c_test1 is
> select *
> from test1;
> begin
> for c in c_test1
> loop
> if mod(c.test1_id,10) = 0
> then
> dbms_output.put_line(c_test1%ROWCOUNT||' '||c.test1_id||' Continue');
> continue;
> end if;
> dbms_output.put_line(c_test1%ROWCOUNT||' '||c.test1_id||' Process');
> end loop;
> end;
> /
>
> 1 1 Process
> 2 2 Process
> 3 3 Process
> 4 4 Process
> 5 5 Process
> 6 6 Process
> 7 7 Process
> 8 8 Process
> 9 9 Process
> 10 10 Continue << Where are tes1_id's 11 to 100? >>
> 11 101 Process
> 12 102 Process
> 13 103 Process
> 14 104 Process
> 15 105 Process
> 16 106 Process
> 17 107 Process
> 18 108 Process
> 19 109 Process
> 20 110 Continue << Where are tes1_id's 111 to 200? >>
> 21 201 Process
> 22 202 Process
> 23 203 Process
> 24 204 Process
> 25 205 Process
> 26 206 Process
> 27 207 Process
> 28 208 Process
> 29 209 Process
> 30 210 Continue << Where are tes1_id's 211 to 300? >>
>
>
> Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
> PL/SQL Release 11.1.0.7.0 - Production
> redhat release 5
> 2 node RAC
Received on Tue Jun 09 2009 - 04:00:53 CDT