Oracle strong ref cursor with dynamic sql

cursorsoracleplsql

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.

Best Answer

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;
/