Error on update in Oracle database

[ChrisVK]

Hi,

Hopefully someone can elaborate on this issue.

In an Oracle database I want to get info from one table and insert and/or update it in another.

The columns concerned in the two tables are defined exactly the same. The PK consists of four columns.

Getting the info is going fine. Inserting info in the second table is going fine as well, but updating info in the second table causes this error:

Error: Output Data (118): DataWrap2OCI:  UPDATE .....on record 1

There's only one record to update in my current dataset.

After some trial and error I could define the column causing this error and when  I deselect this columns in a select tool, so it isn't used in the where clause together with the other pk columns, the update goes well.

It's the only column which causes the update to fail, the other columns are ok.

The column causing the fail is defined as varchar2(40) in the database.

In a select tool I can see that  Alteryx defines it as Wstring 10.

In this column there's a string value length 5, with no special characters.

At first I thought, maybe trailing spaces cause the problem, but when I show the length of the values in this column it's five. So it looks like there are no trailing spaces and no special characters.

We use an oci connection to the Oracle database.

The Output Data tool is in the pdf attached.

[john_watkins · Accepted solution]

The simplest test would be to try switching your column to just a basic String to a length less than that of the  column in the table.   I have seen issues with some of the V_WString etc. (anything with a W).    I believe the String type also uses a basic character set.   Have you checked that there are no newlines, tabs, etc. that you might not see in the data?     

[ChrisVK · Accepted solution]

Thanks @john_watkins  for your answer.

When I ran the flow the first time the records picked up from the first and inserted in the second table. Making a minor text change in one of the columns of the first table and running the flow again should amongst others result in an update on the second table. And at first I thought that he column causing the problem after the insert was ok, which means filled with a string length 5, which is the length of the text. But.....after starting the entire process from scratch i could see that after the insert the length of the text was 10. So I made sure that before inserting to trim the text of this column. 

To my surprise running the flow again after a minor text change in the first table which should result in amongst others an update, it worked.

So yes, you were correct about changing the datatype. And yes, after doing it all over again indeed there were trailing spaces causing the update fo fail.