1 2 Previous Next 16 Replies Latest reply: May 10, 2011 8:48 AM by BluShadow RSS

    ref cursor

    635362
      Hi everybody,
      oracle version 11.2.0.1.0
      i am getting ORA-01001 : Invalid cursor error while running the below code(i have similar code in my code)

         begin
              stmt   :=  select_statement;
              if condition_x  then
                 open ref_cursor for stmt using variable_list;
              end if;
              fetch ref_cursor into v_rec;
              loop
                  if ref_cursor%isopen then
                     exit when ref_cursor%notfound
                  end if;
                  ................ 
                  if ref_cursor%isopen then
                    fetch ref_cursor into v_rec;
                  end if;
             end loop
      
        end
       
      problem - 1 : it is going to infinte loop because ref_cursor%isopen is returning false

      problem - 2 : let us think the ref cursor returning 2 records ..... but it is looping for third records and when coming to statement exit when ref_cursor%notfound
      is giving ORA-01001 : Invalid cursor


      when i am running the same select statement and code outside procedure as a anonymus block it is successfully running
      but when i am calling this procedure from oracle reports then it is returning invalid cursor error

      regards
      murali.

      Edited by: Mr. Murali on May 9, 2011 11:45 PM

      Edited by: Mr. Murali on May 9, 2011 11:51 PM

      Edited by: Mr. Murali on May 9, 2011 11:52 PM
        • 1. Re: REF CURSOR PROBLEM
          sybrand_b
          The code you posted doesn't make sense to me.
          It is unnecessary to check whether a cursor is open inside a loop for every record.
          A cursor is open or it is not open. A cursor is not open when it is exhausted or closed.
          The code should read

          fetch
          while ref_cursor%found loop

          -- processing here
          fetch
          end loop;

          Also you should always post a four-digit version number, and no:
          this isn't too tiresome and no
          you won't get RSI by doing so.
          Also you should do this instead of SHOUTING your subject lines.

          ----------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: REF CURSOR PROBLEM
            635362
            Hi Sybrand Bakker,

            I just modified little bit the code posted here.
            actuallyuy this is an existing code..it is written very long back let us say 6 to 7 years back...
            now i have got a issue from client saying that oracle report is got hanged while running the report.so i have traced and found the problematic area that i have
            shown here.
            and may i know the reason why this code not running succesfully?

            thanks
            murali.

            Edited by: Mr. Murali on May 10, 2011 12:01 AM
            • 3. Re: REF CURSOR PROBLEM
              BluShadow
              Mr. Murali wrote:
              Hi Sybrand Bakker,

              I just modified little bit the code posted here.
              actuallyuy this is an existing code..it is written very long back let us say 6 to 7 years back...
              Ok, so fix it in the manner that sybrand indicates. Clearly the original code was poorly written by someone who didn't know what they were doing.
              • 4. Re: REF CURSOR PROBLEM
                635362
                Hi BluShadow,
                Thanks for the reply.I will fix as you suggested.
                But i want to know one thinng...
                why the ref cursor is looping for more than one records
                    example :
                    as i said u above the cursor returned one record..... iteration is as follows
                    
                    fetch done
                    loop
                    1st iteration (first record)
                    fetch done
                    sucess
                
                    2nd iteration (no records)
                    here it has to exit from the loop...unfirunatly this is not happened.
                    fetch done.
                
                    3rd iteration (no records)
                    return ORA'01001 ' invalid cursor
                   
                whz the loop is not exited from loop as there is no data in cursor for second iteration and whz it is looping for third iteration

                till now none of our client dont have this problem....after 11g migration this problem came....

                Edited by: Mr. Murali on May 10, 2011 12:17 AM

                Edited by: Mr. Murali on May 10, 2011 12:26 AM
                • 5. Re: REF CURSOR PROBLEM
                  635362
                  Hi everzbody .

                  oracle version is 11.2.0.2.0
                  i have seen this behaviour in 11.2.0.2.0 only.
                  prior this version the procedure running correclty.

                  thanks
                  murali.
                  • 6. Re: REF CURSOR PROBLEM
                    Billy~Verreynne
                    This is how a typical cursor fetch loop looks like - including a ref cursor processing loop. Of course, bulk processing should be considered and ideally the processing should be done using a single SQL statement instead (i.e. process data sets using SQL and not rows using PL/SQL).
                    SQL> declare
                      2          c       sys_refcursor;
                      3          objName varchar2(30);
                      4          i       integer;
                      5  begin
                      6          open c for select object_name from user_objects;
                      7  
                      8          i := 0;
                      9          loop
                     10                  fetch c into objName;
                     11                  exit when c%NotFound;
                     12  
                     13                  --// process fetched row
                     14                  i := i + 1;
                     15          end loop;
                     16  
                     17          close c;
                     18  
                     19          dbms_output.put_line( i||' row(s) processed' );
                     20  end;
                     21  /
                    48 row(s) processed
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> 
                    If your loop does not look like this, why not? And post a sample (in a similar fashion than the above sample code) as to what your code does.
                    • 7. Re: REF CURSOR PROBLEM
                      sybrand_b
                      Billy,
                      I am really sorry, but this code is not how a typical cursor loop should look like.
                      It really should be
                      open
                      fetch
                      while <c>%found loop
                      fetch
                      end loop;

                      I agree with my compatriot Edsgar Dijkstra gotos in programming are dangerous. Your exit is essentially a goto.
                      Also if you follow the method of Jackson Structured Programming, you will code it this way.
                      Which always works, and always results in predictable and readable code!!!

                      Let's give a good example to the learners!

                      ---------
                      Sybrand Bakker
                      Senior Oracle DBA
                      • 8. Re: REF CURSOR PROBLEM
                        BluShadow
                        sybrand_b wrote:
                        Billy,
                        I am really sorry, but this code is not how a typical cursor loop should look like.
                        It really should be
                        open
                        fetch
                        while <c>%found loop
                        fetch
                        end loop;

                        I agree with my compatriot Edsgar Dijkstra gotos in programming are dangerous. Your exit is essentially a goto.
                        Also if you follow the method of Jackson Structured Programming, you will code it this way.
                        Which always works, and always results in predictable and readable code!!!

                        Let's give a good example to the learners!
                        I have to say, I use the same way as Billy if I have to do a loop.
                        Whilst using a WHILE loop is just as effective, I prefer not to have to put the FETCH statement in twice as any changes to the fetch has to be done in two places.

                        I'd say both are equally valid and good ways of programming, even if the EXIT is a little like a GOTO (and yes I agree that we shouldn't use GOTO's, I hate them).
                        • 9. Re: REF CURSOR PROBLEM
                          smon
                          yep, I'd go with the single-fetch method too.
                          • 10. Re: REF CURSOR PROBLEM
                            635362
                            Please see the example i worked on oracle version 11.2.0.2.0
                            Create Or Replace Procedure example3 Is
                            -- first run this program without any changes
                            Type Cur_Ref Is Ref Cursor;
                            
                            V_Cur_Ref       Cur_Ref;
                            V_Str           Varchar2(1000);
                            V_Cur_Nec       emp%rowtype;
                            
                            Stmt            Varchar2(1000)  :=  'Select * From Emp E '; --where 1 =0 ';  -- case 1 -- remove this comment then it is giving invalid cursor error
                            
                            I               Number := 0 ;
                            
                            Begin
                            
                               Open V_Cur_Ref For Stmt;
                               Fetch V_Cur_Ref Into V_Cur_Nec;
                               Dbms_Output.Put_Line(' -- Before Loop -- ');
                               Loop
                                    -- Exit When V_Cur_Ref%Notfound; -- case 2 -- remove this comment and comment till C after this statement then see the resulting behaviour it is sucessfully get exited from program unit
                                     
                                     Dbms_Output.Put_Line(' -- Loop  1  -- ');
                                     I := I+1;
                                     Dbms_Output.Put_Line(' -- Loop  2  -- '||I);
                                     If I >= 40 Then
                                        Exit;
                                     End If;
                                             If Not V_Cur_Ref%Isopen Then
                                        I := I+1;
                                     End If;
                                     Dbms_Output.Put_Line(' -- Loop  3  -- '||I);     
                                      
                                     If V_Cur_Ref%Isopen Then
                                        I := I+1;
                                     End If;
                                     
                                     Dbms_Output.Put_Line(' -- Loop  4  -- '||I);     
                                     If V_Cur_Ref%Notfound Then
                                        I := I+1;
                                     End If;     
                                    
                                  If V_Cur_Ref%Isopen Then
                                     Exit When V_Cur_Ref%Notfound;
                                  End If;
                                  -- C
                                  Dbms_Output.Put_Line(' -- Loop  5  -- ');
                                  If V_Cur_Ref%Isopen Then
                                     Fetch V_Cur_Ref Into V_Cur_Nec;
                                     Dbms_Output.Put_Line(' -- Loop  6  -- ');
                                  End If;
                                  
                               End Loop;
                               Dbms_Output.Put_Line(' -- After Loop-- ');
                               Close V_Cur_Ref;
                            End;
                            Thanks & Regards
                            Murali.
                            • 11. Re: REF CURSOR PROBLEM
                              Billy~Verreynne
                              sybrand_b wrote:

                              I am really sorry, but this code is not how a typical cursor loop should look like.
                              It really should be
                              open
                              fetch
                              while <c>%found loop
                              fetch
                              end loop;
                              The reason why I do not code this structure (in any language) is the duplication of the FETCH call.

                              In the old days, the FETCH would have been some custom I/O call and the cursor loop a file/magnetic tape/whatever processing loop. Each FETCH call had to be checked for return codes to deal with I/O errors. Having multiple FETCH calls in the same code unit required the same checks to be duplicated. It also made debugging more difficult as there were more than one I/O "contact point" in the code with the underlying data.

                              And code duplication becomes a major issue wrt readability, maintainability and debugging.

                              There also used to be 2 basic loop structures - one guaranteed at least 1 loop iteration (repeat..until) and the other 0 loop iteration (while..) and this also played a role in where and when the FETCH done in the loop.

                              I still stick to the same basic principles - not duplicating calls, especially FETCH like calls, in a code unit. Unfortunately, PL/SQL as a language lacks a couple of basic programming structures - like a repeat loop and like resource protection blocks. And using cursor variables like NotFound, instead of returning that value as the result of a function call.

                              PL/SQL provides a default infinite loop structure that requires the EXIT clause to be used - and this allows multiple exits at different points in the loop structure.

                              Granted, this can be a problem - if abused. Especially in loops that contains a lot of code that is not modularised and processing/conditional logic being hacked via multiple EXIT clauses scattered all over the code in the loop.

                              But I do not see it as a problem in well designed/written loops - like a cursor loop that typically has a single EXIT clause (at the top part of the loop for single cursor fetches, and at the bottom part for bulk fetches).

                              So until the following is possible in PL/SQL, I will use the EXIT clause loop structure instead.. :-)
                              while ( fetch <cursor_variable> [bulk] collect into var1[,var2..]  )
                              loop
                                .. loop processing..
                              end loop;
                              • 12. Re: REF CURSOR PROBLEM
                                Billy~Verreynne
                                Mr. Murali wrote:
                                Please see the example i worked on oracle version 11.2.0.2.0
                                Makes no sense to me...

                                What does the variable I represent? How does it factor into the conditional processing of the fetch loop?

                                A fetch loop processing should be based on the current row(s) fetched - that determines what needs to be done. Not some arbitrary counter variable that is set externally. The data fetched should be driving the loop.

                                If you do need a counter for processing fetched rows, that can be created using analytical function.

                                Opening other cursors inside a cursor loop - or closing and re-opening the same cursor... this never makes any sense.
                                • 13. Re: REF CURSOR PROBLEM
                                  635362
                                  Create Or Replace Procedure example3 Is
                                  Type Cur_Ref Is Ref Cursor;
                                   
                                  V_Cur_Ref       Cur_Ref;
                                  V_Str           Varchar2(1000);
                                  V_Cur_Nec       emp%rowtype;
                                   
                                  Stmt            Varchar2(1000)  :=  'Select * From Emp E where rownum <=1 ';   
                                  I               Number := 0 ;
                                   
                                  Begin
                                   
                                     Open V_Cur_Ref For Stmt;
                                     Fetch V_Cur_Ref Into V_Cur_Nec;
                                     Dbms_Output.Put_Line(' -- Before Loop -- ');
                                     Loop
                                          If V_Cur_Ref%Isopen Then
                                             Exit When V_Cur_Ref%Notfound;
                                           End If;
                                  
                                        Dbms_Output.Put_Line(' -- Loop  5  -- ');
                                        If V_Cur_Ref%Isopen Then
                                           Fetch V_Cur_Ref Into V_Cur_Nec;
                                           Dbms_Output.Put_Line(' -- Loop  6  -- ');
                                        End If;
                                        
                                     End Loop;
                                     Dbms_Output.Put_Line(' -- After Loop-- ');
                                     Close V_Cur_Ref;
                                  End;
                                  procedure running successfully prior to version 11.2.0.2.0 in this version it is hanging.
                                  i given I to check behaviour of of ref cursor
                                  • 14. Re: REF CURSOR PROBLEM
                                    sybrand_b
                                    Why do you still include this crap code to check everytime whether the cursor is open before you fetch?
                                    Trying to get your code as whacky and as slow as possible apparently?
                                    When will you learn to write structured code and to follow up sound recommendations?
                                    Why do you state 'it hangs' without checking from a different session where it is waiting for

                                    This forum has no intention to do your work for free, and to hold your hand, where you all the time don't cooperate and provide no information at all!! 

                                    -----------
                                    Sybrand Bakker
                                    Senior Oracle DBA
                                    1 2 Previous Next