ORA-06511: PL/SQL: cursor already open. I am closing my cursor but no luck

Question

I am getting the ORA-06511: PL/SQL: cursor already open ERROR.

Not sure why I am getting this error since I am closing my cursor.

Please see code below.

BEGIN 

OPEN findem; ---OPENING HERE!
FOR crfindem IN findem LOOP
 
    
    FETCH findem into other1, other2, other3;
    

    l_CollectionOfRows(Counter).tmps_key := other1;
    l_CollectionOfRows(Counter).tmps_cfb_rate := other2;
    l_CollectionOfRows(Counter).tmps_engagement_pay_rate := other3;
    Counter :=  Counter + 1;
END LOOP;
CLOSE findem;---CLOSING HERE!



FORALL i IN l_CollectionOfRows.FIRST .. l_CollectionOfRows.LAST
UPDATE Base.Table               
SET MARGIN = :PAGE56_MARGIN,
PERCENT = :PAGE56_MARGIN + l_CollectionOfRows(i).rate,
PAY_RATE = (l_CollectionOfRows(i).pay_rate *  (:PAGE56_MARGIN + l_CollectionOfRows(i).rate)) + l_CollectionOfRows(i).pay_rate
         WHERE tmps_key = l_CollectionOfRows(i).tmps_key;




END;

I read from some online threads that for every Insert/Update statement, Oracle will create an implicit cursor. If this is the case how do you treat those implicit cursors that Oracle creates?

Answer 1

You are getting that error because you are opening the same cursor twice: the FOR construct already does all these things for you:

1. FOR opens the cursor
2. FOR implicitly declares a record variable (the one named crfindem, in your code) that will receive the values for each row read from the cursor
3. FOR loops on every row and assigns the values of the current row to the crfindem variable
4. FOR automatically closes the cursor at the end of the loop

so you don't need any OPEN/CLOSE/FETCH .. INTO commands if you are using a FOR loop:

see this simple example: itjust works.

declare
   cursor cur is select * from user_tab_comments;
begin
  for c in cur loop
     DBMS_OUTPUT.PUT_LINE( c.table_name || ' - ' || c.comments);  
  end  loop;
end;

but if i try to open the cursor before using the for loop, I will get your same error because the cursor is already open and the for construct is trying to open it again:

declare
   cursor cur is select * from user_tab_comments;
begin
  open cur; -- this is not needed and will cause problems
  for c in cur loop --! ERROR: here I am trying to open AGAIN the same cursor
     DBMS_OUTPUT.PUT_LINE( c.table_name || ' - ' || c.comments);  
  end  loop;
end;

so, you either must choose if you want to write this code:

 declare
    cursor cur is select * from user_tab_comments;
 begin
    for c in cur loop
       DBMS_OUTPUT.PUT_LINE( c.table_name || ' - ' || c.comments);  
    end  loop;
 end;

or avoid using the FOR construct and do all the open/fetch/close operations by yourself, by writing this:

 declare                          
    -- I cant' use "select *" here:
    -- if I use "fetch into" to a precise list of variables, 
    -- I have to extract exactly the fields I want to assign: 
    
    cursor cur is 
       select table_name,comments 
    from user_tab_comments;
    tabname varchar(100);
    tabcomment varchar2(4000);
 begin
    open cur;
    loop
       fetch cur into tabname,tabcomment;
       exit when cur%notfound;
       DBMS_OUTPUT.PUT_LINE( tabname || ' - ' || tabcomment);  
    end loop;
    close cur;
 end;
 

Your error is that your code is trying to do both these things at the same time.

you should have written this:

  --  OPEN findem; NO NEED TO OPEN THE CURSOR  (when using FOR)
  FOR crfindem IN findem LOOP
      
      --- FETCH findem into other1, other2, other3; FOR ALREADY DOES THIS: the values are in crfindem
      

      l_CollectionOfRows(Counter).tmps_key := crfindem.name_of_the_first_field;
      l_CollectionOfRows(Counter).tmps_cfb_rate := crfindem.name_of_the_second_field;
      l_CollectionOfRows(Counter).tmps_engagement_pay_rate := crfindem.name_of_the_third_field;
      Counter :=  Counter + 1;
  END LOOP;
  --- CLOSE findem; NO NEED TO CLOSE THE CURSOR (when using FOR)

Now let me add some considerations about your code (and about this example):

1. I don't see where you initialize your Counter variable: you MUST initialize it to 0 before entering the loop, because otherwise its initial value will be NULL and will stay null for the whole operation because (NULL + 1) evaluates again to NULL.

2. I don't see how your cursor is declared, so I don't know the names of the fields it extracts. in the code above I used the "fake" names name_of_the_first_field, name_of_the_second_field, name_of_the_third_field... but you must use the correct field names returned by your query

3. if your cursor returns some calculated value (like "select 1+2, sysdate, null from dual") you must assign a name to the calculated column to make it accessible by giving an alias to each calculated column you extract ("select 1+2 AS first_name, sysdate AS second_name, null as third_name from dual")

Edit... another info: you don't really need to declare a variable for each field even when you are explicitly using open/fetch/close: you can declare a RECORD variable (that will contain all column values with the same column names, exactly like the for loop does) by using the %ROWTYPE syntax. my example becomes like this, using %rowtype:

 declare                          
    cursor cur is select * from user_tab_comments;
               
    -- here I am declaring a variable named c that is a RECORD variable:
    -- it can contain a whole row returned by cursor cur
    c cur%rowtype; 

 begin
    open cur;
    loop
       fetch cur into c;
       exit when cur%notfound;
       DBMS_OUTPUT.PUT_LINE( c.table_name || ' - ' || c.comments);  
    end loop;
    close cur;
 end;