topic Re: tOracleInput data being duplicated in Talend Studio

tOracleInput data being duplicated

Anonymous — Wed, 27 Sep 2017 12:08:18 GMT

Hi All, I'm new to both the forum and Talend and looking for some help here....

I have a simple job that reads data in from an oracle table, then maps it to tAggregateRow, where I group by the rows I need and perform a count and an average on two others. This is then tMap'd out to another oracle table. Simple right! Problem is that I have 13,187 rows in the table that I'm reading from, and it's loading in 52,566. If i run the exact same SQL that is in tOracleInput in SQL Developer, I get the 13,187 I expect.

Confused - hope you can help!

Re: tOracleInput data being duplicated

Anonymous — Wed, 27 Sep 2017 12:43:44 GMT

Resolved

Re: tOracleInput data being duplicated

Anonymous — Thu, 28 Sep 2017 07:38:36 GMT

Hi,

Could you please share your solution with us? Which will be preferred.

Best regards

Sabrina

Re: tOracleInput data being duplicated

Anonymous — Thu, 28 Sep 2017 11:07:09 GMT

I worked out why I was seeing duplicated data and it was because I had stupidly forgotten to commit after loading. However, I'm still unable to get Talend to successfully do this commit for me. I've tried a number of ways to get tOracleCommit to perform its task, my last one being the diagram shown below. Any help would be greatly appreciated.

Thanks

commitdiag.png

Re: tOracleInput data being duplicated

Anonymous — Thu, 28 Sep 2017 13:38:11 GMT

you need to use a tOracleConnection/tOracleCommit together to control transactions in talend. at the beginning of your job, put the tOracleConnection, then configure each oracle input/output component to "use existing connection" and then at the end where you want to commit, place the tOracleCommit.

Re: tOracleInput data being duplicated

Anonymous — Thu, 28 Sep 2017 14:59:33 GMT

Thanks for your reply.

The problem I am facing with your solution is that I am using inputs from two different oracle connections in my job..

Re: tOracleInput data being duplicated

Anonymous — Thu, 28 Sep 2017 15:36:09 GMT

you would just need two sets of connect/commit components to work with two DB's

Re: tOracleInput data being duplicated

Anonymous — Fri, 29 Sep 2017 15:56:06 GMT

Thanks - just tried that but still no joy. Also, as I'm only retrieving data from one of the data sources, surely I don't need to commit ton this one, only the one that I'm writing to?

Please see diagram..

Thanks for your help so far, I'm finding it a little frustrating...

commitdiag.png

Re: tOracleInput data being duplicated

Anonymous — Fri, 29 Sep 2017 20:38:01 GMT

Since you havnt told us what exact problem you're having, I'm just gonna show you the way I would design a job like this and maybe you'll spot your problem along the way.

First, get rid of the onComponentOK links you have in there (using onComponentOk is almost always incorrect), then reconnect *everything* using onSubjobOK (disconnected components are almost always incorrect). The job will look something like this:

in each of your oracle components, check the "use an existing connection" checkbox and select the appropriate connection from the "Component List" dropdown:

Then, at the end of your job, use two tOracleCommit components to close the connections to your DB's by linking each one to the appropriate tOracleConnection component.

Re: tOracleInput data being duplicated

Anonymous — Tue, 03 Oct 2017 09:11:15 GMT

Thanks you very much for taking the time to respond.

I've re-written my job taking note of the steps that you have depicted above and have now managed to get my data how I need it. The problems I was having were a combination of ignorance and stupidity 🙂

Thanks again.