0

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.

  • 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? – Justin Cave Mar 22 '14 at 1:50
  • We have wasted many hours with this. We just couldn't make Oracle use indexes if the indexed where clauses are conditional like that. – fejesjoco Mar 22 '14 at 19:39
  • 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? – Colin 't Hart May 27 '14 at 11:29
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.