Character not getting loaded in Oracle through ODBC

chulett · Post by **chulett** » Thu Mar 06, 2008 5:13 pm

If that was an apostrophe (hex 27) it would have loaded fine. I've had the same issue in the past when the source is something like Word and you get them dang 'smart quotes' rather than a simple apostrophe.

What is your NLS_LANG setting for your database?

memrinal · Post by **memrinal** » Thu Mar 06, 2008 5:26 pm

Thanks Chulett.

Code: Select all

NLS_LANGUAGE - American

userenv('LANGUAGE') = AMERICAN_AMERICA.AL32UTF8

Is there some way we can ensure that all such > 80H characters get loaded properly in oracle. (through ODBC would be preferable)

chulett · Post by **chulett** » Thu Mar 06, 2008 5:41 pm

First thing would be to verify that your character actually exists in the target character set. If your source and your target use the same character set, you can just load from one straight to the other, otherwise a conversion needs to happen. When the source character set (controlled by the job setting) is different from the target character set (controlled by the database setting) the conversion is automatic.

Where you run into trouble is when the character sets are different but you tell your process they are the same. No conversion takes place and you get 'garbage' in your target.

We use 7.x/Server/OCI here, not sure if there are any nuances to this when using 8.x/PX/ODBC. We also don't have NLS enabled in DataStage where it sounds like you do. Hopefully others will have words of wisdom to add to this.

memrinal · Post by **memrinal** » Thu Mar 06, 2008 5:50 pm

This character exists in the target chracter set.
this was one of the first things I tested, by manually inserting the record in the DB through a query.