Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: FOR crs IN ( .... ) LOOP with dynamic SQL
Björn Wächter wrote:
> Hi all,
>
> I'm using Oracle 10.1.0.4.0.
> I like this syntax for looping through
> a resultset:
>
>
> FOR crs_test IN (
> SELECT G_ID, REPEAT_UNIT
> FROM ADM_MSG)
> LOOP
> -- your code here
> END LOOP;
>
>
> Is there a way to use this syntax with
> dynamic SQL?
> Like:
>
>
> v_sql := 'SELECT G_ID, REPEAT_UNIT FROM ADM_MSG';
>
> FOR crs_test IN (
> v_sql
> )
> LOOP
> -- your code here
> END LOOP;
>
> But this is not possible.
> Is there another way?
>
> Björn
There is but as Sybrand asks why ... and in 10g it is far from the best practice for fetching records from with a cursor.
Go to Morgan's Library at www.psoug.org and look up the following topic: Bulk Binding. There you will find examples using BULK COLLECT, FORALL, and Native Dynamic SQL.
-- Daniel A. Morgan http://www.psoug.org damorgan_at_x.washington.edu (replace x with u to respond)Received on Thu Jan 19 2006 - 11:32:28 CST