Multibyte Problem - Special Character - Oracle 11g

jweir · Post by **jweir** » Wed Aug 26, 2015 3:00 pm

Hi all,

We are having a problem loading special characters into a table in oracle 11g. However, this same job works in our Dev environment, but not in Staging. The DDLs for the two environments are the same, and when we use the Dev DataStage job and load into the Staging oracle table, we are successful. However, when we use the Staging Datastage job and load into the Stage database table, it fails with the "value too large" error. We have this problem in several jobs and it seems to be due to a special character in some of the records.

Question is - why does this work in the Dev IIS environment and not Stage? Are there IIS settings that I can check that can decrease the length of the byte for special characters?

Thanks in advance.

rkashyap · Post by **rkashyap** » Wed Aug 26, 2015 7:41 pm

Compare NLS settings of Staging project with that of Dev project - validate that NLS is enabled in Staging project. Also compare value of NLS_LANG environment variable between the two projects.

priyadarshikunal · Post by **priyadarshikunal** » Thu Aug 27, 2015 12:57 am

Also check If the column data type is defined as char or byte

i.e. columnA varchar2(20 char) or columnA varchar2(20 byte), you can also check what is the value defined for nls_length_semantics in oracle.

jweir · Post by **jweir** » Thu Aug 27, 2015 6:32 am

The NLS settings are the same between the two projects. The general NLS setting is MS1252-CS and the default server map is UTF8 in both projects.

Where can I find the NLS_LANG environment variable? I do not see it in the Admin client.

@priyadar - Both databases have same DDL and I have loaded into the Staging table using Dev IIS, so as I mentioned, I do not believe this to be a database issue.

priyadarshikunal · Post by **priyadarshikunal** » Thu Aug 27, 2015 6:47 am

did you specify whether to use byte or char in oracle ddl?

There are only two things here which seems to be causing the issue, rkashyap already mentioned one. And second is database, since you mentioned in your last post that you are using dev IIS, then only thing that changed is database, so where do you think the problem is? if the dll and datastage did not change then only place to look at will be database settings, and NLS parameters defined for the same in database itself. Do compare the database nls parameter values in both databases.

By the way, NLS_LANG is oracle database NLS parameter and for oracle stage to use a particular NLS_LANG, an user defined environment variable is created in data stage, for which the value should match the database NLS_LANG for seamless processing.

jweir · Post by **jweir** » Thu Aug 27, 2015 7:43 am

I took rkashyap's advice and it worked. I manually added the NLS_LANG project level variable in the Admin client and added the Stage database NLS settings. After this, the jobs that had issues were successful.

One more question - is there some other setting in IIS that defaults the NLS setting so we do not have to add this variable?

rkashyap · Post by **rkashyap** » Wed Sep 16, 2015 6:19 pm

jweir wrote:One more question - is there some other setting in IIS that defaults the NLS setting so we do not have to add this variable?

There is an option to set NLS_LANG in dsenv, instead of setting it at job/project level as described in thistechnote.