Five Stars

tOracleOutput multi column primary key

Hello, i have one problem. I use in my project tOracleOutput. In my table I dont have primary key. But i want use in talend job multi column primary key. When i check (in tOracleOutput-Advanced settings-Use field options) checkbox only in one row (update) that is work fine, but when i check more rows, that ignore them and insert new rows.....can u help me with this ???
I use talend openstudio 6.2.1 and in tOracleOutput-Action on data i try both - "Insert or update" and "Update or insert"

1 ACCEPTED SOLUTION

Accepted Solutions
Five Stars

Re: tOracleOutput multi column primary key

I found what was wrong, its my fault. In my schemas I have START_DATETIME and END_DATETIME as Date but in DB it is VARCHAR2...
After change, its working and dont insert duplicated values Smiley Happy
Thanks you boys 

11 REPLIES
Forteen Stars

Re: tOracleOutput multi column primary key

Can you give an example of what you are seeing in data? If you have 5 columns (colA, colB, colC, colD and colE) and have your composite key on colA, colB, and colC, you would tick the corresponding tick boxes for update key in the field options box. If colA, colB and colC hold A B C in the first row of data and you have insert or update, then the row will be inserted. If A B C is in the second row, then the record will be updated. If A D C is in the third row, then the record will be inserted, and if A D C is in the fourth row, it will cause an update. 

 

Where is yours going wrong?

Rilhia Solutions
Five Stars

Re: tOracleOutput multi column primary key

Hellou my example is
I have 5 columns (colA, colB, colC, colD and colE), I checked box (key in field options) for columns colA, colB, colC, colD
Table is empty and I insert row with values A B C D X, its all right row was inserted.
Nexttime I insert row with values (A B C D Z) but it was inserted too (with both option "Insert or update" and "Update or insert").
Why was second row added when he have same values A B C D ???

Forteen Stars

Re: tOracleOutput multi column primary key

This is very strange. Your expectations sound correct. Can you give us a screenshot of your configuration? 

Rilhia Solutions
Five Stars

Re: tOracleOutput multi column primary key

That`s my configuration. 
After running, job create duplicty rows.

2018-02-01 10_31_25-Talend Open Studio for ESB (6.2.1.20160704_1411) _ tFYT_PS4 (Connection_ FytPs4W.png2018-02-01 10_31_47-Talend Open Studio for ESB (6.2.1.20160704_1411) _ tFYT_PS4 (Connection_ FytPs4W.png2018-02-01 10_32_23-Talend Open Studio for ESB (6.2.1.20160704_1411) _ tFYT_PS4 (Connection_ FytPs4W.png

Ten Stars

Re: tOracleOutput multi column primary key

do you marked Key for those key columns in Schema of tOracleouput?

Manohar B
Forteen Stars

Re: tOracleOutput multi column primary key

Can you show us the data you are seeing this issue with? Your config looks OK. It is looking like it *could* be a bug. I'd need to see your input and output data examples before I am sure of this. For example, are you sure the data is exactly the same (no rogue characters, spaces, etc?) What version of Talend are you using? 

Rilhia Solutions
Five Stars

Re: tOracleOutput multi column primary key

Yes i have it marked as key in tOracle schema

Five Stars

Re: tOracleOutput multi column primary key

no spaces and etc.
1 col LINE_NAME is string val PSF01

2 col START_DATETIME VARCHAR2 val 05-FEB-18
3 col END_DATETIME VARCHAR2 val 05-FEB-18
4 col UNIT INTEGER val 306
5 col MODEL_NAME VARCHAR2 val CUH-XXXXAA01W
6 col CREATE_TIME DATE val 2018/02/05 07:33:20 AM
values are same as input data, and in table I have duplicities

Ten Stars

Re: tOracleOutput multi column primary key

can you show tmap and why you have checked to Updatable for key columns?

Manohar B
Five Stars

Re: tOracleOutput multi column primary key

Updatable was checked as default, I dont change it, but can I try i2018-02-05 07_57_34-Talend Open Studio for ESB - tMap - tMap_2.png

Five Stars

Re: tOracleOutput multi column primary key

I found what was wrong, its my fault. In my schemas I have START_DATETIME and END_DATETIME as Date but in DB it is VARCHAR2...
After change, its working and dont insert duplicated values Smiley Happy
Thanks you boys