Re: brain dead FOR LOOP gotcha

"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 Received on Thu May 05 2005 - 13:36:15 CDT