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.

 

[ChrisHunt]

What's in v_final_str? Some kind of SQL statement? I think you need you build it up incorporating the values of p1_id and p2_id, rather than appending that "USING" command (that I can't find in the Oracle documentation, btw).

Then you can do something like this...
[tt]
v_final_str := 'SELECT ename FROM emp WHERE empno BETWEEN '||
p1_id||' AND '||p2_id;

OPEN cur FOR v_final_str;
FETCH cur INTO v_name;
-- ... more code here ...
CLOSE cur;
[/tt]

-- Chris Hunt

http://www.extracon.com

http://www.mcgonagall-online.org.uk

http://www.napitalia.org.uk

http://www.leicesteryha.org.uk

 

[BADCODER]

Thanks. That's exactly the way v_final_str has been built. Sutle difference, p1_id anf p2_id are bind variables- 1_id, 2_id.

Now the Ref cursoe is weakly typed, as it has to be opened every time for a new query, similar to above. Thed bind variable get their values from "USING p1id, p2id which are(2 parameters out of 5, passed to the procedure which contains this v_final_str).
Different parameters (out of 5) are used each time for opening the REF cursor.

 

[ChrisHunt]

Oh, I see. Then how about this?:
[tt]
OPEN cur FOR v_final_str USING p1_id, p2_id;
[/tt]
I don't understand why you were trying to wrap up the whole thing in an EXECUTE IMMEDIATE.


-- Chris Hunt

http://www.extracon.com

http://www.mcgonagall-online.org.uk

http://www.napitalia.org.uk

http://www.leicesteryha.org.uk