Re: brain dead FOR LOOP gotcha

Mark C. Stock wrote:
> "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

It worked for both number and date in my example (9.2.0.5.0)... i.e. it returned NULL. It's weird but the only reason I can think of is that explicit cursors fetch resultset into cursor rowtype (r1) and the INTO bit is simply ignored by Oracle. Select INTO is only used with the implicit cursor and ONLY if it returns one row.

Regards
/Rauf Received on Thu May 05 2005 - 13:22:14 CDT