1 2 Previous Next 22 Replies Latest reply: Apr 22, 2009 2:51 AM by Timur Akhmadeev RSS

    Difference between open,fetch,close and a for loop

    604377
      Is there much difference in looping a query using fetch loop or a for loop?

      Example:


      For x in (select * from ....) loop

      end loop;


      and

      OPEN c_cursor;
      LOOP
      FETCH c_cursor INTO myVar;
      EXIT WHEN c_cursor%NOTFOUND;
      END LOOP;
      close c_cursor;

      Let's say we have a few thousand users using a function that uses this code. Would you see much difference in speed? Suppose i forget to close the cursor in the second example. Will that be a major issue if we have a very large userbase or with oracle close the cursor themself after a while? The cursor wont lock the record, right? So even if we forget to close the cursor we won't get a deadlock i suppose. Only if we declare the cursor for update but that's not the case.
        • 1. Re: Difference between open,fetch,close and a for loop
          OrionNet
          Hello,

          You should have look at asktom.oracle.com for some nice explanation and exmaples.

          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1838616387113

          If forgot to close the cursor , you might run into exceeded open cursors errors (you will consume resources and eventually run into errors).

          Regards

          Edited by: OrionNet on Apr 22, 2009 1:40 AM
          • 2. Re: Difference between open,fetch,close and a for loop
            673860
            Hi
            The first cursor which you are using is an implicit cursor in which there is no need to open fetch and close the cursor to access tha fetched data. oracle takes care for it internally.

            Second one is the explicit cursor you need to open the cursor explicitly fetched the cursor data into another variable of type cursor and the access the data then you need to close the cursor also.

            I hope this will clear the difference?

            cheers :)
            • 3. Re: Difference between open,fetch,close and a for loop
              604377
              Allright, thanks so far.

              Just one more question: suppose i have a function:

              function myFunc returns boolean
              is
              Begin
              open myCursor;
              fetch myCursor into myVar;
              if myCursor%found then
              return true;
              else
              return false;
              end if;
              close myCursor;
              end;

              Am i correct to say that if i call this function, my cursor never gets closed because it exists the function when he encouters the return statement and he never comes to the execution of the close?
              • 4. Re: Difference between open,fetch,close and a for loop
                673860
                Hi the function body first execute all the steps and then go back to the point from where it is called so it will first close the cursor and then it will return either false or either true.
                • 5. Re: Difference between open,fetch,close and a for loop
                  _Karthick_
                  Sure about that, Or its just a guess?
                  SQL> create or replace function myFunc return number
                    2  is
                    3     cursor mycursor
                    4     is
                    5     select * from dual;
                    6
                    7     myvar dual.dummy%type;
                    8  Begin
                    9     open myCursor;
                   10
                   11     fetch myCursor into myVar;
                   12
                   13     dbms_output.put_line('step 1');
                   14
                   15     if myCursor%found
                   16     then
                   17             dbms_output.put_line('step 2');
                   18
                   19             return 1;
                   20     else
                   21             return 0;
                   22     end if;
                   23
                   24     dbms_output.put_line('step 3');
                   25
                   26     close myCursor;
                   27  end;
                   28  /
                  
                  Function created.
                  
                  SQL> set serveroutput on
                  SQL>
                  SQL> select myfunc from dual
                    2  /
                  
                      MYFUNC
                  ----------
                           1
                  
                  step 1
                  step 2
                  SQL>
                  But the thing is that the cursor will be automatically closed by oracle when the function execution completes.

                  Edited by: Karthick_Arp on Apr 21, 2009 11:24 PM
                  • 6. Re: Difference between open,fetch,close and a for loop
                    604377
                    i just created a testscript:
                    declare
                    
                     function dummy return boolean
                     is
                      myVar varchar2(20);
                      cursor c_cursor is (select 'foo' from dual);
                     begin
                      open c_cursor;
                      fetch c_cursor into myVar;
                      if c_cursor%found then
                        close c_cursor;
                        return true;
                      else
                        close c_cursor;
                        return false;
                      end if;
                      close c_cursor;
                     end;
                    begin
                      if dummy then
                        dbms_output.PUT_LINE('true');
                      else
                        dbms_output.PUT_LINE('false');
                      end if;
                      
                    end;
                    And if it the close c_cursor at the end would be executed, than i suppose i would get an error because i allready closed it before...

                    >
                    But the thing is that the cursor will be automatically closed by oracle when the function execution completes.


                    Are you sure about that? So it would not be any harm to forget to close the cursor?

                    Edited by: Passero on Apr 22, 2009 8:33 AM
                    • 7. Re: Difference between open,fetch,close and a for loop
                      673860
                      Hi its not a implicit cursor so oracle will not close it by default .
                      • 8. Re: Difference between open,fetch,close and a for loop
                        _Karthick_
                        simple test case to prove the point
                        SQL> create or replace function myFunc return number
                          2  is
                          3     cursor mycursor
                          4     is
                          5     select * from dual;
                          6
                          7     myvar dual.dummy%type;
                          8  Begin
                          9     open myCursor;
                         10
                         11     fetch myCursor into myVar;
                         12
                         13
                         14     if myCursor%found
                         15     then
                         16
                         17             return 1;
                         18     else
                         19             return 0;
                         20     end if;
                         21
                         22
                         23     close myCursor;
                         24  end;
                         25  /
                        
                        Function created.
                        
                        SQL> show parameter open_cursor
                        
                        NAME                                 TYPE        VALUE
                        ------------------------------------ ----------- ------------------------
                        open_cursors                         integer     300
                        
                        SQL>
                        SQL> select myfunc from dual connect by level <= 350
                          2  /
                        
                            MYFUNC
                        ----------
                                 1
                                 1
                                 1
                                 1
                                 1
                                 1
                                 1
                                 1
                                 1
                                 1
                                 1
                         ....
                         ....
                        
                        350 rows selected.
                        
                        SQL>
                        See i dont hit any error
                        • 9. Re: Difference between open,fetch,close and a for loop
                          _Karthick_
                          Again is that a guess??
                          • 10. Re: Difference between open,fetch,close and a for loop
                            673860
                            Hi Passero
                            i have checked it in SQL* plus and you are correct that it will not close the cursor below is the code

                            create or replace function myFunc return number
                            is
                            cursor mycursor
                            is
                            select * from dual;

                            myvar dual.dummy%type;
                            Begin
                            open myCursor;

                            fetch myCursor into myVar;

                            dbms_output.put_line(' after fetched cursor');

                            if myCursor%found
                            then
                            dbms_output.put_line('cursor found');

                            return 1;
                            else
                            return 0;
                            end if;

                            dbms_output.put_line('before closing the cursor');

                            close myCursor;
                            dbms_output.put_line('cursor closed');
                            end;

                            what else do you need?
                            • 11. Re: Difference between open,fetch,close and a for loop
                              673860
                              no its clearly a explicit cursor there is no cursor for loop in the code.
                              • 12. Re: Difference between open,fetch,close and a for loop
                                _Karthick_
                                another test case with your code is
                                SQL> declare
                                  2     a integer;
                                  3
                                  4     function d return integer
                                  5     is
                                  6             myVar varchar2(20);
                                  7             cursor c_cursor is (select 'foo' from dual);
                                  8     begin
                                  9             open c_cursor;
                                 10
                                 11             fetch c_cursor into myVar;
                                 12
                                 13             if c_cursor%found then
                                 14                     return 1;
                                 15             else
                                 16                     return 0;
                                 17             end if;
                                 18
                                 19     end;
                                 20  begin
                                 21     for i in 1..500
                                 22     loop
                                 23             a:=d;
                                 24     end loop;
                                 25  end;
                                 26  /
                                
                                PL/SQL procedure successfully completed.
                                You can see i have not closed the cursor at all but still not getting any error. even though my OPEN_CURSOR is set to 300. So once the function is executed successfully oracle will close the cursor.

                                but at the same time if i try to open 301 cursor inside the function i will hit the error.
                                • 13. Re: Difference between open,fetch,close and a for loop
                                  604377
                                  Ah yes so the cursor is not closed when you put the close cursor AFTER the return because it's an explicit cursor. I think we can agree to that.

                                  The issue we have is that their are lots of cursors and sometimes the developer has forgot the close statement and sometimes it's with the return statement but where the close cursor is the last line but because of the return, the cursor is not closed.

                                  We believe that their could be an issue because their are severall functions that are called severall thousand times an hour that uses cursors that are not closed and we think that their might be a problem that our server often crashes and perhaps this could the issue but we are not sure.
                                  • 14. Re: Difference between open,fetch,close and a for loop
                                    604377
                                    >

                                    >
                                    You can see i have not closed the cursor at all but still not getting any error. even though my OPEN_CURSOR is set to 300. So once the function is executed successfully oracle will close the cursor.

                                    but at the same time if i try to open 301 cursor inside the function i will hit the error.
                                    Wont oracle create a new instance of the cursor every single time you call the function instead of trying to use the same cursor? So instead of using 1 cursor you are really using 300 cursors and using 300 times the memory...
                                    I don't know if this is correct, but that is what we are trying to find out...
                                    1 2 Previous Next