| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle Character sets
Note that setting the client NLS_LANG to match the database character set is a rather risky thing to do, since it tells Oracle not to do any character set conversion between the client and the server. This can permit you to get corrupt data into your database, which is a royal pain to fix.
Imagine that you have a database with a character set of US7ASCII. Logically, there are only 128 distinct values that can be encoded in this character set and those are all English characters.
Now, imagine that I have a Chinese client machine that has some Big-5 encoded data to insert. Obviously, though, you can't insert Chinese data into a US7ASCII database. If you set the NLS_LANG on the Chinese system to US7ASCII, though, Oracle will happily allow you to insert the Big-5 data and will keep it fully intact.
If another client machine with an NLS_LANG of US7ASCII comes along and queries the data, they will get valid Big-5 data out. If that is what the application expects, everything will appear to work and you can have a system that stores Chinese data in an ASCII database seemingly without a problem. As long as everyone lies to Oracle in exactly the same way, life is good.
Some months or years after these sorts of systems go into production, though, problems are bound to occur. For example, if you want to replicate the data from the ASCII database to a UTF-8 database, you're out of luck, since the data in the ASCII database is corrupt. If you have different clients that lie to the database differently-- i.e. one set of clients is passing in Big-5 data, another is passing in UTF-8 data, but everyone has their NLS_LANG set to US7ASCII-- you will generate massive problems as data will appear OK only if your application was the last to write it. This sort of multiple character set corruption is much, much harder to fix than the simple corruption problem, which is itself a royal pain.
The client NLS settings should properly identify the character set of the data that particular client machine is sending and expecting in return. Generally, it should match the code page of the local operating system (i.e. Windows-1252 on an English Windows machine).
Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
Sent: Monday, June 07, 2004 1:45 PM
To: oracle-l_at_freelists.org
Subject: RE: Oracle Character sets
Ron,
I'm not aware of any papers, however, I always set NLS_LANG in my environment to match the character set of my database.
So, I have a database that has WE8ISO8859P1 character set, and I set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1. I do that In the environment before running exp or imp.
-Mark
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Smith, Ron L.
Sent: Monday, June 07, 2004 3:24 PM
To: oracle-l_at_freelists.org
Subject: Oracle Character sets
I am trying to get rid of export and import messages concerning NLS character and language sets.
Can anyone point me to a paper that explains which one(s) to use?
Thanks!
Ron
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org put
 |
 |