Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: inconsistency between sql and pl/sql

Re: inconsistency between sql and pl/sql

From: Rock Lambros <grkpetra_at_yahoo.com>
Date: Thu, 14 Oct 1999 22:18:59 -0700
Message-ID: <7u6dgb$pm5$1@ash.prod.itd.earthlink.net>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US