Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!g14g2000cwa.googlegroups.com!not-for-mail From: "Rauf Sarwar" Newsgroups: comp.databases.oracle.server Subject: Re: brain dead FOR LOOP gotcha Date: 5 May 2005 11:52:30 -0700 Organization: http://groups.google.com Lines: 106 Message-ID: <1115319150.142865.285640@g14g2000cwa.googlegroups.com> References: <1115317334.960115.43080@f14g2000cwb.googlegroups.com> NNTP-Posting-Host: 195.92.67.76 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1115319155 27472 127.0.0.1 (5 May 2005 18:52:35 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Thu, 5 May 2005 18:52:35 +0000 (UTC) In-Reply-To: User-Agent: G2/0.2 Complaints-To: groups-abuse@google.com Injection-Info: g14g2000cwa.googlegroups.com; posting-host=195.92.67.76; posting-account=3xsT9QwAAAC3x7TJbwl3Hj0DXQs_bISq Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:242102 Mark C. Stock wrote: > "Rauf Sarwar" wrote in message > news:1115317334.960115.43080@f14g2000cwb.googlegroups.com... > > > > Mark C. Stock wrote: > >> "Mark C. Stock" wrote in message > >> news: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 > >> > > >> > >> 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