Challenge? dynamic query in store procedure

In a store procedure, I try to pass in fileld and table name to construct a dynamic query and return the cursor, here is the code:

create or replace procedure TestIt(fieldname in varchar2, tablename in varchar2, p_cursor in out types.cursorType )

is

begin

      open p_cursor for select fieldname from tablename;

end;

But it encounters compilation error and I believe the reason is that there only be static query in the store procedure.

Maybe I can get my way by using DBMS_SQL package, such as

DBMS_SQL.Parse(); 
DBMS_SQL.Define_Column(); 
DBMS_SQL.EXECUTE(); 
DBMS_SQL.Fetch_Rows(); 

However I do not know how to return the cursor back to the caller.

Thanks,

Alan Received on Sun Dec 08 2002 - 13:10:15 CST