You are right on that demerit. The way I resolved it, is by a combination of ResetJDBCConnection and FindConnectionAsBroken Svc.
I have been using this set of services to monitor all my jdbc connections for past year.
Here is the brief gist of the solution: (I will upload a package if I find sufficient time to do so in the next week)
Create a Adpt Svc UniversalSelectStatement (for me, it goes like “select count(0) from systypes” - this works for Db2 and SQL Server. Check to see if it works for Oracle also or not. If you are only using Oracle, try a simpler statement as “select sysdate from dual”)
Create a Svc MonitorJDBCConnectionsHealth which iterates over all the adapter connections created (using listAllResources)
In this Svc, loop over all the connections to get the current state
run the UniversalSelectStatement svc and see if any errors occur;
if errors occur, implies something wrong with the connection; set it to disabled state
if no errors, exit flow
try to set the connection to enabled state;
if error, retry after a time delay
if no error, PROBLEM SOLVED
Remember to be able to run the UniversalSelectStatement on different adapter connections, you would use the setAdapterServiceNodeConnection service.
Once completely developed, set MonitorJDBCConnectionsHealth on scheduler for 15 minutes.
Hope this clarifies the solution for you a little bit. Remember to test it extensively before putting any solution in production environment.
Let me know if you have any questions.