Re: FETCH

"Ivica Dimjasevic" <smokesoft_at_email.hinet.hr> wrote in message news:<9v7hft$9hq6$1_at_as201.hinet.hr>...
> Hi! Let say I have PL/SQL procedure:
>
> CREATE OR REPLACE my_proc IS
>
> CURSOR my_cur SELECT * FROM my_table;
> my_rec my_cur%ROWTYPE;
>
> BEGIN
> OPEN my_cur;
>
> LOOP
> FETCH my_cur INTO my_rec;
> EXIT WHEN my_cur%NOTFOUND;
> ...
> END LOOP;
>
> -- Here I want to do it again with different code.
> LOOP
> FETCH my_cur INTO my_rec;
> EXIT WHEN my_cur%NOTFOUND;
> ...
> END LOOP;
>
> CLOSE my_cur;
> END;
>
> Is it possible to FETCH all rows again from the beginning without closing
> and reopening cursor?
> Thanks.

From once a fetch is called on a cursor that record is the discarded until you get to the end ot the cursor. This would mean that you would have to close and then reopen it again.

depending on what you are doing I would do something like this if you need the same data twice for som reason (like you want it to do)

        CURSOR my_cur SELECT * FROM my_table;
        my_rec my_cur%ROWTYPE;


        TYPE my_recs_tab IS TABLE OF my_cur%rowtype
	    INDEX BY BINARY_INTEGER;
	my_rec_1 my_recs_tab;
        my_rec_2 my_recs_tab; 

        i number(10) := 1;

        begin
            for my_rec in my_cur loop
               -- Don't need open, fetch, or close the FOR does it
implicitly
               my_rec_1(i) := my_rec
               my_rec_2(i) := my_rec
               i = i+1; 
            end loop;
   
          -- other code 
         end;

This way you only make one call to the cursor and you have both tables with the exact same info that you can play with without hitting the DB again.

Adam
p.s. this EXACT code may not work but the logic will. Received on Thu Dec 13 2001 - 21:29:58 CET