Catch Oracle timeout exceptions

Question

I want to handle timeout exceptions differently from other SQL exceptions, much like How to catch SQLServer timeout exceptions

However, our application supports both Oracle and MSSqlserver.

The solution would idealy cover both providers: System.Data.OracleClient and Oracle.DataAccess.Client.

What are the error codes for the exceptions that are thrown by those?

Answer 1

What I ended up with was similar to this:

However, the errorCode for TimeOut seems to be 01013

    /// <summary>
    ///     Catches network problems for oracle connections and clears the session pool of invalid connections
    /// </summary>
    /// <param name="ex"></param>
    /// <param name="triedBefore"></param>
    /// <returns></returns>
    private bool reconnectOracle(DbException ex)
    {
        var exType = ex.GetType();
        if (exType.FullName == "Oracle.DataAccess.Client.OracleException")
        {
            dynamic exOra = ex;
            int errorNo = exOra.Number;
            // Check for oracle network error numbers
            if (errorNo == 03113 ||
                errorNo == 03114 || 
                errorNo == 03135) // **Timeout seems to be 01013**
            {
                AL.Warn("Connection is broken. Error number: {0}. Attempting reconnect.", errorNo);
                // Network error, close connection
                Command.Connection.Close();

                // All connections in the pool are probably invalid. Ensure that all connections are cleared
                dynamic con = new StaticMembersDynamicWrapper(Command.Connection.GetType());
                con.ClearAllPools();

                // Ensure that new connection object is given
                Command.Connection = null;

                return true;
            }
        }
        return false;
    }

StaticMembersDynamicWrapper as explained in: http://blogs.msdn.com/b/davidebb/archive/2009/10/23/using-c-dynamic-to-call-static-members.aspx

I did not want to have a hard reference to Oracle assemblies, so I used this instead.