Can REF cursor be opened dynamically?

[BADCODER]

This is the declartion:
------------------------

TYPE CTyp IS REF CURSOR;
cur CTyp;


This the Error LINE:
-----------------------

v_open:= 'BEGIN OPEN Cur '||' FOR '||''''||v_final_str||''''||' USING '|| p1_id ||','|| p2_id||','|| p3_id||'; END;';

EXECUTE IMMEDIATE v_open;


This is the ERROR:
----------------------

PLS-00201: identifier 'CUR' must be declared


PS: I also tried using
'BEGIN OPEN '|| Cur || ....
but, Cur type cannot be concatenated to string.
Also everything works OK if Dynamic is not used, but since i dont know the id's ahead of time, I have to use dynamic.

 

[sem]

Why not? Just declare CUR within dynamic statement. Though, you can not declare it ouside.
You may treat EXECUTE IMMEDIATE as calling some temporary procedure. You may pass some values to it by USING clause or get some output by INTO, but in fact it knows NOTHING about variables declared within CALLING procedure.
I really hope that this explanation may push you off your IMO completely falsy way amd make you look for another one rather than ask n-th question, that even if being answered, can not help you in your moving forward

Regards, Dima

 

[RaviV]

Declare the RefCursor in a package specification. Then use the package reference in the Dynamic SQL.

As in:

PACKAGE RF IS
TYPE CTyp IS REF CURSOR;
cur CTyp;

END RF;

v_open:= 'BEGIN OPEN RF.Cur '||' FOR '||''''||v_final_str||''''||' USING '|| p1_id ||','|| p2_id||','|| p3_id||'; END;';

EXECUTE IMMEDIATE v_open;

This should work.