How to mark a connection for eviction from the pool in Tomcat 6

Question

We have an application using an Oracle StoredProc. When the stored proc is upgraded or recompiled from outside of our application, the database connections have to be closed and reopened. Otherwise we get an Oracle Exception ORA-20003.

Until now, we were using a WebLogic specific solution, where we were throwing a specific Exception and the Weblogic connection pool would mark the connection for eviction. We are now moving to Tomcat 6.

Is there a similar solution for Tomcat 6 ? Or even better, a generic solution ?

We could configure our connection pool with minIdle=0 and timeBetweenEvictionRunsMillis=some small number, but there still would be no garantie, only a mitigation of the problem.

Thanks for your help !

Answer 1

Can you come up with a SELECT statement that would validate whether the connection is up to date? If so, you could use it to configure the connection pool with validationQuery (potentially combined with connectionInitSqls).

Edit: Perhaps USER_OBJECTS.LAST_DDL_TIME can be of some help?