Menu
I just found what I think is somewhat unexpected behavior in PLSQL vs SQL in Oracle.
If I run this query on SQLDeveloper I get 5 results:
select level lvl from dual connect by level <=5;
But if i run this statement in SQLDeveloper:
declare
w_counter number :=0;
begin
for REC in (select level lvl from dual connect by level <=5)
loop
w_counter := w_counter+1;
end loop;
dbms_output.put_line('W_COUNTER: '|| w_counter);
end;
The variable w_counter finishes with value 1 (weird)
but the weirdest part is that if I encapsulate the query in a subquery... something like:
declare
w_counter number :=0;
begin
for REC in (select * from (select level lvl from dual connect by level <=5))
loop
w_counter := w_counter+1;
end loop;
dbms_output.put_line('W_COUNTER: '|| w_counter);
end;
The w_counter variable finishes with value 5...
What do you have to say to this?
I am using Oracle 9.2i
847
That is because there is always one more evaluation of the condition of the loop than there are executions of the body of the loop.
Here, the initialization block of the for loop contains nothing, hence an empty statement. For creating a loop that runs infinitely, we can use empty statements. However, if we use break statements inside the body of the loop, then the loop can terminate.
Now about your for( ; ; ) syntax. It has no initialization statement, so nothing will be executed. Its conditional check statement is also empty, which means it evaluates to true after that the loop body is executed.
It's a bug in a version of Oracle 9i confirmed up to 9.2.0.8, but not beyond.
It was previously discussed on Ask Tom, the response simply being that "sqlplus does that".
Premier support for Oracle 9.2 ended on 2007-07-31 and extended support ended on 2010-06-30. To fix this it is recommended that you upgrade to a current version of Oracle; if unable, you should patch your database past version 9.2.0.8.
127
answered Sep 29 '22 08:09
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
