title: JDBC Oracle Thin ORA-02396 Connection idle timeout
body: <p>I am supporting some legacy code and it's chugged along fine until recently.  I am looking for if there is a setting for JDBC Oracle thin connection where I can specify idle timeout via Java (no connection pooling)?  A lot of resources online refer to connection pooling... is it even possible in my case (to specify idle timeout, in a non-pooling situation)?  Or is idle time a setting on the specific DB user account?</p>
  
  <p><strong>Updates + Questions</strong></p>
  
  <ol>
  <li><p>I was able to log in as the user, and ran a query to try to find out resource limits. <code>select * from USER_RESOURCE_LIMITS;</code> However everything came back "UNLIMITED".  Is it possible for another value (say from the JDBC connection) to override the "UNLIMITED"? </p></li>
  <li><p>So the job holds onto the connection, while we actively query another system via DB links for a good duration of ~2+ hours... Now, why would the idle timeout even come into play? </p></li>
  </ol>
  
  <p><strong>Update #2</strong></p>
  
  <p>We switched to a different account (that has the same kind of DB link setup) and the job was able to finish like it did before. Which sort of points to something wonky with the Oracle user profile?  But like I said, querying <code>USER_RESOURCE_LIMITS</code> shows both users to have "UNLIMITED" idle time. DBA pretty confirmed that too.  What else could be causing this difference? </p>
  
  <p><strong>Update #3</strong></p>
  
  <p>Stack trace and such.</p>
  
  <pre><code>java.sql.SQLException: ORA-02396: exceeded maximum idle time, please connect again
  ORA-06512: at line 1
  
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)
      at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:184)
      at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:873)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1086)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2984)
      at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3076)
      at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4273)
      at com.grocery.stand.Helper.getAccess(Helper.java:216)
      at com.grocery.stand.fruitbasket.Dao.getPriceData(Dao.java:216)
      at com.grocery.stand.fruitbasket.Dao.getPricees(Dao.java:183)
      at com.grocery.stand.fruitbasket.UpdatePrice.updateAllFruitPrices(UpdatePrice.java:256)
      at com.grocery.stand.fruitbasket.UpdatePrice.main(UpdatePrice.java:58)
  SQL Exception while getting Data from SYSTEM_B
  Exception while updating pricing : ORA-01012: not logged on
  
  Exception in thread "main" java.sql.SQLException: ORA-01012: not logged on
  
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:277)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
      at oracle.jdbc.driver.T4C7Ocommoncall.receive(T4C7Ocommoncall.java:129)
      at oracle.jdbc.driver.T4CConnection.do_rollback(T4CConnection.java:478)
      at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:1045)
      at com.grocery.stand.Helper.rollBack(Helper.java:75)
      at com.grocery.stand.fruitbasket.UpdatePrice.updatePartNumbers(UpdatePrice.java:291)
      at com.grocery.stand.fruitbasket.UpdatePrice.main(UpdatePrice.java:58)
  </code></pre>
  
  <p><strong>Connection Code</strong></p>
  
  <pre><code>    public  static Connection openConnection() throws SQLException {
  
  
      String userName = propBundle.getString(DB_UID);
      String password = propBundle.getString(DB_PWD);
      String url = propBundle.getString(DB_URL);
  
  
      Connection conn = null;
      try {
          DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
          conn = (Connection) DriverManager.getConnection(url, userName,
                  password);
          conn.setAutoCommit(false);
      } catch (SQLException sqle) {
          sqle.printStackTrace(System.out);
          throw sqle;
      }
      return conn;
  }
  </code></pre>
  
  <p><strong>Error occurs on line <code>execute()</code></strong></p>
  
  <pre><code>public static void getSystemAccess(Connection dbConnection) throws SQLException {
    try {
        CallableStatement authStmt = null;
        String authorize = "CALL ABC.ACCESS_PROCEDURE@some_db_link()";
        authStmt = dbConnection.prepareCall(authorize);
        authStmt.execute();
        authStmt.close();
    } catch (SQLException sqle1) {
        sqle1.printStackTrace();
        throw new SQLException(sqle1.getMessage());
    }
  }
  </code></pre>