One Star

Commit every... with existing connection

I am trying to load data into oracle using TOS 2.3.3.
1) I have main connection that goes from my file to tOracleOutput.
2) I use the existing connection.
3) I want to commit every 10000 rows.
Is there a way to implement this kind of load in Talend?
I understand that I can do it if I don't use existing connection (there is a Commit Every... field on the Advanced Settings tab, but it disappears when I switch to "use existing connection"). Why can't I do it with the existing connection? Is there a reason or am I missing something?
8 REPLIES
Employee

Re: Commit every... with existing connection

Why can't I do it with the existing connection? Is there a reason or am I missing something?

It's a design choice from us the developers. tOracleConnection/tOracleCommit/tOracleRollback where designed so that user can control the transactions.
Workaround:
tFileInputDelimited --row--> tOracleOutput --row--> tFilterRow --row--> tOracleCommit
in the tFilterRow, advanced mode:
sequence('abcd', 1, 1) % 10_000 == 0

Don't forget to add a tOracleCommit after the subjob, so that last lines are commited.
May I ask you to add a feature request "add a commit every in t*Commit"? I think it would be much more user friendly than the tip I just gave you.
One Star

Re: Commit every... with existing connection

Why can't I do it with the existing connection? Is there a reason or am I missing something?

It's a design choice from us the developers. tOracleConnection/tOracleCommit/tOracleRollback where designed so that user can control the transactions.
Workaround:
tFileInputDelimited --row--> tOracleOutput --row--> tFilterRow --row--> tOracleCommit
in the tFilterRow, advanced mode:
sequence('abcd', 1, 1) % 10_000 == 0

Don't forget to add a tOracleCommit after the subjob, so that last lines are commited.
May I ask you to add a feature request "add a commit every in t*Commit"? I think it would be much more user friendly than the tip I just gave you.
Excellent! I will use this trick for now and wait for the new feature.
Submitted 3797
One Star

Re: Commit every... with existing connection

How to perform such trick with java tFilterRow?
One Star

Re: Commit every... with existing connection

Hi,
In tFilterRow, Advanced:
Numeric.sequence("abcd", 1, 1) % 500 == 0
Kind regards,
Yves
One Star

Re: Commit every... with existing connection

Numeric.sequence("abcd", 1, 1) % 500 == 0

It works.
Thank you very much!
One Star

Re: Commit every... with existing connection

what is "abcd"?
One Star

Re: Commit every... with existing connection

The first 4 letters of the alphabet.
Actually it's the sequence reference name.
Four Stars

Re: Commit every... with existing connection

Hi guys,

 

Could you please help me with this??

 

I have 495 records in input when I try to run the job it inserts only 400 records if i keep the expression

Numeric.Sequence("S1",1,1)%100==0 in advanced mode of tFilterRow.

 

SELECT COUNT(1) FROM TEMP ---> 400 

 

May I know why and how to insert the other 95 records?

PFA screenshot for reference

 

Thanks in advance