Dynamic Everything

[jtanner]

Howdy!

I would like to create a black box function that retreives a cursor by passing it basic SQL and then be able to use it in a FOR LOOP. Performance is not a concern (very small tables). Standard Oracle rules seem to dictate that you have to at least know\declare a table name if you want a cursor. My problem is it MUST be dynamic (passed on-the-fly).

Psuedo code goal:
<cursor_variable> := getCursor(<table>,<cols>,<where>);

cMyCusor := getCursor('mytable','emp_id,emp_name','state='''NY''' ');

FOR recEmp IN cMyCusor LOOP
   dbms_output.put_line(recEmp.emp_id);
   dbms_output.put_line(recEmp.emp_name);
END LOOP;

I am stuck (mind goo) trying to create Native Dynamic SQL or the like to achieve the above. Maybe I am making it too hard?

Please, can you offer any suggestions or hopefully a code snippet?

Thanks,

JT

 

[jtanner]

Guys,

After pounding the code for many more hours I think I have it 95% there. There is just one thing I need to get there.

When referring to a cursor using %ROWTYPE is it possible to pass a variable for the tablename?

Example: pMyTableName%ROWTYPE

How can I do this?


Thanks,

TJ