cursor with execute immediate; dynamic table name

[md]

Greetings oh Brains,

I have a table name that changes. I need to build a cursor for this.
How? What I've tried looks like
(where things that start with 'v_' are variables)

ei_sql varchar2(250) := 'select house from ' || v_table_name || '
where lot = ' || '44S33E';

CURSOR c
IS
execute immediate ei_sql;

Nope - no go.


This works (as a test of the string el_sql alone)

execute immediate ei_sql into v_my_var;


thank you

[md]

Will I go blind.... :-)

It's a ref cursor I want. Thanks. Posting gets the blood flowing.
Here's an example I swiped from

http://ediyanto83.wordpress.com/2007/04/14/tuning-dynamic-sql-with-execute-immediate-and-cursor-variables/

DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
v_name employees.last_name%TYPE;
v_sal employees.salary%TYPE;
my_sal NUMBER := 2000;
table_name VARCHAR2(30) := ‘employees’;
BEGIN
OPEN emp_cv FOR ‘SELECT last_name, salary FROM ‘ || table_name ||
‘ WHERE salary > ‘ || my_sal;
LOOP
FETCH emp_cv into v_name, v_sal;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name || ‘ ‘ || v_sal);
END LOOP;
CLOSE emp_cv;
END;
/

[rob....@oraclegeeks.com]

It is remarkable how often just the act of either telling someone else or
writing down a problem will be enough to kick the answer out ...

Happens to me at least weekly.