Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: inconsistency between sql and pl/sql
The For...Loop does an implicit fetch so you are essentiall stepping through
2 rows for every iteration of the loop. Try this:
declare v_date varchar2(100);
cursor cur1 is select to_char(odate,'yyyy-mon') from c_order
group by to_char(odate,'yyyy-mon') ; begin
for rec in cur1
dbms_output.put_line(rec.v_date);
end loop;
end;
/
HTH,
Rock Lambros
Programmer
Las Vegas Valley Water District
<tedchyn_at_yahoo.com> wrote in message news:7u4r4n$n3t$1_at_nnrp1.deja.com...
> Sir, why am I getting this inconsistency between sql and pl/sql for
> identical sql statement (sql 10 rows and pl/sql 5 rows).
>
> Thanks in advance ted chyn
>
> SQL> select to_char(odate,'yyyy-mon') from c_order
> 2 group by to_char(odate,'yyyy-mon') ;
>
> TO_CHAR(
> --------
> 1973-nov
> 1999-apr
> 1999-aug
> 1999-jul
> 1999-jun
> 1999-mar
> 1999-may
> 1999-oct
> 1999-sep
> 2000-sep
>
> 10 rows selected.
>
> SQL> declare v_date varchar2(100);
> 2 cursor cur1 is select to_char(odate,'yyyy-mon') from c_order
> 3 group by to_char(odate,'yyyy-mon') ;
> 4 begin
> 5 for rec in cur1 loop
> 6 fetch cur1 into v_date;
> 7 dbms_output.put_line(v_date);
> 8 end loop;
> 9 end;
> 10 /
> 1999-apr
> 1999-jul
> 1999-mar
> 1999-oct
> 2000-sep ** only 5 rows here
>
> PL/SQL procedure successfully completed.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Oct 15 1999 - 00:18:59 CDT