Dynamic SQL and dynamic variable names

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? Received on Wed Jan 17 2007 - 15:59:28 CST