Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Unanswered: Can REF cursors be opened dynamically?

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I think you need to give more info about what you're trying to achieve. Certainly you can open a dynamic (weak) cursor, but it all depends on what you want to do with it afterwards :-)

    Cheers
    Bill

  3. #3
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    For example:

    set serveroutput on size 100000

    declare
    type refcursor is ref cursor;
    cur refcursor;
    n number;
    begin
    open cur for 'select count(*) from dual';
    fetch cur into n;
    dbms_output.put_line ('n='||n);
    end;
    /

    n=1

    Of course you must close the cursor, fetch in a loop if reading more than one row, and so on ... this is just an example.

    Al

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I just tried it on an 8.1.7 and yes, it works. I wouldn't have imagined it would to be honest :-)

    PHP Code:
    declare
       
    type rcur is ref cursor;
       
    mycur rcur;
       
    c varchar(200);
       
    n number(12);   
    begin
       open mycur 
    for 'select * from help_classes';
       
    loop
          fetch mycur into c
    ,n;
          exit 
    when mycur%notfound;
          
    dbms_output.put_linec||);
       
    end loop
       
    close mycur;
    end
    Hth
    Bill

  5. #5
    Join Date
    Sep 2003
    Posts
    6
    Well, it worked for me and works as long as this is not opened dynamically. But i have bind variables, not know at the point of coding, hence used dynamically:


    My query would be:

    open mycur for 'select * from help_classes WHERE CLASS= :1, ROOM= :2' USING p_class_id, p_room_id;


    here p_class_id and p_room_id are 2 parameters out of total 5 passed to the procedure which contains this code. The REF cursor is everytime opened with different parametrs used to value the bund variables.

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I think I got myself confused there - I confused Alberto's post as a reply from Badcoder and promptly asnwered Alberto's answer :-( , apologies to both.

    Badcoder - I guess Alberto's reply is what you were needing?

    Cheers
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •