Error in Talend job with oracle DB java.sql.SQLRecoverableException: Closed Connection

[PSanadi]

Hi All,

One of our Talend job failed with below error

Message :java.sql.SQLRecoverableException: Closed Connection: next

at oracle.jdbc.driver.InsensitiveScrollableResultSet.ensureOpen(InsensitiveScrollableResultSet.java:110)

at oracle.jdbc.driver.InsensitiveScrollableResultSet.next(InsensitiveScrollableResultSet.java:404)

Talend Data Integration 7.3.1 Oracle Database 19.3.0

Ojdbc - ojdbc8-19.3.0.0.jar

JDK - 11.0_18l64

We are using shared connections and before any DB input component we have a connection component to register the shared connection name.

All the operations are select. Please help to find root cause of this issue.

[jlolling]

This is a typical error caused by a problem in the job design. Please post here a screenshot of your job.

[PSanadi]

Hello the job is having connection component to register the shared connection and then tdbinput to select the data. ​The same job was working fine with DB2 database. We did multiple executions and all of them were successful but few of them failed with the error mentioned.

[Anonymous]

Hello @Pravin Sanadi​ ,

Regarding to the error log, the problem is that the db connection is closed while it's still in use.

Do you have a connection pool settings for the oracle db?

One possible solution is not to check the button "Use or register a shared DB Connection" for tOracleConnection component

Thanks

Aiming

[PSanadi]

Hi Aiming, How do I check the connection pool settings in oracle can you help me with it. We want to use shared connection for better processing time. We have 2 db input components connected out of which one worked fine the next one failed so don't understand where and who closed the connection. We do not have any close connection or commit component in the code. And as it is just select we don't need it as well.

[Anonymous]

Hello @Pravin Sanadi​ ,

Sorry, it should not be related to the connection pool issue as the tOracleConnection won't use the pool.

As a test, could you please double check if the "Auto Commit" button is checked in tOracleConnection's advanced settings?

if not, please check it to re-try?

[PSanadi]

But do we need this ? The auto commit is unchecked and as we are doing only select why do we need this to be checked ?​

[Anonymous]

@Pravin Sanadi​ ,

By default, the auto commit function is disabled and changes must be committed explicitly using the corresponding commit component, as you didn't use commit component in the job, so it should be checked so that it will commit any changes to the database automatically upon the transaction.

[PSanadi]

We are not doing any DML operation we are just doing select which does not need any commit. Similar setting and code was working with ibm db2 and failed for Oracle. So something wrong with the ojdbc jar and talend connectivity I feel.​

[PSanadi]

Any update? What could be the reason for the failure?