11 Replies Latest reply: Oct 9, 2012 7:07 AM by BluShadow RSS

    REF CURSOR in a cursor FOR LOOP

    862489
      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
          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
            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
              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
                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
                  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
                    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
                      Billy~Verreynne
                      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
                        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
                          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
                            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
                              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.