Forum OpenACS Q&A: Response to ORA-01000: maximum open cursors exceeded ??

Collapse
Posted by Tom Jackson on

You definitely should not close the cursor inside the loop. You should close it just outside the loop, since that is when you are done with it. Also, you should get an error if you try to open an already open cursor, so you should check for that before you open the cursor. Occasionally you get an error in your loop. You should use the exception block to check for and close an open cursor (Just remembering that you might have to comment out the exception block to find bugs in your code)

  --
  -- Open the cursor
  --
  if not my_cursor%ISOPEN
  then
   OPEN my_cursor;
  end if;
  --
  -- Begin Looping through records
  --
  loop
   FETCH my_cursor INTO v_my_cursor_var;
   EXIT WHEN my_cursor%NOTFOUND;
   --
   -- Do some work
   --
   null;
  end loop;
  CLOSE my_cursor;
  exception 
   when others
   then
     if my_cursor%ISOPEN
     then
       CLOSE my_cursor;
     end if;