I have a 10.2.0.4 database running locally on Windows 2008 64-bit. That database runs with a database character set of WE8ISO8859P1 (not my choice!). If I run SQL*Plus on that server and enquire what client NLS_LANG settings are in place, I get this:
SQL> @.[%NLS_LANG%].
SP2-0310: unable to open file ".[ENGLISH_UNITED_KINGDOM.WE8MSWIN1252]..sql"
And that is probably right: it is a Windows server, after all, so I would have expected MSWIN1252 to be the client character set.
This database communicates with several others, over database links. Most of these other databases are identical to itself: running on Windows servers, 10.2.0.4, set to have database character set of WE8ISO8859P1. But one of them is about to be upgraded to 11g Release 2, running on Linux. That upgraded database is going to run with an AL32UTF8 character set.
The WE8ISO database often sends this sort of thing to what is soon to be the AL32UTF8 database:
insert into sqltable values ('insert into order values (''smith",50,1.5,75)');
That is, the AL32 database contains a table which stores SQL statements, complete with literals (I didn't design this!), and those SQL statements are later performed by the AL32 database against several of the other databases, all of which, like the first, are running on Windows machines, character set WE8ISO8859P1.
My worry is that we might introduce data corruption into this setup because of the assortment of national language settings going on here. In particular, since my WE8ISO databases are going to be clients of the new AL32 one, should the server they run on have NLS_LANG set to WE8ISO8859P1? Even though they run on Windows machines and therefore actually have 'true client charactersets' of MSWIN1252? Does the database character set need to be taken into account, in other words, when setting an appropriaye NLS_LANG registry value if the database involved is going to be a client of another via database links, and that other database uses a different character set from the first?
Happy to elaborate further if my explanation here has been too obscure!