Setting NLS_LANG Value for Oracle

Introduction

@H_477_6@many times,when you have an Oracle application and you have to support special characters like ö,ä,ü,é,è or currency symbols (e.g. €),you encounter problems with proper display. Mostly,this problem is caused by improper setTing of NLS_LANG value.

NLS_LANG sets the language and territory used by the clIEnt application and the database server. It also sets the clIEnt‘s character set,which is the character set for data entered or displayed by a clIEnt program.

Character Set of Database

When an Oracle Database is created,the DBA has to specify the CHaraCTER SET and the NATIONAL CHaraCTER SET.

Nowadays,the default values are:

• AL32UTF8 for CHaraCTER SET and
• AL16UTF16 for NATIONAL CHaraCTER SET

these Database character sets define which characters (in which format) can be stored in CHAR, CLOB, VARCHAR2resp. in NCHAR, NCLOB, nvarchar2 column. On an exisTing database,you can query the values with:

SELECT * 
FROM NLS_DATABASE_ParaMETERS WHERE ParaMETER liKE ‘%CHaraCTERSET‘; ParaMETER VALUE ========================================== NLS_CHARACTERSET AL32UTF8 NLS_NCHAR_CHaraCTERSET AL16UTF16 2 row(s) SELEcted.

The database character sets do not define if and how charaters are displayed in your clIEnt application!

Some Facts of NLS_LANG

Format of NLS_LANG deFinition is NLS_LANG = LANGUAGE_TERRITORY.CHARSET

All components of the NLS_LANG deFinition are optional; any item that is not specifIEd uses its default value. If you specify territory or character set,then you must include the preceding delimiter [underscore (_) for territory,period (.) for character set]. Otherwise,the value is parsed as a language name.

Following deFinitions are all valID:

• NLS_LANG=.WE8ISO8859P1
• NLS_LANG=_GERMANY
• NLS_LANG=AMERICAN
• NLS_LANG=ITAliAN_.WE8MSWIN1252
• NLS_LANG=_BElgiUm.US7ASCII

If NLS_LANG value is not provIDed,then Oracle defaults it to AMERICAN_AMERICA.US7ASCII.

LANGUAGE and TERRITORY set the default value for many other NLS Parameters,see this table to get an overvIEw. CHARSET is used to let Oracle kNow what character set you are using on the clIEnt sIDe,so Oracle can do the proper conversion. SetTing the LANGUAGE and TERRITORY parameters of NLS_LANG has nothing to do with the ability to store characters in a database. Here,you see a List of available Languages, Territoriesand Character Sets.

You can change the language and territory of your session by:

ALTER SESSION SET NLS_LANGUAGE = ‘...‘; respective ALTER SESSION SET NLS_TERRITORY = ‘...‘;

However,you cAnnot change your clIEnt charset with any sql command,it is set only by the NLS_LANG value.

Some setTing can be explicitly set in sql functions,for example:

SELECT TO_CHAR(sysdate,‘DD Month‘,‘NLS_DATE_LANGUAGE = french‘) FROM dual;

other can not,e.g.:

SELECT TRUNC(SYSDATE,‘DY‘,‘NLS_TERRITORY = AMERICA‘) AS FirsT_DAY_OF_WEEK FROM dual;

does not work.

You cAnnot query your clIEnt charset by any Dictionary or dynamic perfoRMANce vIEw or any other sql command. Also,Dictionary vIEw NLS_SESSION_ParaMETERS shows the database character set,not the clIEntcharacter set!

You can run query:

SELECT CLIENT_CHARSET 
FROM v$sesSION_CONNECT_INFO;

However,the values appear not reliable. Sometimes,it shows NulL or "unkNown".

DeFinition of NLS_LANG

NLS_LANG can be set by Environment variable (e.g. SET NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252) or by your Registry at HKEY_LOCAL_MACHINE\Software\Oracle\KEY_{OracLE_HOME_namE}\NLS_LANG,resp. HKEY_LOCAL_MACHINE\Software\Wow6432Node\Oracle\KEY_{OracLE_HOME_namE}\NLS_LANG for 32-bit Oracle ClIEnt on a 64-bit windows. The Environment variable takes precedence over Registry entry.

You can interrogate exisTing values with:

windows:

reg query HKEY_LOCAL_MACHINE\Software\Oracle\KEY_{OracLE_HOME_namE} /f NLS_LANG
reg query HKEY_LOCAL_MACHINE\Software\Wow6432Node\Oracle\KEY_{OracLE_HOME_namE} /f NLS_LANG
set NLS_LANG

Unix/linux:

echo $NLS_LANG

Proper Value of NLS_LANG

Usually,the values for LANGUAGE and TERRITORY are obvIoUs and less critical in the application. The most interesTing is the CHaraCTER SET value. Many times,you read in forums (and sometimes even in official documentation): "The clIEnt NLS_LANG character set must be the same value as the database character set" - This is simply not true! ConsIDer the database has two character sets,the "normal" and the national character set. On the clIEnt sIDe,you have only one value,so actually they cAnnot be equal. Some character sets are available only on Client side which also vinDicates my statement.

