Re: ORA-01000: maximum open cursors exceeded

Edi Weitz wrote:
> [...]
> so it looks as if CLSQL doesn't release all OCI resources in case of
> a database error.
> 
> I can try to investigate this but maybe someone else knows the code
> good enough to find the problem immediately.

I believe I see the problem, though the solution will take some work.
First, the routine HANDLE-OCI-ERROR in sql/oracle-sql.lisp doesn't
free errbuf before invoking error. I've fixed that, but that isn't
causing your trouble.

I believe the trouble comes from the design of the API. In
oracle-api.lisp, the macro DEF-OCI-ROUTINE adds error handling to the
results of every function. Thus, the results of every OCI invocation
is automatically check and the proper type of error is
signaled. However, this design fails to note any allocated resources
that should be freed before signaling an error.

In your example, I believe the issue is in SQL-STMT-EXEC in
oracle-sql.lisp where OCI-HANDLE-FREE is not being called if an error
occurs in OCI-STMT-EXECUTE.

A quick way around this could be to add an unwind protect to
SQL-STMT-EXEC to ensure the handle is freed. A more sophisticated
approach would be to add a macro wrapper to a group of OCI statements
where resources could be added to a list which is automatically
freed in an unwind-protect.

I'm releasing version 3.2.2 now with the simple solution (and a few
minor UFFI foreign object cleanups. Perhaps you wouldn't mind testing
it.

Kevin




-- 
Kevin Rosenberg
kevin-HJRc7zDS/[email protected]