Friday, May 22, 2009

Oracle 11g SQL*PLUS on Windows - fighting NLS_LANG, code pages and character sets

Oracle has dumped sqlplusw - The native Windows SQL*PLUS - from windows in 11g - so now there is only the crappy command shell version.

So, how do we enter non-keyboard characters in SQL*PLUS now that Oracle has dumped sqlplusw (windows gui sql*plus)?

More importantly, to avoid incorrect character conversion and therefore possible data corruption , before using sqlplus in the command prompt or ANY Oracle command line tool, you need to MANUALLY SET the Oracle NLS_LANG parameter, and or the Windows  OEM code page to specify an Oracle client character set or Windows code page  that are compatible.

This little epistle discusses how to run sql*plus in a command shell AND avoid NLS / character set problems.

Code Pages

On Windows, GUI applications and command shell applications do not use the same code page (unless in a Chinese-Japanese-Korean environment).

Windows uses 2 different character sets for the ANSI (GUI) and the OEM (Command shell) environments. For most English PC's the ANSI code page is 1252.

To check what code page your command prompt (OEM code page) uses, open a command prompt and type "chcp"

Alternatively, open regedit and go to:


C:\Documents and Settings\kbrocas>chcp

Active code page: 437

So on my system I'm using OEM code page 437.

A bit about how Oracle reads the registry:

When starting Oracle applications on Windows, it reads the oracle.key file located in the same directory to determine which registry tree to use. The Oracle registry entry specifies the NLS_LANG value for your Oracle client applications.

Setting NLS_LANG appropriately for the command shell

The old sqlplusw ran with the ANSI code page, with NLS_LANG set appropriately to the matching Oracle character set in the Oracle registry .

For example, in my case, the oracle installer has set NLS_LANG to Oracle character set WE8MSWIN1252 which maps to my Windows ANSI code page 1252.

However, command-line sqlplus runs with the OEM code page, and when it reads the registry, it gets NLS_LANG=WE8MSWIN1252  - which is not appropriate for the command shell OEM code-paged sqlplus.

We have to change / set the NLS_LANG parameter ine the command prompt/console session correctly otherwise incorrect character conversion can corrupt error messages and data.

For example, in my case:
C:\Documents and Settings\kbrocas>set NLS_LANG=ENGLISH_AUSTRALIA.US8PC437

This over-rides the registry value of NLS_LANG that sqlplus would use by default.

Now the Oracle client applications correctly identify the character set they are running with when they connect to the database, allowing appropriate character set conversions between client and server.

Sounds good? Yes - until you realise that the ANSI character set I'm using is a 7-bit ASCII character set - I can't enter any fancy characters! How about changing the default OEM codepage in the command prompt so that it matches the GUI codepage, or at least some code page that supports 8+ bit characters?

Well, yes you can....

Changing the windows command prompt OEM code page

You can change the code page in the console just for the session, or for all time.

By example, to change from the standard OEM code page to the standard MS Windows Latin 1 code page:

For the current command shell/console

C:\> chcp 1252

C:\Documents and Settings\kbrocas>chcp 1252

Active code page: 1252

You can now enter weird characters. Remember to unset NLS_LANG in the command prompt if you have already set it before connecting to the database with client tools.

For all time

NOTE: Some people have reported that if the code page specified here is not available on the system, you may have trouble booting afterwards! I put this here as a reference, however I need to do more research on this before I can recommend it. Some suggest using code page 65001 instead.


Go to [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage]

Change the "OEMCP" value to "1252" for the standard MS Windows Latin 1 code page.



Configuring Oracle Database Globalization Support, Oracle Database Client Installation Guide 11g Release 1 (11.1) for Microsoft Windows

Configuration Parameters and the Registry
Oracle Database Platform Guide 11g Release 1 (11.1) for Microsoft Windows

Oracle Globalization FAQ


Code Pages

Character sets

Character sets and codepages

Code Pages Supported by Windows

Use Locale Model

Encodings and Code Pages



Other pages I found useful

Code page 437

That Ol' OEM Code Page

Keep your eye on the code page

Why ACP != OEMCP (usually)


Windows' command prompt : How to DEFINITELY change default codepage


  1. j0bfNL gdmxdehwcedw, [url=]pkypdowzygpr[/url], [link=]rsnvkfxduxbv[/link],

  2. qWuqwK vhrftlupdgkx, [url=]tfbxajgabjtn[/url], [link=]rqaxtjssdrlr[/link],