Hi,

Running the JUnit tests that comes with Hibernate 2.1 I (using DBCP) always run into "java.sql.SQLException: ORA-01000: maximum open cursors exceeded" in the FooBarTest (and others). I have set hibernate.statement_cache.size=0 as the FAQ recommends and checked that the setting is indeed set when in the test's log file:

10:24:12,318 INFO Environment:466 - loaded properties from resource hibernate.properties: {hibernate.connection.password=test, hibernate.query.substitutions=true 1, false
0, yes 'Y', no 'N', hibernate.dbcp.whenExhaustedAction=1, hibernate.show_sql=true, hibernate.proxool.pool_alias=pool1, hibernate.jdbc.batch_size=0, hibernate.dbcp.testOnBorrow=true, hibernate.dbcp.testOnReturn=false, hibernate.dbcp.validationQuery=select 1 from dual, hibernate.dbcp.ps.maxActive=100, hibernate.max_fetch_depth=1, hibernate.jdbc.use_streams_for_binary=true, hibernate.statement_cache.size=0 , hibernate.connection.pool_size=2....

The specific Oracle 9.2.0.1 instance is configured with "open_cursors=300"

Having a very brief look at the source it's not immediately clear what hibernate does with said property (if anything):

[root@rde-harp-project-wireless hibernate]# find . | grep -i "\.java$" | xargs grep -i statement_cache
./cfg/Environment.java: public static final String STATEMENT_CACHE_SIZE ="hibernate.statement_cache.size";
./cfg/Environment.java: private static final String STATEMENT_CACHE_SIZE_OLD ="hibernate.statement_cache_size";
./cfg/Environment.java: OBSOLETE_PROPERTIES.put(STATEMENT_CACHE_SIZE_OLD, STATEMENT_CACHE_SIZE);
./dialect/InterbaseDialect.java: getDefaultProperties().setProperty(Environment.STATEMENT_CACHE_SIZE, "0");
[root@rde-harp-project-wireless hibernate]#

In the Oracle 9i JDBC development guide I see the following:

((OracleConnection) conn).setStatementCacheSize(5);

// Enable Implicit caching
((OracleConnection) conn).setImplicitCachingEnabled(true);
((OracleConnection) conn).setExplicitCachingEnabled(true);

I guess those needs to be set to 0 and false respectively.

Any ideas/comments?

Thank you,
Peter

Hibernate no longer has a built-in statement cache. It lets the connection pool to statement caching.

Thanks for the quick reply Gavin! Do you know offhand if DBCP let's one turn statement caching off? It doesn't look like it at first glance.

Thanks much,
Peter van Rensburg

Should do ... just don't supply the hibernate.dbcp.ps.* properties.

Thanks again Gavin. DBCP seemed a bit unhappy without those options, setting the maxActive to 0 did not help either. Ah well, just resorted to using c3p0, which seems to work just fine.

A few blog entries here and there indicates the lack of statement cache control in DBCP for what it's worth.

Cheers,
Peter

c3p0 is a better implementation


however, dbcp *should* work w/o statement cache - it *could* be a problem with our DBCPConnectionProvider; perhaps you should take a closer look