CODEWITHSUNDEEP
oracle-databasecharacter-encodingenvironment-variablesspecial-characters
hajduk
hajduk

Reputation: 121

Special characters (german umlauts) are displayed as ASCII

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!

Upvotes: 0

Views: 3954

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

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!

Update

For correction of PL/SQL code, try this one (not tested):

Create a SQL script like this:

SET HEADING OFF 
SET FEEDBACK OFF 
SET NEWPAGE NONE 
SET VERIFY OFF 
SET TRIMSPOOL ON 
SET DEFINE OFF 
SET ECHO OFF 
SET TERMOUT OFF
SET PAGESIZE 0
SET LINESIZE 32767
SET LONG 2000000000
SET LONGCHUNKSIZE 32767 

spool PACKAGE_NAME.sql;
SELECT DBMS_METADATA.GET_DDL('PACKAGE', 'PACKAGE_NAME', USER) FROM DUAL t;

spool OFF;
EXIT

And the run it like this

c:\> chcp 437
c:\> SET NLS_LANG=.WE8MSWIN1252
c:\> sqlplus -s ... @<above script>

c:\> SET NLS_LANG=.US8PC437
c:\> sqlplus ... @PACKAGE_NAME.sql

Upvotes: 2

Related Questions