We are using hibernate's c3p0 connection provider in order to interact with the DB from our application.
In case of Oracle RAC failover scenario, whenever the transaction is established and failover happens the DB throws this exception: org.hibernate.exception.GenericJDBCException: Cannot open connection
I would like to know the SQL error code for this exception and based on that we would like to implement the retry logic in our application.
If a RAC failover happens in the middle of a transaction (i.e. no commit has happened) then the transaction is automatically rolled back by the database. The application must reconnect or switch to a pooled connection from a working instance (the original session to the failed instance will not be usable) and replay the transaction.
As there are a variety of reasons for a RAC failover event, not all of which are even visible within the database, there isn't any one specific SQL code to watch for. Oracle Grid Infrastructure can generate messages using Fast Application Notification or Fast Connection Failover paradigms to applications so that they can reconnect as necessary.
See the documentation on application continuity for more: https://docs.oracle.com/en/database/oracle/oracle-database/19/racad/ensuring-application-continuity.html