TomEE-Oracle connectivity: Connection has already been closed

Question

I have some database insert/update operations which take more than 10 mins to get completed as they work with very large datasets.

I have the following configuration in TomEE for the data source:

<Resource id="BSLDataSource" type="javax.sql.DataSource">
    DefaultAutoCommit = false
    JdbcDriver = oracle.jdbc.OracleDriver
    JdbcUrl = jdbc:oracle:thin:@host:port:sid
    JmxEnabled = true
    JtaManaged = true
    LogAbandoned = true
    MaxActive = 500
    MaxIdle = 5
    MaxWait = 10000
    MinEvictableIdleTimeMillis = 1800000
    MinIdle = 0
    NumTestsPerEvictionRun = 3
    Password = my-password
    RemoveAbandoned = true
    RemoveAbandonedTimeout = 600
    TestOnBorrow = true
    TestOnReturn = true
    TestWhileIdle = true
    TimeBetweenEvictionRunsMillis = 600000
    UserName = my-user
    ValidationQuery = SELECT 1 FROM DUAL
</Resource>

While running such operations I'm getting exception message: java.sql.SQLException: Connection has already been closed.

What configurational change do I need to make so that:

• Long-running DB queries get executed successfully
• Unused connections get closed properly

I tried to add a new property: ConnectionProperties = oracle.net.CONNECT_TIMEOUT=3600000;oracle.jdbc.ReadTimeout=3600000 but no luck.

Update

Setting:

• MaxIdle = 10
• MinEvictableIdleTimeMillis = 3600000
• MinIdle = 5
• TimeBetweenEvictionRunsMillis = 3600000

No luck.

Answer 1

Can you use the service name and use the URL in this format? jdbc:oracle:thin:@myhost:1521/myorcldbservicename

Also, are you able to get the connection through sqlplus or sqldeveloper ?