[resolved] Error in tOracleInput when using tOracleConnection

[Anonymous]

Hello,
I am trying for a while to solve that, but can't figure out where the problem come from.
I have a job, which been created on talend 4.2.2. this job do a few operation on an oracle database 10. the job is working is fine for the last 2 years. I have to move this job to talend 5.6 and then move the database to oracle 11. I exported my workspace Tos 4.2 into an archive and imported in on Tos 5.6, download the differents libs, and exported as an OSGI. everything went fine.
But when I am trying to run that job in Tos 5.6 or on karaf I get that error :

Exception in component tOracleInput_1
java.sql.SQLException: Connexion interrompue
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:147)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:209)
	at oracle.jdbc.driver.PhysicalConnection.createStatement(PhysicalConnection.java:780)
	at oracle.jdbc.driver.PhysicalConnection.createStatement(PhysicalConnection.java:756)
	at xxx.yyyy_1_2.zzzzz.tOracleInput_1Process(zzzzz.java:4578)
	at xxx.yyyy_1_2.zzzzz.tJava_2Process(zzzzz.java:4327)
	at xxx.yyyy_1_2.zzzzz.tWarn_11Process(zzzzz.java:4178)
	at xxx.yyyy_1_2.zzzzz.tFixedFlowInput_3Process(zzzzz.java:4011)
	at xxx.yyyy_1_2.zzzzz.tOracleConnection_1Process(zzzzz.java:3379)
	at xxx.yyyy_1_2.zzzzz.runJobInTOS(zzzzz.java:16714)
	at xxx.yyyy_1_2.zzzzz.main(zzzzz.java:16429)
Composant / Sous job en erreur pour cause d'exception Java

Here is the differents tests I did :
using my computer, another computer, and the a Centos server :
I tried those with and oracle database 11, didn't work as well :

Environement	OS	Tos version	Java version	Java compilator version in Tos	Database	Error
My computer	Windows 7	5.6	6 / 7	1.5 / 1.6	Oracle 10	Connexion interrompue
My computer	Windows 7	4.2	6 / 7	1.6 / 1.5	Oracle 10	OK
other computer	Windows XP	4.2	6	1.6	Oracle 10	Connexion interrompue
TESB	Centos 6.4	5.6	7	1.5 / 1.6	Oracle 10	Connexion interrompue
StandAlone	Centos 6.4	4.2	7	1.5 / 1.6	Oracle 10	OK

 

I feel like the connection is closed too fast by the oracle server, but it s working fine on tos 4.2.
the tOracleInput use the connection etablished by the tConnectionOracle. If I move the block tOracleInput right after the tConnectionOracle, it s working fine, same if I connect to the database inside the tOracleInput...
I have a lot of tOracleInput and don't want to use to do a connect in all the different tOracleInput.
Any idea how to solve that without touching the database ?
Thank you

[Anonymous]

Sorry I put it in the wrong section, and can't move it.
www.talendforge.org/forum/img/members/206659/ErrorOracle.jpg

Here the link to the picture since it won't let me insert it into the page...

[Anonymous]

Hi,
Can you connect to your oracle DB successfully by using DB metadata in 5.6 studio?
Best regards
Sabrina

[Anonymous · Accepted solution]

Hello,
Yeah it's working fine.
the tOracleConnection work as far as I can see in the listener.log of oracle

13-JAN-2015 09:25:06 * (CONNECT_DATA=(SID=XXXXXXXXX)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XX.XX.XX.XX)(PORT=56925)) * establish * XXXXXXXXX * 0
13-JAN-2015 09:25:12 * (CONNECT_DATA=(SID=XXXXXXXXX)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XX.XX.XX.XX)(PORT=56926)) * establish * XXXXXXXXX * 0
13-JAN-2015 09:25:12 * (CONNECT_DATA=(SID=XXXXXXXXX)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XX.XX.XX.XX)(PORT=56927)) * establish * XXXXXXXXX * 0
13-JAN-2015 09:25:13 * service_update * XXXXXXXXX * 0

using ojdbc14 for oracle10 (download by Tos 5.6).
the same job but built by talend 4.2 is working fine on that database everyday.

[Anonymous]

for the table on my main post I  did a mistake, it suppose to be like that :
other computer
Windows XP
4.2
6
1.6
Oracle 10
OK


other computer
Windows XP
5.6
6
1.6
Oracle 10
Connexion interrompue

sorry.

[Anonymous]

Hi,
Do you mean it didn't work well when you publish your job created in 5.6 on Talend Runtime? Have you tried to create a new job in 5.6 and pulish it on Talend runtime to see if everything is going well? 
Best regards
Sabrina

[Anonymous]

Hello,
yeah but the problem doesnt come from the runtime.
the job doesn't work when I try to run it in tos 5.6 (debug), or if I export it as a stand alone or OSGI and run it.
but the job work fine when I run it in tos 4.2 (debug), or if I export it as a stand alone and run it.
I m gonna recreate my job from scratch in tos 5.6 and see if I can make it work that way.
I have a lot of job to migrate from tos4.2 to tos5.6 and can't recreate them all though.

[Anonymous]

Hi,
First of all, make sure your 5.6 studio is working well. Have you ever executed a job successfully in 5.6 studio? Could you please give us your curent OS and JDK version?
Did you follow up documents: TalendHelpCenter:How to import projects and TalendHelpCenter:How to export a project?
Best regards
Sabrina

[Anonymous]

Well, I run a few jobs and they are working fine, smaller job, some with database acces (oracle).
I recreated the start of the job and executed it, it s working. But when I copy those blocks into my old job and replace them, it s not working anymore... I probably miss something, but can't figure out what.
for the export/import, I exported the workspace into a folder and reimported it, same as in the tutorial.
first computer : windows 7 64 bits, java 1.7.0_45
second computer windows XP 32 bits, java 1.6.0_35
I am going to recreate the job and try it regulary during the development, to get more information.

[Anonymous]

I found where the problem come from.
inside the job we have a tLogCatcher, which launch another job we created.
This job manage the different kind of log we want to save in our database. It start with a tOracleConnection then process a few thing, write in the database and then tOracleCommit (without closing the connection).
Both of my jobs (the main job and the subjob) use the same " Use or register a shared DB Connection" -> context.nameConnexion.
thoses jobs were working fine in tos4.2, but it look like, in tos5.6 the connection is kill by the subjob..?
If I change the " Use or register a shared DB Connection" of the subjob, there no more problem.
thank you for your help