SQL*Plus, Character Sets and Windows

Again and again I stumble upon issues regarding Windows, SQL*Plus and character encoding. Let me share my experience with you.

For whatever reason Microsoft decided to use the PC850 character set for the command prompt (cmd) instead of the MSWIN1252 character set. That means, if you install Oracle on Windows and use MSWIN1252 as NLS character set, the “cmd” will destroy charcters when writing or reading from the database. That means one must be very careful when using “cmd” in combination with SQL*Plus. There is a way around that issue that I will describe.

NLS_LANG

You need to set the value for NLS_LANG explicitly before starting SQL*Plus to the proper value which is “WE8PC850” in that case.

C:\>set NLS_LANG=american_america.we8pc850

C:\>sqlplus marco/*****@******

SQL> create table bla (txt varchar2(100));

Tabl created.

SQL> insert into bla values ('NLS850: äöü');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from bla;

TXT
--------------------------------------------------------------------------------
NLS850: äöü

Change Code Page

You may also change the code page that “cmd” is using before starting SQL*Plus:

C:\>chcp
Active code page: 850

C:\>chcp 1252
Active code page: 1252

C:\>sqlplus marco/*****@******

SQL> select * from bla;

TXT
--------------------------------------------------------------------------------
NLS850: äöü

SQL> insert into bla values ('chcp1252: üöäß');

1 row created.

Commit complete.

SQL> select * from bla;

TXT
-----------------------------------------------
NLS850: äöü
chcp1252: üöäß

Be sure to use a font other than “Raster Fonts” for “cmd”. You can change that in the “Properties” dialogue on the “Font” tab.

Change Code Page permanently

If you are not willing to change NLS_LANG or the code page over and over again when using “cmd” you can also change the default code page for “cmd”. Just add the following registry key:

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Console]
"CodePage"=dword:4e4
C:\>chcp
Active code page: 1252.

C:\>sqlplus marco/*****@******

SQL> select * from bla;

TXT
-------------------------------------
NLS850: äöü
chcp1252: üöäß

Identifying the current NLS_LANG setting

Since the Oracle environment on Windows platforms can be defined via registry settings rather than environment variables it is kind of tricky to query the current setting that is in effect. There is a simple trick to query that setting from SQL*Plus:

SQL> @ .[%NLS_LANG%]
SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8MSWIN1252]"

If you get

SQL> @ .[%NLS_LANG%]
SP2-0310: unable to open file ".[%NLS_LANG%]"

then there is no current setting, neither in the environment nor in the registry.

Additonal Information

You can find a list of valid Windows code pages on MSDN

Advertisements