There are two requirements for the NLS_LANG character set:

1. The NLS_LANG character set must support the characters you like to use in your application.
2. The NLS_LANG character set must match the character set (or enCoding) of your application.
@H_338_301@

Some applications/drivers load NLS_LANG deFinition when at launch and derive their character set from NLS_LANG value. In such case,it becomes easIEr and only the first requirement applIEs.

NLS_LANG with sql*Plus

sql*Plus inherits the character set from the terminal session where you started it. On windows,you get the current character set (here called "codepage") with chcp,the linux/Unix equivalent is locale charmap or echo $LANG. Thus,a proper setTing would be for example:

@H_301_321@HIDe   copy Code

C:\>chcp
Active codepage: 850.

C:\>set NLS_LANG=.WE8PC850

C:\>sqlplus ...

With chcp,you can also change your codepage,e.g., chcp 1252. You can use the small batch file to change the codepage of your command line window peRMANently:

HIDe   Shrink 

   copy Code

@H_944_345@@ECHO off

SET ROOT_KEY="HKEY_CURRENT_USER" FOR /f "skip=2 tokens=3" %%i in _ (‘reg query HKEY_LOCAL_MACHINE\SYstem\CurrentControlSet\Control\Nls\codepage /v OEMCP‘) do set OEMCP=%%i ECHO. ECHO ............................................... ECHO SELEct codepage ECHO ............................................... ECHO. ECHO 1 - CP1252 ECHO 2 - UTF-8 ECHO 3 - CP850 ECHO 4 - ISO-8859-1 ECHO 5 - iso-8859-15 ECHO 6 - US-ASCII ECHO. ECHO 9 - reset to System Default (CP%OEMCP%) ECHO 0 - EXIT ECHO. SET /P CP="SELEct a codepage: " if %CP%==1 ( echo Set default codepage to CP1252 reg add "%rOOT_KEY%\Software\Microsoft\Command Processor" /v autorun /t REG_SZ /d "chcp 1252" /f ) else if %CP%==2 ( echo Set default codepage to UTF-8 reg add "%rOOT_KEY%\Software\Microsoft\Command Processor" /v autorun /t REG_SZ /d "chcp 65001" /f ) else if %CP%==3 ( echo Set default codepage to CP850 reg add "%rOOT_KEY%\Software\Microsoft\Command Processor" /v autorun /t REG_SZ /d "chcp 850" /f ) else if %CP%==4 ( echo Set default codepage to ISO-8859-1 add "%rOOT_KEY%\Software\Microsoft\Command Processor" /v autorun /t REG_SZ /d "chcp 28591" /f ) else if %CP%==5 ( echo Set default codepage to iso-8859-15 add "%rOOT_KEY%\Software\Microsoft\Command Processor" /v autorun /t REG_SZ /d "chcp 28605" /f ) else if %CP%==5 ( echo Set default codepage to ASCII add "%rOOT_KEY%\Software\Microsoft\Command Processor" /v autorun /t REG_SZ /d "chcp 20127" /f ) else if %CP%==9 ( echo reset codepage to System Default reg delete "%rOOT_KEY%\Software\Microsoft\Command Processor" /v autoRun /f ) else if %CP%==0 ( echo Bye ) else ( echo InvalID choice pause )

Note,the setTings will apply only for the current user. If you like to set it for all users,replace line:

HIDe   copy Code

SET ROOT_KEY="HKEY_CURRENT_USER"

by:

HIDe   copy Code

SET ROOT_KEY="HKEY_LOCAL_MACHINE"

Be careful with codepage UTF-8 (chcp 65001) there is a BUG,see this discussion. I do not kNow whether this has been fixed in more recent windows / sql*Plus versions.

NLS_LANG with .sql files

When you run sql files in sql*Plus,check the save options of your editor. Typically,you can choose values like ISO-8859-1, UTF-8, ANSI, CP1252 as enCoding. Term "ANSI" denotes the default windows code pages. On a Western PC,this is CP1252.

You can interrogate default windows code pages with:

@H_818_618@ HIDe   copy Code

C:\>reg query HKEY_LOCAL_MACHINE\SYstem\CurrentControlSet\Control\Nls\codepage /v ACP

HKEY_LOCAL_MACHINE\SYstem\CurrentControlSet\Control\Nls\codepage
    ACP    REG_SZ    1252

C:\>

SELEct dump(‘€‘) from dual; DUMP(‘€‘) ----------------- Typ=96 Len=1: 164

byte[] o = new byte[] { 164 }; foreach ( var enc in EnCoding.GetEnCodings() ) { var convertedString = enc.GetEnCoding().GetBytes("€"); if ( convertedString.SequenceEqual(o) ) Console.Writeline(String.Format("{0}\t{1}\t{2}",enc.codepage,enc.name,enc.displayName)); }