NLS mapping problem

saikrishna · Post by **saikrishna** » Fri Nov 07, 2008 2:27 am

Hi

We recently changed NLS mapping from ISO8859-1 to UTF8. We were done this to have all the international characters as specified in UTF8 to show in the table correctly.

Because of this change, the following job behaving differently

SEQ FILE -> TFM -> TABLE

Earlier
All the records were loading to the table. But showing some weird characters in the table for some records.

NOW
Some of the records are not loading to the table.. showing that as NLS mapping error as follows:

load_stgf_msgr_imv_d_max..feed_msgr_imv_d.feed_msgr_imv_d: nls_map_buffer_in() - NLS mapping error, row 13804 (approx), row = "us6.000VNh?nh n?y d?p qu? co ph?i kh?ng h?u: http://vietgiaitri.com/ind100000010000011"

Can any one please help us in finding the solution to this problem...

thanks
Sai

saikrishna · Post by **saikrishna** » Thu Dec 04, 2008 3:36 am

Can any one please help us the problem behind nls mapping problem...

chulett · Post by **chulett** » Thu Dec 04, 2008 8:05 am

What database and what is the "NLS setting" of that database?

saikrishna · Post by **saikrishna** » Thu Dec 04, 2008 8:28 am

It is Oracle database.
And NLS_CHARACTER SET is "AL32UTF8"

Thanks
Sai

PhilHibbs · Post by **PhilHibbs** » Mon Dec 08, 2008 11:03 am

saikrishna wrote:Because of this change, the following job behaving differently

SEQ FILE -> TFM -> TABLE

What character set is the file? Is it a UTF8 file or an ISO8859-1 file? If it is ISO8859-1 file, then set the Sequential File stage itself to that NLS map and then your job will convert it to UTF8. I find that ISO8859-1+MARKS handles a wider variety of characters correctly then plain old ISO8859-1.

saikrishna · Post by **saikrishna** » Wed Dec 10, 2008 7:20 am

The NLS character set in Sequential file and Table are set to UTF8

saikrishna · Post by **saikrishna** » Tue Jan 13, 2009 6:47 am

It is ASCII file, which contains Chinese characters. But could not load using DataStage.. But could load correctly using SQLLOADER..

I tried setting UTF, ISO8859-1+MARKS in NLS of DataStage..but could not load it correctly.

For chinese characters, DataStage is reading more characters than actual.

Can any one of you please help us in resolving the NLS problem in DataStage with Chinese characters.

Note: Whatever I pasted the data in my first comment was a direct copy..so please ignore that.

Thanks for the help
Sai

PhilHibbs · Post by **PhilHibbs** » Tue Jan 13, 2009 6:57 am

saikrishna wrote:It is ASCII file, which contains Chinese characters.

That makes no sense. ASCII cannot represent Chinese characters, it only has English alphanumerics and punctuation marks in the character range 32 (Space) through to 126 (~).

saikrishna · Post by **saikrishna** » Tue Jan 13, 2009 7:17 am

hI PhilHibbs
It could be non-ascii file... we get this from an upstream system as a regular file.

Please dont concentrate on the type of file, instead can you please tell us, if possible, the problem with the non-conversion of chinese characters using UTF8?

As it is working fine with normal sql loader utility in oracle.

Thanks
Sai

PhilHibbs · Post by **PhilHibbs** » Tue Jan 13, 2009 7:54 am

saikrishna wrote:Please dont concentrate on the type of file, instead can you please tell us, if possible, the problem with the non-conversion of chinese characters using UTF8?
Sai

Well, the file type is pretty important, if your Sequential File stage specifies UTF8 and the file isn't a UTF8 file, then you will have real problems.

In the SQL*Loader definition, do you specify the file type as UTF8 in the CTL or does it detect it? I'm a bit rusty with SQL*Loader and I've never had to worry about file types as I've only ever used it for ISO8859-1 files and that worked by default.

Can you tell me what the first few bytes of your source file are in hex? That will tell me whether it is UTF8 or not, providing it has a Byte Order Mark which most UTF8 files should have (but not always).

nisaumande · Post by **nisaumande** » Tue Jan 13, 2009 8:45 am

You should also check the parameter NLS_LANG for the oracle client.

Nicolas

PhilHibbs · Post by **PhilHibbs** » Tue Jan 13, 2009 9:17 am

nisaumande wrote:You should also check the parameter NLS_LANG for the oracle client.

Maybe, but that's not related to this incident. This is clearly the processing of the input link, as the error message is relating to nls_map_buffer_in().

ray.wurlod · Post by **ray.wurlod** » Tue Jan 13, 2009 2:50 pm

saikrishna wrote:Please dont concentrate on the type of file, instead can you please tell us, if possible, the problem with the non-conversion of chinese characters using UTF8?

Your thinking is "off base" here. The NLS Map must accurately describe how the data in the file are encoded.

DSXchange

NLS mapping problem

NLS mapping problem

Re: NLS mapping problem