Cursor limit reached

From: Sam Jordan <sjo_at_spin.ch>
Date: Tue, 18 May 1999 07:31:33 +0000
Message-ID: <37411755.16518371@spin.ch>

Hi

Apparently a lot of people seem to have problems with cursors which aren't closed for some obscure reason. I'm joining the team now...

I'm calling a stored procedure from a Java application several times, and after the n-th time (n always the same and matches the open_cursors value) I get the error saying that the maximal amount of cursors have been exceeded.

I don't commit the transaction after every procedure call, but want to commit resp. rollback at last.

The procedure (to be accurate, it's a function) basically looks like:

function myfunc(...) return integer is

	cursor MYCUR (p integer) is
		select * from yyy where ...

-- misc variable declarations

begin
-- here a lot of constructs like:

	begin
		select xxx into myvar from yyy where...
	exception
		when NO_DATA_FOUND then
			raise_application_error(...)
	end;

--

open MYCUR (param);
fetch MYCUR into myRec;
if (MYCUR%rowcount > 0) then

	close MYCUR;
	update www set ...
	return n;
else
	close MYCUR;
	insert into www values ...
	return m;

end if;
end;

Right now the procedure always enters the 'then' case and performs the 'update'. The error is then thrown in a trigger which is fired upon the update operation.

The trigger does only perform some 'select xxx into myvar from yyy' statements and stores the results into some package variables. One of these select statements seems to cause the open-cursor error.

I really can't see any cursors which aren't closed. But all this looks as the 'close MYCUR' statement doesn't really close the cursor, because it's definitely the only explicit cursor used in all these operations.

Maybe some people had similar problems and have found the reason for them?
Are there possibilities to print out the amount of open cursors in a stored procedure at any time for debugging purposes?

bye
--

Sam Jordan Received on Tue May 18 1999 - 02:31:33 CDT