brain dead FOR LOOP gotcha

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Thu, 5 May 2005 13:25:13 -0400
Message-ID: <MK-dnVrhlrhny-ffRVn-1g@comcast.com>

been pounding my head on my desk trying to figure out why a test script was not working -- it was just a simple loop like this one that called a stored procedure with the value returned by the for loop

SQL> declare

  2     n_dummy number;
  3     n_count number default 0;
  4  begin
  5     for r1 in (
  6           select deptno
  7           into   n_dummy
  8           from   dept
  9           )
 10     loop
 11        n_count := n_count + 1;
 12        dbms_output.put_line(n_count ||') '||n_dummy);
 13     end loop;

 14 end;
 15 /

Results:

1)
2)
3)
4)

it would run, but always appear to return NULL for the selected value ... can you spot the problem? when i finally did i wasn't sure who was more brain-dead, me or pl/sql.

what's interesting is that PL/SQL (9i and 10g) both accepted the syntax -- i've not researched the docs to see if any refernces exist, but obviously the INTO clause serves absolutely no purpose, yet it parses without an error

++ mcs Received on Thu May 05 2005 - 12:25:13 CDT