JDBC Oracle Thin ORA-02396 Connection idle timeout

Question

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?

Updates + Questions

1. I was able to log in as the user, and ran a query to try to find out resource limits. select * from USER_RESOURCE_LIMITS; However everything came back "UNLIMITED". Is it possible for another value (say from the JDBC connection) to override the "UNLIMITED"?

2. 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?

Update #2

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 USER_RESOURCE_LIMITS shows both users to have "UNLIMITED" idle time. DBA pretty confirmed that too. What else could be causing this difference?

Update #3

Stack trace and such.

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)

Connection 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;
}

Error occurs on line execute()

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());
  }
}

Answer 1

I'm not sure that I understand the question you're asking.

The error you are getting indicates that the Oracle user that you are using to connect to the database has a profile configured (in Oracle) that limits the amount of time the connection can be idle. Oracle is killing your connection when the connection remains idle too long. Normally, the solution to this sort of problem would be to go to the DBA and ask for the idle time to be increased or to look through your code and see why the connection is open and unused for so long. If you were using a connection pool (which it doesn't appear you are), it would make sense for some connections to remain open and idle for long periods of time. Since it doesn't appear that you are using a connection pool, the question is whether it makes sense for the application to hold open the connection for long periods of time without doing anything. If the application opens a connection when the user logs in at 9am and doesn't close it until the user shuts down at 5pm, it may make sense to adjust the IDLE_TIME setting for this user in the database. Otherwise, you may want to investigate whether it makes logical sense for the application to hold open the database connection so long without doing something or whether the application can be modified to close the connection when it is no longer needed.