Forum OpenACS Q&A: Response to ORA-01000: maximum open cursors exceeded ??
begin v_result := ''; v_started := 'f'; open v_package_cur; loop 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.