Four Stars

tOracleInput data being duplicated

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Six Stars JGM
Six Stars

Re: tOracleInput data being duplicated

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:

 

image.png

 

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

image.png

 

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. 

 

9 REPLIES
Four Stars

Re: tOracleInput data being duplicated

Resolved
Moderator

Re: tOracleInput data being duplicated

Hi,

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

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: tOracleInput data being duplicated

 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

 

 

Six Stars JGM
Six Stars

Re: tOracleInput data being duplicated

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.
Four Stars

Re: tOracleInput data being duplicated

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

Six Stars JGM
Six Stars

Re: tOracleInput data being duplicated

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

Re: tOracleInput data being duplicated

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

Six Stars JGM
Six Stars

Re: tOracleInput data being duplicated

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:

 

image.png

 

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

image.png

 

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. 

 

Four Stars

Re: tOracleInput data being duplicated

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.