Re: brain dead FOR LOOP gotcha

Mark C. Stock wrote:
> "Rauf Sarwar" <rs_arwar_at_hotmail.com> wrote in message
> news:1115317334.960115.43080_at_f14g2000cwb.googlegroups.com...
> >
> > 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
> >

>
> first time running into this, since i was doing some quick cut and
paste
> between test scripts (in other words, just for the record, i know the
INTO
> doesn't belong there)

>
> tried again in 9.0.1.4.0 & 10.1.0.2.0 -- the error was actually a
compile
> time error, not a runtime (ORA-00932: inconsistent datatypes:
expected DATE
> got NUMBER)

>
> so at compile time, valid SELECT INTO syntax is allowed (and
verified) but
> the INTO clause is ignored at runtime

>
> ++mcs

I have not seen this before either... plus never tried it myself since as you said... INTO clause does not belong inside cursor select statement.

Learn something new every day. Next thing to try... take a rental car upto 30mph and throw it in reverse... :)

Regards
/Rauf Received on Thu May 05 2005 - 13:52:30 CDT