Why does a connect by expression in a FOR loop, execute only once?

Question

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

Boche

I get 5 for both blocks in Oracle 11g. Unfortunately I dont have 9i! – Philipson

Maybe something is different with 9.x, because on 11g, it works as expected. – Kutenai

Fascinating! Does it do the same thing if you use select rownum from dual connect by level <=5 instead? – Adaptable

Using rownum does not change the behaviuor still returns 1... this is really weird. I'd like to know if someone has tried this in another 9.2i database just to check if this is the expected return... – Boche

Sounds like an optimizer Bug.... can you try to run the PL/SQL without any optimizing? I guess the optimizer sees the select from dual, infers a select from dual will only return one row and strips the loop to a single call... just a wild guess ;-) – Prevot

I get 5 for both blocks. I am on Oracle 10G. If I try this on 9.2.0.7.0, I am seeing the same results as you. – Strick

It's a bug. Premier support for 9.2 expired in 2007 It's fixed in later versions. You should really think about upgrading your server - we're on 11gR2 and I thought we were behind the times...Sorry I think you need to be logged into Oracle support to see the link I posted below – Complement

support.oracle.com/epmos/faces/… – Complement

See this thread on asktom.oracle.com. asktom.oracle.com/pls/apex/… Tom Kyte explains on other threads that there are optimizer diffs between 9i and 10g. – Ultrasound

This looks like the same issue as Tom's article too. I've check that and the PL/SQL version occur in 9.2.08 and not in later versions. – Quintus

Answer 1

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.

Sunken

Can you add bug number? That it is a bug is rather obvious based upon preceeding comments. – Bezant

@GuidoLeenders I looked but could not find the bug number. I added this answer to summarize the comments, in case no better answer is provided. I marked it as community wiki so I won't get any points for repeating what everyone else is saying. – Mima

@jonearles: Then don't post it as an answer. This should be a comment instead. – Skew

If you don't like this answer then please EDIT it. That's why it's a community wiki. All the comments are basically saying "it's a bug". This question deserves an answer, even if it's not a very good one. Nobody wants to read through all these comments. I got this idea from this thread. – Mima