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

Posted by Dan Wickstrom on
One things I noticed about the openacs-4 code was the inconsistent way in which cursors were closed. For example, this code in doc-package.sql seems to imply that the cursor is closed when NOTFOUND occurs:

    v_result := '';
    v_started := 'f';

    open v_package_cur;
      fetch v_package_cur into v_line, v_text; 
      exit when v_package_cur%NOTFOUND;
      -- Look for the function header
      if v_started = 'f' then
        if v_text like '--%' then
          v_started := 't';
        end if;
      end if;
      -- Process the header
      if v_started = 't' then

        if v_text not like '--%' then
          close v_package_cur;
          return v_result;
        end if;
        v_result := v_result || v_text;
      end if;
    end loop;
    -- Return unfinished result
    return v_result;     

  end get_package_header;

end doc;
show errors

As you can see, the cursor is closed prior to returning from the loop, but if a NOTFOUND condition occurs, the loop and then the routine is exited without closing the cursor. To test this, I setup a simple test case that emulated this code, and I explicitly closed the cursor after the NOTFOUND condition occured. This worked without throwing an error, so, as Don pointed out to me, the cursor must have still been open, since closing an already closed cursor will cause an error.