This discussion is archived
11 Replies Latest reply: Oct 9, 2012 5:07 AM by BluShadow RSS

REF CURSOR in a cursor FOR LOOP

862489 Newbie
Currently Being Moderated
declare
TYPE typ_name is ref cursor;
V_ref_cur typ_name;
CURSOR c1 is SELECT col1,col2 from table_name1;

BEGIN

for i in C1
Loop
Open v_ref_cur for

select * from table_name2;
end loop;

END;

Pls let me know if it is possible to open/use ref cursor inside a cursor for loop?


Thanks
  • 1. Re: REF CURSOR in a cursor FOR LOOP
    908002 Expert
    Currently Being Moderated
    yes you can open ./ use refcursor in another cursor for loop..


    But in the example you mentioned... ref cursor not really needed..why can not u use two cursor for loop one inside other...
  • 2. Re: REF CURSOR in a cursor FOR LOOP
    jeneesh Guru
    Currently Being Moderated
    What are you trying to achieve?

    Your requirement seems very strange. What is your business requirements?

    And why cant you just try?

    Below is a sample code..
    SQL> declare
      2   rc1 sys_refcursor;
      3   cursor csr is select distinct deptno from emp;
      4  begin
      5   for rec in csr loop
      6    open rc1 for 'select * from dept where deptno = '||rec.deptno;
      7     -- do your things...
      8    close rc1;
      9   end loop;
     10  end;
     11  /
    
    PL/SQL procedure successfully completed.
    NB:Never use like this :)
  • 3. Re: REF CURSOR in a cursor FOR LOOP
    BluShadow Guru Moderator
    Currently Being Moderated
    859486 wrote:
    declare
    TYPE typ_name is ref cursor;
    V_ref_cur typ_name;
    CURSOR c1 is SELECT col1,col2 from table_name1;

    BEGIN

    for i in C1
    Loop
    Open v_ref_cur for

    select * from table_name2;
    end loop;

    END;

    Pls let me know if it is possible to open/use ref cursor inside a cursor for loop?
    You can, but you shouldn't, as your requirement clearly shows no need for a ref cursor. You also have an issue that you are opening a ref cursor, but not closing it again before you try to open it again in subsequent iterations of your loop.

    Your code is therefore fundamentally flawed.

    Explain what you are business logic you are trying to achieve and people can advise you on the best way forward.
  • 4. Re: REF CURSOR in a cursor FOR LOOP
    862489 Newbie
    Currently Being Moderated
    Thanks for your suggestions


    Actually, we have REF CURSOR as an OUT variable in the Pl/sQL procedure, hence trying to use it dynamically inside the cursor for loop



    create or replace procedure test1 (P_ID IN NUMBER,P_OUT OUT SYS_REFCURSOR)

    cursor c1 is select <cols1> from table_name where col2=P_ID;

    BEGIN
    for i in c1
    Loop
    OPEN P_OUT For select <col_names> where col1=i.<cols1>;
    end loop;
    end;

    Although there are no compilation errors, the output is not coming expected.

    Thanks
  • 5. Re: REF CURSOR in a cursor FOR LOOP
    BluShadow Guru Moderator
    Currently Being Moderated
    859486 wrote:
    Thanks for your suggestions


    Actually, we have REF CURSOR as an OUT variable in the Pl/sQL procedure, hence trying to use it dynamically inside the cursor for loop
    Ok. Still not much information, but at least we can see where you're ref cursor is supposed to be going (i.e. outside the procedure)
    create or replace procedure test1 (P_ID IN NUMBER,P_OUT OUT SYS_REFCURSOR)

    cursor c1 is select <cols1> from table_name where col2=P_ID;

    BEGIN
    for i in c1
    Loop
    OPEN P_OUT For select <col_names> where col1=i.<cols1>;
    end loop;
    end;

    Although there are no compilation errors, the output is not coming expected.
    What output are you expecting?
    Though you won't get compilation errors, you may get a "too many open cursors" error at runtime.

    Another question for you...

    Are you thinking that the ref cursor is storing the results of the query you are executing in it, and building up a result set (appending data as you're looping)? If so, you are not understanding what a ref cursor is...

    {thread:id=886365}
  • 6. Re: REF CURSOR in a cursor FOR LOOP
    Ashu_Neo Pro
    Currently Being Moderated
    There is not need of using a local cursor.

    You can write it like this( Just formatted your sample code).
    CREATE OR REPLACE PROCEDURE test1 (P_ID IN NUMBER,P_OUT OUT SYS_REFCURSOR)
    is
    /*
    cursor c1 
    is 
    select <cols1> from table_name where col2=P_ID;
    */
    BEGIN
         --for i in c1
         --Loop
         OPEN P_OUT For select <col_names> --where col1=i.<cols1>;
         WHERE col1 IN (select DISTINCT <cols1> from table_name where col2=P_ID) 
          ;
         --end loop;
    end;
    Thanks!
  • 7. Re: REF CURSOR in a cursor FOR LOOP
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    859486 wrote:

    Actually, we have REF CURSOR as an OUT variable in the Pl/sQL procedure, hence trying to use it dynamically inside the cursor for loop
    Conceptually wrong. This needs to be done using only SQL and via a SQL JOIN, EXISTS or IN operation.

    Doing a cursor loop fetch and inside doing another cursor loop fetch, is called a nested loop join - and SQL is significantly better and faster at doing joins. And it supports far more sophisticated joins algorithms than a primitive (and slow) nested loop coded in PL/SQL (or other languages like Java or .Net).
    create or replace procedure test1 (P_ID IN NUMBER,P_OUT OUT SYS_REFCURSOR)
    cursor c1 is select <cols1> from table_name where col2=P_ID;
    BEGIN
    for i in c1
    Loop
    OPEN P_OUT For select <col_names> where col1=i.<cols1>;
    end loop;
    end;

    Although there are no compilation errors, the output is not coming expected.
    Correct - "wrong" output is obvious.. As there is a single output cursor handle passed to the caller. And the loop creates multiple cursors - which means the LAST cursor created is what is passed to the caller.

    It also means resource leakage. This code haemorrhages cursors handles - badly. Cursors that will remain open, but unusable, for the entire duration of the session that ran the code.

    Whatever you tried doing with this code - don't. It is flawed and dangerous. Rather step back and explain what the actual requirements are.
  • 8. Re: REF CURSOR in a cursor FOR LOOP
    9876564 Newbie
    Currently Being Moderated
    859486 wrote:
    Thanks for your suggestions


    Actually, we have REF CURSOR as an OUT variable in the Pl/sQL procedure, hence trying to use it dynamically inside the cursor for loop



    create or replace procedure test1 (P_ID IN NUMBER,P_OUT OUT SYS_REFCURSOR)

    cursor c1 is select <cols1> from table_name where col2=P_ID;

    BEGIN
    for i in c1
    Loop
    OPEN P_OUT For select <col_names> where col1=i.<cols1>;
    end loop;
    end;

    Although there are no compilation errors, the output is not coming expected.

    Thanks
    What i understood from your logic is that you are trying to open your ref cursor everytime for your outer or local loop.
    Instead of opening your cursor each time , you can first select all the values from your query and put them all into some table.
    Once you are done , have a "open ref curosor" clause at the end to return the values.

    I hope it will help you.

    thanks
  • 9. Re: REF CURSOR in a cursor FOR LOOP
    BluShadow Guru Moderator
    Currently Being Moderated
    AbSHeik wrote:
    What i understood from your logic is that you are trying to open your ref cursor everytime for your outer or local loop.
    Instead of opening your cursor each time , you can first select all the values from your query and put them all into some table.
    Once you are done , have a "open ref curosor" clause at the end to return the values.
    So your recommendation is to query the data out of a table... insert them into another table, and then pass back a query on this other table?

    Erm... wouldn't it be easier to just pass back a query on the original table data?
  • 10. Re: REF CURSOR in a cursor FOR LOOP
    9876564 Newbie
    Currently Being Moderated
    BluShadow wrote:
    AbSHeik wrote:
    What i understood from your logic is that you are trying to open your ref cursor everytime for your outer or local loop.
    Instead of opening your cursor each time , you can first select all the values from your query and put them all into some table.
    Once you are done , have a "open ref curosor" clause at the end to return the values.
    So your recommendation is to query the data out of a table... insert them into another table, and then pass back a query on this other table?

    Erm... wouldn't it be easier to just pass back a query on the original table data?
    My point is that you can not open your ref crsor twice in case it is one of the output parameters ?
    Please feel free to correct me.

    Edited by: AbSHeik on Oct 9, 2012 4:30 AM
  • 11. Re: REF CURSOR in a cursor FOR LOOP
    BluShadow Guru Moderator
    Currently Being Moderated
    AbSHeik wrote:
    BluShadow wrote:
    AbSHeik wrote:
    What i understood from your logic is that you are trying to open your ref cursor everytime for your outer or local loop.
    Instead of opening your cursor each time , you can first select all the values from your query and put them all into some table.
    Once you are done , have a "open ref curosor" clause at the end to return the values.
    So your recommendation is to query the data out of a table... insert them into another table, and then pass back a query on this other table?

    Erm... wouldn't it be easier to just pass back a query on the original table data?
    My point is that you can not open your ref crsor twice in case it is one of the output parameters ?
    Please feel free to correct me.
    Well, you can... but you shouldn't....
    SQL> ed
    Wrote file afiedt.buf
    
      1  create or replace procedure do_not_do_this(p_rc out sys_refcursor) is
      2    cursor cur_emp is select * from emp;
      3  begin
      4    for i in cur_emp
      5    loop
      6      dbms_output.put_line('Opening ref cursor...');
      7      open p_rc for 'select * from dept where deptno = :1' using i.deptno;
      8    end loop;
      9* end;
    SQL> /
    
    Procedure created.
    
    SQL> set serverout on;
    SQL> var rc refcursor;
    SQL> exec do_not_do_this(:rc);
    Opening ref cursor...
    Opening ref cursor...
    Opening ref cursor...
    Opening ref cursor...
    Opening ref cursor...
    Opening ref cursor...
    Opening ref cursor...
    Opening ref cursor...
    Opening ref cursor...
    Opening ref cursor...
    Opening ref cursor...
    Opening ref cursor...
    Opening ref cursor...
    Opening ref cursor...
    
    PL/SQL procedure successfully completed.
    
    SQL> print rc;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
    ... lots of open cursors in the session now, but only 1 that can be accessed to be closed again... the last one.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points