Which characterset does TOAD use when connecting to the server?
It seems to be irrelevant what is set in the registry as NLS_LANG.
BR
Dirk
It should be NLS_LANG.
In Toad, Check Database -> Administer -> NLS Parameters. Look on the "Session" tab.
There's no characterset parameter listed:
Parameter Value
NLS_CALENDAR GREGORIAN
NLS_COMP BINARY
NLS_CURRENCY ?
NLS_DATE_FORMAT DD.MM.RR
NLS_DATE_LANGUAGE GERMAN
NLS_DUAL_CURRENCY ?
NLS_ISO_CURRENCY GERMANY
NLS_LANGUAGE GERMAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS ,.
NLS_SORT GERMAN
NLS_TERRITORY GERMANY
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
I changed the value in the registry for the client I am using for a test (to US7ASCII). Nevertheless, special characters are still displayed.
BR
Dirk
It's really the Oracle client that determines that, not Toad.
Maybe you have an environment variable that is overriding the registry setting?
Look in Control Panel > System. In the dialog, click the Advanced tab, then Environment Variables.
Hi Dirk,
you only ever see the database characters details in the NLS_DATABASE_PARAMETERS view which is what Toad is looking at. The instance and session character sets never show up in that screen. (Unless my version of Toad is too old, but querying the three NLS_%_PARAMETERS views only shows the same information.
select * from nls_session_parameters;
select * from nls_instance_parameters;
select * from nls_database_parameters;
What exact problem are you having? You say that when you swapped the client session's registry to US7ASCII, you still saw "special characters". Were these upside down question marks or similar?
When the client connects with a different (or too different) character set to that of the database, then usually a warning is given that translation will take place. What the client then sends to the database is translated to something that the database can handle and stored in the tables.
No matter what character set any other client is using, those stored characters that got translated will, probably, never come back to what they should be. They are, effectively, corruptions in your database.
I suffered this many years ago when the databases used in-house ere defaulted to US7ASCII while some of our European customers obviously did it correctly and used an 8 bit characterst to allow their languages accented characters. When we imported database dumps from them to run tests or whatever it was back then, the top bit was stripped out and we lost their accents. Effectively, we had imported oranges and got a database full of apples.
So, what are your exact problems please? If I know then I should be able to help.
One other thing, in Toad you can configure the grids to use a font that itself doesn't handle special characters and so on, this in conjunction with character set translations, makes debugging the problem harder.
And don't get me started on people who copy "smart" quoted text from Word or Excel etc into my databases!!! 
Cheers,
Norm. [TeamT]
Thank you very much for the information.
The reason for my question is the following:
We have a DB whose character set is configured to AL32UTF8 and an application that has problems displaying Unicode characters. (The app is a bit older 
and currently uses WIN1252 as its character set).
I wanted to make sure that the test data we made available to our colleagues in development was correct and that it arrived correctly at the client when it was set to UTF8.
Therefore I tried to set different character sets for my Oracle client. Strangely enough, the result of a SELECT in TOAD is always the same. The characters are always displayed correctly, there is no conversion.
I have already checked with the DUMP function whether the data is stored correctly on the server. That looks good so far.
An environment variable is not defined. I checked this in the control panel as well as on the command line.
Maybe it's because I have installed a total of 5 Oracle clients:
10.2 32bit
12.1 64bit
12.2 32bit
18.0 32bit
18.0 64bit
I am using the 64bit version of TOAD and have selected the client 12.1 64bit for the database connection.
BR
Dirk
The OCI has 2 sets of API calls - one for unicode, one for non-unicode. Toad uses the unicode calls, so when connecting to a unicode database, the NLS_LANG doesn't matter because our strings are unicode. It would matter if Toad were using the non-unicode OCI calls and sending or receiving non-unicode strings.
for the old app:
Make sure that the windows "language for non-unicode programs" (control panel->region->administrative) is correct to display the data, that the font supports it, and that whichever client your old app uses has the NLS_LANG that you want set in the registry.
Disclaimer:
I think this is all correct. It's confusing to me too.
Hi, John,
thank you very much for the info. This explains why the TOAD always displays everything correctly, no matter what value I have configured for NLS_LANG.
The old app is currently being converted to Unicode. Therefore I just wanted to make sure that the test data in the DB is correct.
BR
Dirk