tOracleOutput multi column primary key

Highlighted
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"


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 


All Replies
Community Manager

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?

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

Community Manager

Re: tOracleOutput multi column primary key

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

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

Forteen Stars

Re: tOracleOutput multi column primary key

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

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Community Manager

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? 

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

Forteen Stars

Re: tOracleOutput multi column primary key

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

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
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 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog