hi,

I use hibernate 1.2.3 and I have the error too many opened cursors.
I try to set the param statement_cache.size to 10 by using hibernates files configurations but it do not solve my probl

There is a post on the forum which discusses this in detail. It had to do with the way Oracle drivers decide to release server-side cursors when using prepared statements. Basically, if I remember correctly, the oracle drivers do not release the cursors until that prepared statement is reused.

Search for that post for the resolution.

I saw the topic "cursors stay open when using session.iterate" but it's not the context of my application.

My configuration :
Hibernate 1.2.3
Oracle 9i (classes12.jar)
Jdk 1.3.1

I try to keep the same session to retrieve a large amount of data. I use Iterate and Load methods on one session object and I have more than 300 opened cursors ( default limit in Oracle).

I didn't want to close the session ( I know that this should close all the statements as explained in the topic "cursors stay open when using session.iterate") to keep the cache on.

Do I miss something on this topic ?

I have found a way to close the statement after each load or iterate call :
((SessionImplementor) hibernateSession).getBatcher().closeStatements();

I am testing the effects on my application. It seems that it close correctly the session statements.

Do you think that this could cause problems ?

Thanks in advance

How long are you keeping this session? Sessions are designed to be relatively short lived (typically the execution life of a single use-case). Maintaining a session longer (without at the very least disconnecting/reconnecting it) will have these types of effects on the system.

If you need longer term caching, look at utilizing the plugable cache providers which are meant to span session usage.

My session have a short lifecycle, I hope it will not exceded 30 seconds.

My session read a large amount of data, so I make a lot of request on the database with load and iterate methods from one session.

I need cache only during the lifecycle of my session.

I made my test with
((SessionImplementor) hibernateSession).getBatcher().closeStatements();
and it seems to wook perfectly.

Try the latest release of 2.1, which releases PreparedStatements more aggressively (and make sure the connection pool itself is not caching PreparedStatements).