Re: Dynamic SQL and dynamic variable names

saketkhanna_at_gmail.com wrote:
> I have a peculiar problem I've been working on for a while now...
>
> I am trying to call a procedure in a loop and pass the value of each
> column in a cursor.
>
> something like this:
>
> for v_cursor_columns in c_columns
> loop
> begin
> v_dynamic := 'BEGIN compare_proc ( v_cursor_columns.column_name)';
> execute immediate v_dynamic;
> end loop;
>
>
> Basically - I am trying to access variable in the current anonymous
> pl/sql block.
>
> However, I get this error whenever I try:
>
> PLS-00201: identifier 'v_cursor_diff.column_name' must be declared
>
> In some other parts, I am even trying to generate the variable name (in
> above example v_cursor_columns.column_name ) on the fly using
> concatenation.
>
> How can I accomplish these tasks?
>
> I've searched all the web I could, but couldn't find such an example.
> Someone mentioned that dynamic sql will access global variables only -
> Can someone please help me?

Shouldn't that be

v_dynamic := 'BEGIN compare_proc (:b1)';

for v_cursor_columns in c_columns
loop

    execute immediate v_dynamic USING v_cursor_columns.column_name; end loop; Received on Thu Jan 18 2007 - 04:52:13 CST