0

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?

Community
  • 1
  • 1
Alex AIT
  • 17,361
  • 3
  • 36
  • 73

2 Answers2

0

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.

Alex AIT
  • 17,361
  • 3
  • 36
  • 73
0

Using void setQueryTimeout(int seconds) throws SQLException; might work; but it might cause thread creation for every sql executed with Oracle jdbc driver, so use with caution.

/**
 * Sets the number of seconds the driver will wait for a 
 * <code>Statement</code> object to execute to the given number of seconds.
 * If the limit is exceeded, an <code>SQLException</code> is thrown. A JDBC
 * driver must apply this limit to the <code>execute</code>,
 * <code>executeQuery</code> and <code>executeUpdate</code> methods. JDBC driver
 * implementations may also apply this limit to <code>ResultSet</code> methods
 * (consult your driver vendor documentation for details).
 *
 * @param seconds the new query timeout limit in seconds; zero means 
 *        there is no limit
 * @exception SQLException if a database access error occurs, 
 * this method is called on a closed <code>Statement</code>
 *            or the condition seconds >= 0 is not satisfied
 * @see #getQueryTimeout
 */
void setQueryTimeout(int seconds) throws SQLException;
gokhant
  • 96
  • 1
  • 3