With database/sql, cursors stay open about ora HOT 8 CLOSED

See 7601cc0 (Test_open_cursors_db) for a reproducing case.

from ora.

I do suspect the driver of keeping open cursors, but am unsure if it is. It certainly sounds like it. I initially looked into it but wasn't able to find out how to solve it.

from ora.

It is the same with non-driver code: see Test_open_cursors in z_session_test.go

Why is this automatic ResultSet management? I'm not sure that not that's the problem's source...

from ora.

I think you're right. Its a general issue not specific to database/SQL.
Automatic rset management was added for database/SQL and general ease of
use. If auto management is the issue it should be fixed or removed.

It might be related to rset pooling or at the oci level. When I looked at
open cursors in oracle they would be reduced after time. So, does that mean
the driver opens them quickly and oracle eventually cleans them up? Or
is there a leak?

I will have a closer look at test_open_cursors.

On Tue, Jun 2, 2015, 2:39 AM Tamás Gulácsi [email protected] wrote:

It is the same with non-driver code: see Test_open_cursors in
z_session_test.go

Why is this automatic ResultSet management? I'm not sure that not that's
the problem's source...

—
Reply to this email directly or view it on GitHub
https://github.com/rana/ora/issues/21#issuecomment-107893918.

from ora.

I've added Test_open_cursors_db to goracle, and there it passes.
Goracle uses OCIStmtPrepare2 & OCIStmtRelease; I've tried to add it to ora, but didn't work: after OCIStmtPrepare2 (instead of OCIStmtPrepare), I get ORA-24337: statement handle not prepared.
In retrospect, maybe that's because of how I declared the pointer to ocistmt.

Now I've tried it again, and WORKS! See my PR.

from ora.

Awesome!

from ora.

I was reviewing the open cursor issue, docs and tests.

Statement Caching in OCI says that OCIStmtPrepare2 must be followed with a call to OCIStmtRelease. And according to Oracle, "A call to OCIStmtPrepare2(), even if the session does not have a statement cache, also allocates the statement handle. Therefore, applications using only OCIStmtPrepare2() must not call OCIHandleAlloc() for the statement handle."

Separately, OCIStmtPrepare can be followed by a call to OCIHandleFree.

If you want to read more about it, the Oracle docs have some good guidance on the matter.

I also fixed a bug in Test_open_cursors to use 'opened cursors current' STATISTIC#=5 to determine the number of open cursors. I think this issue is closed due to your bug fix. In my test, The number of open cursors was 2 before the test and 3 after the test. The increase in cursor count was due to querying the number of cursors.

So your code fixed the issue. Thank you!

from ora.

I've read that paragraph several times, but it doesn't say why an OCIStmtPrepare + OCIHandleFree would leave cursors open...

Anyway, thanks for the cleanup!

from ora.