Columnvalue in unix file truncated and rejected

[rverharen]

Hello,

Can someone help me with the following problem:

I have a unix sequential file (NLS ISO8859-1). The euro sign in the file is shown as a question mark when i view data in datastage (because of the NLS setting). The length of the column is 100 positions and is written to an oracle table (NLS UTF8) to a VARCHAR2(100 CHAR) column.
Nevertheless, I still get the next two warnings:
-----------
Value "In 2002 bedroeg de afkoopwaarde ? 1.471,08. In 2006 bedraagt deze ? 680,00. Er is voor ? 3.833,8..." truncated to "In 2002 bedroeg de afkoopwaarde ? 1.471,08. In 2006 bedraagt deze ? 680,00. Er is voor ? 3.833,8.."

InterfaceKMSKlachtData..InlezenData: At row 733, link "DataOra"
Inserted value too large for column, row rejected.
-----------

I hope someone has a solution.

[nick.bond]

have you set the correct LOCALE?

If you're not sure go into Administrator, select the project and go to NLS tab and somewhere in there you can set the LOCALE.

[rafik2k]

Also check after setting same nls map at both the stages(Sequential file and Oracle stage)

[rverharen]

thanks for the responses but the locale property doesn't solve the problem and both stages can't have the same NLS setting because oracle needs UTF8 for certain caracters and the sourcefile has to be ISO8859-1.

when debugging i got the following result:
the string is 99 characters long
when it has 2 or less euro signs in it the insert goes well
when the third euro sign comes in (and the length is still 99) the rejected row appears

off course i can simply expand the column but i do think it's strange the row gets rejected

[nick.bond]

i think the reason you are getting rejects is because although there are 99 characters in the field, the Euro signs are 2 bytes long and DataStage is counting the size of the field in bytes.

I had a similar problem with fixed width files once, going through row merge stages it didn't work.

Can't remember the workaround.

One idea before I disappear for the weekend is to check the LOCALE being used in Oracle too.

Other than that play around with the code pages.

....or truncate more characters off the records so that even when there are euros in there it passes validation! - You could count the number of Euros in there and allow 2 bytes per one.....

[rverharen]

i have discovered the problem
the length of the columns in the oracle stage have to be set to 400 length if they are marked varchar2 (100 char) in the database itself.
this is because it can have special characters which indeed take more space.
I guess the new columns were added by hand and been given the exact length of the column of the database instead of the extended value which is given when you are importing the columns from the table definitions in datastage.