With check-valid-connection-sql, you place a SQL statement in *-ds.xml and that statement is run. If there is no error, it is assumed that the connection is valid.
With valid-connection-checker-class-name, you write a class that performs a connection check. This class can do anything. For example, it might query certain tables to ensure that the expected data exists, or perform a benign update to ensure that the account used has update right (or doesn't have update rights!)
So the question is, would running a simple SQL statement be sufficient to verify the connection, or do you need to do something more complex?
Thats for the reply Peter. My requirement is simple - I want the db connection to be tested by JBoss prior to handing it over to the application. This is easily achieved by the former check-valid-connection-sql statement.
However, in certain cases (say Oracle package changed and JBoss start's receiving ORA-04062) we need to stop and start JBoss even with the above statement. I am wondering whether replacing it with valid-connection-checker-class-name will help resolve this issue and save us from a restart everytime something on Oracle side changes.
Of course, I will try this out ; but wanted to understand the inner workings before blindly changing it. The documentation is not very detailed in this regard.
Looking at the documentation on that Oracle error, it would appear that because of a database change that the current connection(s) are no longer valid and I guess that what you are looking for is some way of closing the existing connections and reestablishing them. I have seen requests for this reconnect capability before, but I do not think it is currently possibly.
And to answer your specific question, using valid-connection-checker-class-name will not help you resolve this issue.
I had another thought. The issue appears to have something to do with timestamps assigned to stored procedures. All of the posts I have seen on this recommend recompiling the client code so that the client picks up the new timestamps. Well, in Java, I suspect that the Oracle JDBC driver loads the timestamps. If the Oracle JDBC driver provides a way to reload the timestamps, then if you wrote a valid-connection-checker-class-name class, you could have it make that Oracle JDBC driver call to reload the timestamps. So the question then becomes, does the Oracle JDBC driver provide such a feature?