Re: brain dead FOR LOOP gotcha

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:MK-dnVrhlrhny-ffRVn-1g_at_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
>

PS: change the N_DUMMY declaration in the above sample to a DATE datatype, and you do get a runtime error -- so the code is getting excersized, but the INTO variable is not set; any value set before the FOR LOOP is preserved

++ mcs Received on Thu May 05 2005 - 12:48:47 CDT