Oracle strong ref cursor with dynamic sql

Question

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.

• Justin Cave: Are you certain that you need dynamic SQL to add the predicates? Are you sure you can't have a static query with predicates like where (parameter = 'A' and (something)) that would only evaluate the something predicate if parameter is A?
• fejesjoco: We have wasted many hours with this. We just couldn't make Oracle use indexes if the indexed where clauses are conditional like that.
• Colin 't Hart: Do you need a ref cursor? If you can do bulk collect into a PL/SQL table as in @DieterDHoker's answer, could you iterate over that rather than iterate over your strong ref cursor?

Answer 1

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