Re: Counter rows in cursor using for loop.

Thanks for you answer. Unfortunatly it doesn't seem to solve my problem completly.

What I am actually doing here is to create a select statement that I then use to spool the content of a file to a pipe delimited file.

counter := 0;
for rec in cur
LOOP
    counter :=counter + 1;
    if counter<last
    then
    dbms_output.put_line(rec.column || '||''|''||' )     else
    dbms_output.put_line(rec.column || '||''|''') END LOOP This creates the select statement that I use to spool the content of the table to a flat file.

select
col1 ||'|'||
col2 ||'|'||
...
colN ||'|'
from table1

Mr Lewis elegantly solved the other cases that I had with the Nth (last) row being differnt from the rest by placing things around. However in this case I don't see how I can place things around in this case.

I see how you grab the last row and then use it after the loop. However I am spooling inside the loop as well and therefore need a criteria to stop before the last row. Otherwise I would end up with two of the last rows in my new select statement.

Thanks for you help !!

In article <37F9F152.691DB7EB_at_Unforgettable.com>,   BlueSax_at_Unforgettable.com wrote:
> What about something like this:
>
> declare
> cursor c1 is
> select *
> from emp;
> myrec c1%rowtype;
> begin
> dbms_output.enable(1000000);
> for i in c1 loop
> myrec := i;
> end loop;
> dbms_output.put_line('Last Record: EMP='||myrec.empno);
> end;
>
> When the last record is read, execution will fallout of the loop and
the
> user-defined record 'myrec' will hold the last record retrieved.
>
> screwbai_at_my-deja.com wrote:
> >
> > I want count the number of rows that a cursor contain. Basically I
want
> > to do something with the last row in the cursor.
> >
> > This is what I want to do:
> > for rec in cur
> > LOOP
> > if counter<last
> > then
> > .......
> > else
> > .......
> > end if;
> > counter :=counter + 1;
> > END LOOP;
> >
> > I guess there is no other way to find the value of last than to
> > introduce a second cursor for loop.
> >
> > This is how I want to get to the value of last.
> >
> > last :=0;
> > for rec in cur
> > LOOP
> > last := last + 1;
> > END LOOP;
> >
> > I am fairly happy with this solution. But would be curious if there
is
> > one that would be more elegant.
> .............................................................
> .............................................................
> .............................................................
> .............................................................
> .............................................................
> .............................................................
> .............................................................
> .............................................................
> .............................................................
> .............................................................
> .............................................................
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 05 1999 - 10:56:50 CDT