In our database german umlauts are displayed as ASCII. Here some examples

Ü is displayed as "š"
ä is displayed as "„" 
Ä is displayed as "Ž" 
ö is displayed as "”"
Ö is displayed as "™" 

Here some Oracle enviroment variables:

SELECT distinct client_charset FROM v$session_connect_info
WHERE sid = sys_context('USERENV','SID');
retuns Unknown

select * from v$NLS_PARAMETERS;
NLS_LANGUAGE    GERMAN  
NLS_CHARACTERSET    WE8MSWIN1252    

I have also noticed, that there is no NLS_LANG setted up. Can someone give me any clue how to check if this is a problem of env variable or character-encoding itself? Or how to fix it so that the umlauts would be displayed correctly? Thanks in advance!

CodePudding user response：

The first sentence is already wrong, it must be "my client application displays german umlauts as ..." Characters like Ž are not ASCII either. The Oracle environment variables have nothing to do how your client displays any characters.

There could be two reasons:

• Characters like š „ Ž ” ™ are really stored in your database because they are inserted wrong, and you just think it should be Ü ä Ä ö Ö

• Your client uses the wrong character set.

NLS_LANG is used to tell the database "my client uses character set XYZ" - no more, no less! Well, some clients inherit the NLS_LANG value for own character set setting but that is not always the case.

Actual answer:

You have wrong data in your database! When you inserted the data then your client was using CP437 (Oracle Character Set US8PC437) but you did not tell this the database.

Most likely data was inserted by sqlplus which inherits the codepage from cmd.exe which is by default 437 on English Windows machines. NLS_LANG character set was set to WE8MSWIN1252. When character set of client and database is the same then data is transferred byte-by-byte without any conversion - there is the mismatch!

Solutions:

• Before you run your sqlplus script Set NLS_LANG accordingly, e.g.

  SET NLS_LANG = .US8PC437
  sqlplus ...
  

  Language and Territory are optional and thus can be skipped.

• Your database uses character set WE8MSWIN1252, then it would be beneficial to use it also by your client. By this you can insert and display any character which is supported by the database (no less but also no more)

  chcp 1252
  SET NLS_LANG = .WE8MSWIN1252
  sqlplus ...
  

Correction of wrong data:

In order to correct the wrong data try this:

UPDATE table_name SET column_name = CONVERT(column_name, 'WE8MSWIN1252', 'US8PC437');

Note, this should be a one-time correction only. Do not put such update as part of your application code!