Character not inserting into Oracle DB.
Moderators: chulett, rschirm, roy
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
Bestest!
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
Well, it was worth a shot. You probably do need the NLS stuff enabled then, but I'm clueless there.
Toodles!
Toodles!
Bestest!
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
-
- Participant
- Posts: 45
- Joined: Thu Aug 31, 2006 3:13 am
- Location: Bangalore
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
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
Yes, you need to set NLS_LANG to reflect the Oracle database language.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
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.
Devlopers corner
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
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
Devlopers corner
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
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
Devlopers corner
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers