Handling special characters with Oracle OCI

Soumyabrata · Post by **Soumyabrata** » Fri Dec 15, 2006 7:58 am

Hi,
I am trying to load data from a sequential file to a staging table in Datastage using ORacle OCI coneector.The file contains special characters.After load is done in the staging table,I am finding that the special characters have been replaced by junk characters in the table.I am using UTF8 as NLS and have also tries out MS1252 as NLS but without success.Can you pls. tell if DataStage supports Oracle OCI for special characters.If so how?If not so why,because logically OCI connector should not be a problem even if special characters are present in the data.

Thanks,
Soumyabrata

chulett · Post by **chulett** » Fri Dec 15, 2006 8:04 am

See if this post helps or any of the other posts you can find by searching for NLS_LANG.

If not, come back with more details, like what NLS_LANG setting your target database is and some examples of these 'special' characters. Some are more special than others, from what I recall.

Soumyabrata · Post by **Soumyabrata** » Fri Dec 22, 2006 4:03 am

Hi,

We have tried inserting the data using OCI, by setting the load mode of OCI to manual which would create the above files.
In the control.ldr file add the parameter CHARACTERSET utf8.
Now log on to the unix session set NLS_LANG= AMERICAN_AMERICA.WE8ISO8859P1. If we run sqlldr the characters are loaded correctly.

sqlldr userid=<username>/<password> control=control.ldr

So it seems that the datastage is missing some NLS mapping. Please try to add the WE8ISO8859P1 NLS map in data stage, by default it is not present it might need to downloaded and installed.I am not sure about this, needs to be checked.Can some one help me how to download and install the NLS map?

Thanks,
Soumyabrata

Soumyabrata · Post by **Soumyabrata** » Fri Dec 22, 2006 4:04 am

Hi,

We can try inserting the data using OCI, by setting the load mode of OCI to manual which would create the above files.
In the control.ldr file add the parameter CHARACTERSET utf8.
Now log on to the unix session set NLS_LANG= AMERICAN_AMERICA.WE8ISO8859P1. If we run sqlldr the characters are loaded correctly.

sqlldr userid=<username>/<password> control=control.ldr

So it seems that the datastage is missing some NLS mapping. Please try to add the WE8ISO8859P1 NLS map in data stage, by default it is not present it might need to downloaded and installed.I am not sure about this, needs to be checked.Can some one help me how to download and install the NLS map?

Thanks,
Soumyabrata

chulett · Post by **chulett** » Fri Dec 22, 2006 8:35 am

Did you read the post I linked to? The blue letters there can be clicked on and will explain what you need to do. Short answer is modify your dsenv file.

DataStage isn't missing anything because it's not the one that needs that mapping - Oracle does. So nothing to download or install, just a little configuration change or two needed.

dwuser · Post by **dwuser** » Sat Apr 14, 2007 12:29 am

Chulett,

Can you please provide the link for this topic.

chulett · Post by **chulett** » Sat Apr 14, 2007 6:24 am

I did - as noted, it is in my first reply in this thread. And as noted, two words there should appear different - blue - they are the link to the topic in question.

Or simply search for NLS_LANG and you'll find a plethora of topics.