Character not inserting into Oracle DB.

jdmiceli · Post by **jdmiceli** » Mon Mar 24, 2008 2:42 pm

What datatypes are these fields defined as in your mapping? They should be something like 'NVarChar' to allow for the expanded ASCII characters. In all honesty, I don't have a system set up with NLS enabled, so I can't speak to whether this suggestion is worth it's salt at all. Hopefully, that helps

tom · Post by **tom** » Mon Mar 24, 2008 2:50 pm

Thanks john for your reply .I tried with NVarchar as a datatype to this column,but still the character is not inserting.

jdmiceli · Post by **jdmiceli** » Mon Mar 24, 2008 2:58 pm

Well, it was worth a shot. You probably do need the NLS stuff enabled then, but I'm clueless there.

Toodles!

chulett · Post by **chulett** » Mon Mar 24, 2008 3:20 pm

No, you don't need NLS for this. However, you do need to know what characterset your database is using, ask your DBA if you are unsure.

Sudhindra_ps · Post by **Sudhindra_ps** » Mon Mar 24, 2008 3:21 pm

hi,

You could determine NLS settings on your Oracle DB by executing below SQL query.
SELECT * FROM NLS_SESSION_PARAMETERS;
Just try inserting the character you are talking about in the DB as an first step. If it is fine then check whether code page on your ETL OS is same as that you see in DB. If not, change your NLS code page accordingly.

Thanks & regards
Sudhindra P S

ArndW · Post by **ArndW** » Tue Mar 25, 2008 2:11 am

Yes, you need to set NLS_LANG to reflect the Oracle database language.

tom · Post by **tom** » Tue Mar 25, 2008 8:47 am

Hi Arndw

Thanks for your reply.After setting NLS_LANG user defined environment
variable in my project and while using in my job I am getting the below error

ORA_BM_INSERT: connect failed for env: EIM_D, user: etl_load, password: *****
Oracle error: -12705
ORA-12705: Cannot access NLS data files or invalid environment specified

NLS is not enabled in my project.Please guide me.

Thanks
tom.

chulett · Post by **chulett** » Tue Mar 25, 2008 9:02 am

Tell us A) how you set this and B) what value you set it to.

tom · Post by **tom** » Tue Mar 25, 2008 9:29 am

Hi chulett,

Thanks for your reply

I set this value as

NLS_LANG=AMERICAN_AMERICA.UTF16 in the userdefined environment variables of my project and
in my job set as

NLS_LANG=$PROFDEF

I could see NLS_LANGUAGE as AMERICAN in oracle DB using the query.

SELECT * FROM NLS_SESSION_PARAMETERS;

NLS_CHARACTERSET value is not appearing by the above query.

Please let me know this setting is correct.

Thanks in Advance.
tom

chulett · Post by **chulett** » Tue Mar 25, 2008 11:36 am

Session parameters won't help, run the query from NLS_DATABASE_PARAMETERS and let us know what you find.

tom · Post by **tom** » Tue Mar 25, 2008 12:00 pm

Chulett,

Thanks for your reply.

While querying NLS_DATABASE_PARAMETERS I can see value of NLS_CHARACTERSET which is set to AL32UTF8 and NLS_NCHAR_CHARACTERSET to AL16UTF16.

Thanks
tom

chulett · Post by **chulett** » Tue Mar 25, 2008 12:10 pm

Try: NLS_LANG=AMERICAN_AMERICA.UTF8 and also LC_CTYPE=C.utf8 rather than the NCHAR version.

tom · Post by **tom** » Wed Mar 26, 2008 7:49 am

Thanks chulett for your help.

My problem got solved after setting the

NLS_LANG environment variable value as AMERICAN_AMERICA.AL32UTF8 in the administrator client.

Can any one please clarify these questions.

1.)Why we need to give as AMERICAN_AMERICA.AL32UTF8 to NLS_LANG ,where oracle NLS_CHARACTERSET is just AL32UTF8?

2.)What is LC_CTYPE?Do I need to set this environment variable also with the same value of NLS_LANGor C.utf8?

3.)How can I view the default character map which is currently set?-When NLS is not installed

4.)Is it mandatory that Database set character value and DS character set should be in sync?

5.)Why below variables are commented in dsenv file?

#LANG="<langdef>";export LANG
#LC_ALL="<langdef>";export LC_ALL
#LC_CTYPE="<langdef>";export LC_CTYPE
#LC_COLLATE="<langdef>";export LC_COLLATE
#LC_MONETARY="<langdef>";export LC_MONETARY
#LC_NUMERIC="<langdef>";export LC_NUMERIC
#LC_TIME="<langdef>";export LC_TIME
#LC_MESSAGES="<langdef>"; export LC_MESSAGES

In DS director LC_CTYPE value is en_US.ISO8859-1.From where this value is getting to this variable?

Thanks for your time.
tom

chulett · Post by **chulett** » Wed Mar 26, 2008 8:20 am

Glad you got it sorted out. Let's see...

1. Because NLS_LANG is a combination of several values.

2. Not entirely sure. No, it doesn't get set to the same value as NLS_LANG, it has its own set of values. Type 'locales' from the command line to see how all of them are set. Or maybe it's 'locale'... I forget.

3. No clue.

4. No, nothing is mandatory about it. Just understand that when they are in sync, no characterset conversion happens. When they are different, a conversion is attempted. Sometimes you need the conversion to happen and at others... you don't.

5. They are commented so as not to override their default settings. I'm guessing you would uncomment them if, for some reason, you needed to override one or more of them all the time.

chulett · Post by **chulett** » Wed Mar 26, 2008 9:03 am

That one remains... unresolved... at the moment.