nls lang - Why Oracle displays ??? for special characters like åäö

Question

Some time ago my PL/SQL stopped showing characters ???.

I've tried reinstalling oracle_home_11g, PL/SQL cleaned the registry but the problem remains.

Anyone know why?

Answer 1

Most probably it is due to the mismatch between the locale-specific NLS characterset and the database characterset. Or else, the NLS_LANG value is not correctly set in the OS environmental variable.

Have a look at Why are junk values/special characters/question marks displayed on my client?

1. Your client charaterset doesn't match with database characterset.

You can compare between :

-- locale-specific characterset

select value
from   v$nls_parameters 
where  parameter = 'NLS_CHARACTERSET';


-- database characterset

select value
from   nls_database_parameters 
where  parameter = 'NLS_CHARACTERSET';

If you see a mismatch between the two, then set the locale-specific NLS characterset to that of database characterset.

2. If the above charactersets match, then you need to set the NLS_LANG value in the OS environmental variable.

For Windows OS, the format is:

[NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET]

Follow the instruction in the documentation regarding setting up the NLS_LANG : Setting the NLS_LANG Environment Variable for Oracle Databases