Handling non-breaking space character (ASCII 160)
Posted: Wed May 06, 2009 3:21 pm
Hi everybody,
I've a job reading from SQL Server 2005 using DRS (Dynamic RDBMS) stage and writing to Oracle 9 using Oracle OCI stage. The problem I have is that in source DB there is a field with two non-breaking space characters (ASCII 160) that when trying to write to Oracle fails due to and "Inserted value too large for column" because does not recognize these characters.
Doing a substring of this field to be able to insert it into Oracle, I can see four squares instead of two spaces (meaning unknown characters). That's why the error I see in DataStage.
The worst thing is that job works fine in our production environment but does not in our recently created testing env. And I am talking about using same source and target databases since I was able to do a small test in production env using test DBs.
I suspect might be the NSL configuration but I can't do much from my developer role. DataStage Admin has been notified but said both environments have same settings.
Other valuable info:
-Oracle's NLS character map is AMERICAN_AMERICA.UTF8
-I use NCHAR when reading from SQL Server
-DataStage's NSL character map is UTF8 (Administrator->Project->NLS)
One hint I have is that in Director, under the "Environment variable settings:" information; in prod env appears the record NLS_LANG=AMERICAN_AMERICA.UTF8 whereas in test does not. Not sure if means something.
How can I verify NLS is configured properly? Any idea why this is not working in one environment?
I appreciate any help.
I've a job reading from SQL Server 2005 using DRS (Dynamic RDBMS) stage and writing to Oracle 9 using Oracle OCI stage. The problem I have is that in source DB there is a field with two non-breaking space characters (ASCII 160) that when trying to write to Oracle fails due to and "Inserted value too large for column" because does not recognize these characters.
Doing a substring of this field to be able to insert it into Oracle, I can see four squares instead of two spaces (meaning unknown characters). That's why the error I see in DataStage.
The worst thing is that job works fine in our production environment but does not in our recently created testing env. And I am talking about using same source and target databases since I was able to do a small test in production env using test DBs.
I suspect might be the NSL configuration but I can't do much from my developer role. DataStage Admin has been notified but said both environments have same settings.
Other valuable info:
-Oracle's NLS character map is AMERICAN_AMERICA.UTF8
-I use NCHAR when reading from SQL Server
-DataStage's NSL character map is UTF8 (Administrator->Project->NLS)
One hint I have is that in Director, under the "Environment variable settings:" information; in prod env appears the record NLS_LANG=AMERICAN_AMERICA.UTF8 whereas in test does not. Not sure if means something.
How can I verify NLS is configured properly? Any idea why this is not working in one environment?
I appreciate any help.