Setting NLS_LANG on Windows for Oracle Database

Sometimes, it is difficult to deal with Windows Platform as it drains the hell out of us!! Recently, I have come across a situation where one of my client's requirement was to input Arabic language into Oracle Database [or] read/retrieve the output into Various client Applications such as PL/SQL Developer/SQL Developer/TOAD etc.. Inputting the non-english language into Database  has never been difficult as we are given plenty of language options within our beloved Oracle Database, but, the problem lies within the complex Windows OS when user wanted to view the data in his/her beloved language, in Applications such as PL/SQL Developer or SQL Developer etc.. This post is all about it.

Character Set, Character encoding, & Code point

Yes, it is a group of characters that is recognised by the Hardware through the OS Interface. Every character is allocated a number, called a code point, these code points will be represented in the computer by one or more bytes. So, every character has to be unlocked or cracked by reading this code in order to either understandable by machine or readable by the User, which is called Character encoding

How Windows OS deals with this Character Set?

In Windows, Character Sets are called Code Pages (CP). A Code page is set of characters that used to be the conventional approach of Character encoding within Windows OS, was replaced by "Unicode" later point in time otherwise still very much compatible to use. There are basically 3 types of Code Pages we see within Windows Registry,

• ACP

ACP is ANSI Code Page used for native non-unicode Application using a Graphical User Interface on Windows Systems.

• OEMCP

OEMCP is used by Win32 Applications which is originated from IBM PC Architecture

• MACCP

Indicates the default code page for characters in non-Unicode Macintosh programs. 

How to do it?

Step (1) : Change the ANSI Code Page (ACP) (Change the Locale)

Changing the Code Page could be same as changing the locale of the system for almost all times. It can be done within the registry or Control Panel settings, but Control Panel method is strongly recommended. Changing it within the registry may cause system to malfunction or even get crashed on the boot that obviously requires a Backup to restore, and Recovery which is quite dangerous and time consuming.  

This post's objective is to enable the non-english(Arabic in this case) code page for third party GUI applications, so reflecting the ACP to desired code page number will do the job for us.  Following is the procedure I have followed successfully, the desired ANSI code page for Arabic language is 1256. You may check what is desired for your environment here

• Start->Control Panel
• Double click the Regional Language and Options setting
• Choose Administrative tabe
• Click on Change System Locale
• Choose the desired locale, Arabic(Lebanon) in this case
• It will prompt you to restart to reflect the change in effect, restart the system

How to determine the change if reflected, please follow below procedure,

• Start->run
• Type "regedit", and enter, it will take you to the Windows Registry
• HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage, Scroll down to the bottom
• Below screenshot locates the same,

Step (2) : Change the NLS_LANG Parameter within the Windows Registry of ORACLE_HOME

Our next step is to take care of our Oracle environment respect to Operating System or what we need,

Please follow procedure below for the same to get it done,

• Start->run
• Type "regedit", and enter, it will take you to the Windows Registry
• Then, HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_<oracle_home_name>
• Change the NLS_LANG to the desired value, in this case, it is "AMERICAN_AMERICA.AR8MSWIN1256".
• Below screenshot locates the same,

Step (3) : Change NLS_LANG in System's Environment Variables to the same value that has been set in the ORACLE_HOME registry

• My Computer->Right Click->Properties
• Choose advanced system settings
• Click on Environment Variables
• Click New on both User Variables and System Variables, and add NLS_LANG to the same value that has been set in both ORACLE_HOME registry and Windows Code Pages, im this case, it is "AMERICAN_AMERICA.AR8MSWIN1256"
• Below screenshot locates the same,

Step (4) : Set the Character Set of the Database to the same character set that has been set as above during the Database Creation

Below screenshot locates the same,

Step (5) : Check on PL/SQL Developer or SQL Developer If Database is able take the Arabic, and Client PL/SQL Developer is able to read it and display in Arabic

Below screenshot shows the same,

I wish this helps those who seek to set correct NLS_LANG on Windows platform, Your feedback on the blog post is very much appreciated. Many Many Thanks!!