Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: FOR crs IN ( .... ) LOOP with dynamic SQL

Re: FOR crs IN ( .... ) LOOP with dynamic SQL

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 19 Jan 2006 09:32:28 -0800
Message-ID: <1137691937.435412@jetspin.drizzle.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US