Re: How to use a different cursors in a single FOR loop

lduhl_at_corp.realcomp.com (Lee) wrote in message news:<719532c5.0411031429.76d5d0b2_at_posting.google.com>...
> I have a procedure that is based off of a FOR LOOP. I would like to
> be able to use this same procedure for a couple of diffenet
> situations. The processing within the FOR loop is always the same,
> the only thing that is different is the cursor the FOR LOOP is based
> on.
>
> I would like to know if there is a way to change the cursor that is
> used within the FOR LOOP depending on the specific situaion.
>
> For example
> FOR x IN (SELECT * from MyTable WHERE Col1 = 'ABC')
> LOOP
> .... Handle Processing
> END LOOP;
>
> Under a different situation I may want do to the following:
> FOR x IN (SELECT * from MyTable WHERE Col1 = 'DEF' AND Col2 = 1)
> LOOP
> .... Handle the same processing mentioned above.
> END LOOP;
>
> I've tried creating a func within a package that returns a ref cursor
> that is dynamically generated, but this does not work becuase the FOR
> LOOP requires an explicit cursor.
>
> I guess one opton would be to move the "Processing Logic" to a
> seperate func/proc in the package and have set of overloaded
> funcs/procs as a wrapper. Within each of these overloaded funcs/procs
> I would have the specific "FOR LOOP" declared.
>
> Any info you can supply would be greatly appreciated.
>
> Thanks
> Lee

A CURSOR FOR LOOP won't work for your case. But a REF CURSOR might and with it, you need to use the OPEN-FETCH-CLOSE constructs (no FOR LOOP here).

Some more reminders. If you can do these "processing" in SQL without PL/SQL iterations, try and do so. If not, at least try to use BULK COLLECT. Details about all these in the PL/SQL Guide.

Cheers! Received on Thu Nov 04 2004 - 03:58:36 CST