topic Re: tOracleOutput multi column primary key in Talend Studio

tOracleOutput multi column primary key

Anonymous — Wed, 31 Jan 2018 09:53:19 GMT

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"

Re: tOracleOutput multi column primary key

Anonymous — Wed, 31 Jan 2018 10:23:24 GMT

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?

Re: tOracleOutput multi column primary key

Anonymous — Thu, 01 Feb 2018 05:26:24 GMT

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

Re: tOracleOutput multi column primary key

Anonymous — Thu, 01 Feb 2018 09:13:24 GMT

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

Re: tOracleOutput multi column primary key

Anonymous — Thu, 01 Feb 2018 09:37:41 GMT

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

Re: tOracleOutput multi column primary key

manodwhb — Thu, 01 Feb 2018 10:18:07 GMT

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

Re: tOracleOutput multi column primary key

Anonymous — Thu, 01 Feb 2018 10:57:34 GMT

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?

Re: tOracleOutput multi column primary key

Anonymous — Mon, 05 Feb 2018 06:40:37 GMT

Yes i have it marked as key in tOracle schema

Re: tOracleOutput multi column primary key

Anonymous — Mon, 05 Feb 2018 06:47:31 GMT

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

Re: tOracleOutput multi column primary key

manodwhb — Mon, 05 Feb 2018 06:49:17 GMT

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

Re: tOracleOutput multi column primary key

Anonymous — Mon, 05 Feb 2018 06:58:54 GMT

Updatable was checked as default, I dont change it, but can I try i

Re: tOracleOutput multi column primary key

Anonymous — Mon, 05 Feb 2018 08:50:08 GMT

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
Thanks you boys