Oracle Code Page usage on Windows - 2 different NLS_LANG in Registry

About the question 'how do I control which one is used?' that depends on the Oracle Home select that can choose HOME0 or HOME1

What I mean in regarding consolidating is that we are reducing down to just one NLS_Lang.

What do you mean with Oracle home select. Our clients use the oracle client to query different Oracle databases and the databases have different codepages. How can I tell one application to use the one Oracle home that I want.

Hi,
About Oracle Home, you can have several installations of oracle software (for example different client version) and each one has its registry entry.

About NLS_LANG you are not supposed to have the same codepage than the database one.
The database codepage is the codepage of data.
The client codepage is the codepage of the client entry, and that depends on the client. Windows and Unix do not have the same codepages.

So, you may have (for example):
Database with WE8ISO8859P1
Windows client with WE8MSWIN1252
Unix client with WE8ISO8859P1

Then when windows user enters data it is converted from WE8MSWIN1252 to WE8ISO8859P1
If windows client reads that data, it is converted back to WE8MSWIN1252
If unix lient reads that data it is converted to WE8ISO8859P1- so in this case no conversion occurs

This is the only way to have data that can be manipulated by different plateforms
Each client defines its codepage (the one that the client OS can display)
The database stores it in a codepage that is compatible with all possible clients
Conversion occurs when needed.

But now, imagine you already have data that was entered with a bad codepage.
For example you have WE8ISO8859P1 database and windows clients that are configured as WE8ISO8859P1because of a mistake at installation.

What happens is that because you have the same codepage on both database and client, no conversion occurs. So as far as you always have windows client, you will see the right data. Not because it was the right configuration, but because there were no conversion so you read what you have written.

You see right data but data is not good: as no conversion occurs and as data was entered by windows, data is stored as WE8MSWIN1252 ... but in a WE8ISO8859P1 database ...

But imagine now that you have a unix client, configured with WE8ISO8859P1 No conversion occurs as well but now you see bad data. Because the character you entered on windows do not have the same ascii code on unix, so a conversion is mandatory (not for 0-127 ascii that are common however)

And imagine you want to correct that and put you windows clients with WE8MSWIN1252.
Now the data that was entered before will be badly displayed, because there is a WE8ISO8859P1->WE8MSWIN1252. A wrong conversion because data is stored incorrectly as WE8MSWIN1252.

So, it is a good idea to make things right: all windows client with WE8MSWIN1252

But you need to check existing data before that or all characters that are not 0-127 ascii characters will be corrupted. For example, you will need to convert them from WE8MSWIN1252 to WE8ISO8859P1if they were entered by a windows client that had WE8ISO8859P1instead of WE8MSWIN1252

Regards,
Franck.

OK, slowly I'm learning more about how Oracle works. Our enviroment is a complete windows one, servers and clients. I want to upgrade from 9 to 10 and our standard server nls_lang is GERMAN_GERMANY.WE8MSWIN1252 which is the same on our clients. On our earlier DBs we had WE8ISO8859P1. With export done from a client with WE8MSWIN1252 the data should be properly converted, if I remember correctly. The import was done also from the client with the 1252 code page. My import ran fine and as far as I can tell the data is ok.
My problem is still when I have two different code pages (HOME0 & HOME1) how can I get application 'A' to use the codepage info from HOME0 and app 'B' from HOME1?

Hi,

I read the Oracle documentation and am now in the process of trying to figure out how to get my appliction to use home0 or home1. Right now I've got tons of different things going on and this problem doesn't have that high of a priority right now.

Hi Franck,

When I read your answers here correctly (first answer), when I've got a all windows system (Server is Windows 2003) and all our clients are XP clients, then I should be using WE8MSWIN1252. No conversion is necessary, all DBs have the same code page. Correct?

In order to use different Oracle Homes I need to have the Home Selector installed?

Theoretically I could have two different oracle clients installed whereby they would have different settings (code page in my case) and when I need the 'other' home I could run per batch the script: ohsel -switch "Oracle Home Name"
to set the one I need. After that my software that uses the Oracle Client runs with the needed code page.  Correct?

Thanks for your assistance here,
DSchat

Yes, that's right.
Regards,
Franck.