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

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

PSanadi — Fri, 15 Nov 2024 21:55:12 GMT

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.

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

jlolling — Sat, 08 Apr 2023 16:30:42 GMT

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

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

PSanadi — Sun, 09 Apr 2023 15:08:28 GMT

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.

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

Anonymous — Mon, 10 Apr 2023 01:22:49 GMT

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

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

PSanadi — Mon, 10 Apr 2023 01:44:48 GMT

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.

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

Anonymous — Mon, 10 Apr 2023 02:39:12 GMT

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?

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

PSanadi — Mon, 10 Apr 2023 03:16:46 GMT

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 ?

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

Anonymous — Mon, 10 Apr 2023 06:05:00 GMT

@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.

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

PSanadi — Mon, 10 Apr 2023 06:27:11 GMT

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.

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

PSanadi — Wed, 12 Apr 2023 10:33:33 GMT

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

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

jlolling — Wed, 12 Apr 2023 12:26:56 GMT

What do you expect? I have told you the problem is related to the job design and you do not want to show us your job design. So no progress is possible until you provide the information we need to help.

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

PSanadi — Wed, 12 Apr 2023 12:35:50 GMT

The job is working fine with db2, so if it was related to design it could have failed in db2 as well. Also for multiple runs in oracle it worked fine. The design is pretty simple we use the connection component to register the shared connection and use in multiple jobs.

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

jlolling — Wed, 12 Apr 2023 12:37:43 GMT

Yes but at some point your job closes the connection and I would like to find out where. But it is YOUR problem and not mine. I done with it here.

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

PSanadi — Wed, 12 Apr 2023 12:41:25 GMT

Sorry if I have offended you in any manner. Only intention was to get help on this. It is not possible to add the job design. I have checked there is no close or commit component added in entire job. Appreciate your help.

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

jlolling — Wed, 12 Apr 2023 12:46:30 GMT

Please check if you have database output components where "Die On Error" is not checked.

It could be a component dies silently and the next component trying to use the connection cannot do this until a rollback has performed. This way you will find out the actual problem.

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

PSanadi — Wed, 12 Apr 2023 12:51:36 GMT

We are just generating file using select. There are no output components still I will cross check and confirm back.

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

PSanadi — Wed, 12 Apr 2023 17:04:05 GMT

We are not using any output component.

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

jlolling — Wed, 12 Apr 2023 19:10:43 GMT

Ok, I do not know your job design and no logs. How do you think we could help you?

Again, if you still not provide more information we cannot help.

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

TBates1683209008 — Thu, 11 May 2023 11:10:48 GMT

Have you resolved it?