Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: A question about NLS Language settings

RE: A question about NLS Language settings

From: Justin Cave (DDBC) <>
Date: Thu, 14 Apr 2005 12:45:04 -0600
Message-ID: <87E9F113CEF1D211A4C300902730187475905C@ddbcinc.ddbc.local>

I am assuming here that you changed the application server configuration so that the NLS_LANG there matched the NLS_LANG of the database server
(i.e. your database character set is WE8ISO8859P1).

When the Oracle client and the Oracle database are using the same character set, the Oracle client assumes that there is no need to do a character set conversion. The database trusts the client to pass in valid ISO 8859-1 data and stores whatever binary format is passed in.

The problem here, though, is that the client has been configured to lie. When it passes in these special Microsoft characters, it is not passing in a valid ISO 8859-1 data stream, it is most likely passing in a Windows-1252 data stream. Microsoft's curly quotes cannot be represented in the ISO 8859-1 character set.

So long as the data stays in this system, and so long as every application that accesses the data pretends that the data stream is ISO 8859-1 when dealing with Oracle but really displays the values using the Windows-1252 character set, things will probably work. I can build applications that appear to store and retrieve Chinese data from a US7ASCII database, for example. Oracle doesn't support this sort of configuration and has threatened that it may not continue to work in future releases, but for today it will just work.

The problem comes down the line when you have to exchange data with another system. If you are going to configure replication between two systems, for example, and they have different character sets, you need to have a character set conversion take place. If you have a system that advertises that it has ISO 8859-1 data when it really has Windows-1252 data, however, that character set conversion won't work properly and will corrupt the data stream. =20

Justin Cave <>
Distributed Database Consulting, Inc.

-----Original Message-----
[] On Behalf Of
Sent: Monday, April 11, 2005 8:48 AM
To:; Subject: RE: A question about NLS Language settings

We ran into a problem in one of our applications where the users would cut text from a varchar2(2000) field, edit it in Word, then paste it. The problem was that certain characters like double-quotes apparently were "unknown" in the default characterset and where stored as upside down question marks. To get around this my colleague changed the configuration in the application server files to:

AMERICAN_AMERICA.WE8ISO8859P1 Some questions, if this is a superset for the default then why isn't it the default?
Are there any pitfalls to setting this in our .profile on our database server as well as our application server so that when we export/import
(for example) we don't have problems?

What about backups and restores - will this be an issue and should we set this for backups and restores.

I can test the 3rd issue fairly easily but I would like to know if anyone has had experience with this issue and how they dealt with it.


Received on Fri Apr 15 2005 - 00:18:24 CDT

Original text of this message