CONTINUE Statement not Completing Loop Correctly when used in a CURSOR FOR LOOP
Last updated on JULY 05, 2017
Applies to:
PL/SQL - Version 11.1.0.6 to 11.1.0.7 [Release 11.1]Information in this document applies to any platform.
***Checked for relevance on 23-Sep-2013***
Symptoms
Normally the statement CONTINUE immediately completes the current iteration of a loop and passes control to the next iteration of the loop. But when it is executed in a CURSOR FOR LOOP and the initialization parameter PLSQL_OPTIMIZE_LEVEL is set to 2 (which is the default setting on 11G), it exits the loop.
The following sample code demonstrates the behavior:
set serverout on
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;
DECLARE
x NUMBER := 0;
CURSOR get_6rows IS SELECT * FROM EMP WHERE ROWNUM < 7;
BEGIN
DBMS_OUTPUT.PUT_LINE ('First a basic loop without CURSOR FOR LOOP running fine');
LOOP -- After CONTINUE statement, control resumes here
DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x));
x := x + 1;
IF x < 3 THEN
CONTINUE;
END IF;
DBMS_OUTPUT.PUT_LINE ('Inside loop, after CONTINUE: x = '||TO_CHAR(x));
EXIT WHEN x = 5;
END LOOP;
DBMS_OUTPUT.PUT_LINE (' After loop: x = ' || TO_CHAR(x));
DBMS_OUTPUT.PUT_LINE (' Basic loop was working correctly');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE ('Should see the same results with CURSOR FOR LOOP');
x := 0;
FOR irec IN get_6rows LOOP -- After CONTINUE statement, control should resume here
DBMS_OUTPUT.PUT_LINE ('Inside cursor loop, before CONTINUE: x = ' || TO_CHAR(x));
x := x + 1;
IF x < 3 THEN
CONTINUE;
END IF;
DBMS_OUTPUT.PUT_LINE ('Inside cursor loop, after CONTINUE: x = ' || TO_CHAR(x));
END LOOP;
DBMS_OUTPUT.PUT_LINE (' After cursor loop: x = ' || TO_CHAR(x));
IF X < 6 THEN
DBMS_OUTPUT.PUT_LINE (' So the CURSOR FOR LOOP is exited on call to CONTINUE' );
END IF;
end;
/
OUTPUT :-
==========
First a basic loop without CURSOR FOR LOOP running fine
Inside loop: x = 0
Inside loop: x = 1
Inside loop: x = 2
Inside loop, after CONTINUE: x = 3
Inside loop: x = 3
Inside loop, after CONTINUE: x = 4
Inside loop: x = 4
Inside loop, after CONTINUE: x = 5
After loop: x = 5
Basic loop was working correctly
Should see the same results with CURSOR FOR LOOP
Inside cursor loop, before CONTINUE: x = 0
After cursor loop: x = 1
So the CURSOR FOR LOOP is exited on call to CONTINUE
PL/SQL procedure successfully completed.
Cause
Sign In with your My Oracle Support account |
|
Don't have a My Oracle Support account? Click to get started |
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms