| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Counter rows in cursor using for loop.
On Tue, 05 Oct 1999 15:56:50 GMT, screwbai_at_my-deja.com wrote:
Try writing the loop like this.
l_sep := null;
dbms_output.put_line( 'select ' );
for red in cur loop
dbms_output.put_line( l_sep || rec.column );
l_sep := '||''|''||';
end loop;
dbms_output.put_line( '||''|''' );
...
hope this helps.
chris.
>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.
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |