title: Getting Stale Connection using OracleDataSource with OCI driver
body: <p>I am getting stale connection error when there is no requests to the database from my java application for couple of hours. </p>
  
  <p>Its a simple java application runned on Linux box with OCI (type driver). Dont ask me why OCI, why not thin. I am using <code>OracleDataSource</code> and <code>OracleConnectionCacheManager</code> for maintaining the cache of connection objects. Here is the code snippet: </p>
  
  <pre><code>import java.sql.Connection; 
  import java.sql.SQLException; 
  import java.util.Properties; 
  
  import oracle.jdbc.pool.OracleConnectionCacheManager; 
  import oracle.jdbc.pool.OracleDataSource; 
  
  import org.apache.log4j.Logger; 
  
  import com.exception.DataException; 
  
  public class ConnectionManager { 
  private static OracleDataSource poolDataSource = null; 
  private final static String CACHE_NAME = "CONNECTION_POOL_CACHE"; 
  private static OracleConnectionCacheManager occm = null; 
  
  public static void init(String url,String userId,String password) throws PCTDataException{ 
  Properties cacheProps = null; 
  try { 
  poolDataSource = new OracleDataSource(); 
  poolDataSource.setURL(url); 
  poolDataSource.setUser(userId); 
  poolDataSource.setPassword(password); 
  
  cacheProps = new Properties(); 
  cacheProps.setProperty("MinLimit", "1"); 
  cacheProps.setProperty("MaxLimit", "5"); 
  cacheProps.setProperty("InitialLimit", "1"); 
  cacheProps.setProperty("ValidateConnection", "true"); 
  
  poolDataSource.setConnectionCachingEnabled(true); 
  occm = OracleConnectionCacheManager.getConnectionCacheManagerInstance(); 
  occm.createCache(CACHE_NAME, poolDataSource, cacheProps); 
  occm.enableCache(CACHE_NAME); 
  } catch (SQLException se) { 
  throw new DataException("SQL Exception while initializing connection pool"); 
  }catch(Exception e){ 
  throw new DataException("Exception while initializing connection pool"); 
  } 
  } 
  
  public static Connection getConnection() throws PCTDataException { 
  try{ 
  if (poolDataSource == null) { 
  throw new SQLException("OracleDataSource is null."); 
  } 
  occm.refreshCache(CACHE_NAME, OracleConnectionCacheManager.REFRESH_INVALID_CONNECTIONS); 
  Connection connection = poolDataSource.getConnection(); 
  return connection; 
  }catch(SQLException se){ 
  se.printStackTrace(); 
  throw new DataException("Exception while getting Connection object"); 
  }catch(Exception e){ 
  e.printStackTrace(); 
  throw new DataException("Exception while getting Connection object"); 
  } 
  } 
  
  public static void closePooledConnections() { 
  try{ 
  if (poolDataSource != null) { 
  poolDataSource.close(); 
  } 
  }catch(SQLException se){ 
  }catch(Exception e){ 
  } 
  
  } 
  } 
  </code></pre>
  
  <p>The error is as follows: </p>
  
  <pre><code>ConnectionManager.java:getConnection:87 - Exception while getting Connection object: 
  java.sql.SQLException: Invalid or Stale Connection found in the Connection Cache 
  at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) 
  at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) 
  at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) 
  at oracle.jdbc.pool.OracleImplicitConnectionCache.getConnection(OracleImplicitConnectionCache.java:390) 
  at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:404) 
  at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:189) 
  </code></pre>
  
  <p>What am I missing?</p>