I'm using Oracle 10g. I have a query in a stored procedure that selects entire rows from a table. Currently it returns a strongly typed ref cursor (tablename%rowtype). Now I have to add some dynamic WHERE clauses to this query, but dynamic SQL doesn't support a strong ref cursor. It has been asked on SO before. My question is: is there a workaround? Use some kind of wrapper or conversion or DBMS magic to convert from the weak cursor to the strong cursor? I can guarantee that the results match the table rowtype.
There are probably better ways to achieve your underlying goal, but if you really want values from dynamic SQL into a strongly typed refcursor I guess you could do something like this:
create table TESTTABLE(TESTID number not null); create global temporary table TEMPTESTTABEL(TESTID number not null); create or replace procedure testprocedure is type testtablerefcursor is ref cursor return TESTTABLE%rowtype; type testtabletable is table of TESTTABLE%rowtype; mycursor testtablerefcursor; mysyscursor SYS_REFCURSOR; mytesttable testtabletable; begin open mysyscursor for 'select * from TESTTABLE'; fetch mysyscursor bulk collect into mytesttable; close mysyscursor; forall i in mytesttable.first..mytesttable.last insert into TEMPTESTTABEL values mytesttable(i); open mycursor for select testid from TEMPTESTTABEL; end; /