Skip to main content

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 is used by Win32 Applications which is originated from IBM PC Architecture

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
  • 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!!

Popular posts from this blog

Data Guard Configuration with Oracle DGMGRL

About Data GuardEfficient business operations, quality customer service, compliance with government regulations, and safeguarding corporate information assets all require high levels of data protection and data availability. Thus it is no surprise that data protection and data availability are among the top priorities for enterprises of all sizes and industries.
A set of questions one needs to ask him/herself is what If a disaster impacts ongoing business transactions, How soon can those systems be back to business again, How much Data is affordable to loose, is it acceptable? The only answer for all these questions to position a proper "Business Continuity Plan (BCP)" in place so that it helps business to grow inline with its consumer expectations.  Technically, this is where Oracle's Data Guard plays a vital role that ensures superior reliability, and rock solid performance. DataGuard is Oracle's complete Disaster Recovery Solution which can reliably deliver aggressi…

IPC Send timeout detected - The Story of a Database

Some errors make us panic, nervous, and beyond belief when we positively attempt to resolve a panic situation. Specific to case of Clusterware where it attempts to resolve a conflict by removing dead resources from the cluster but it suffers because of lack of additional resources to complete the task, and makes the whole situation even worst so everything that has in contact with the key resource also suffers. The key resource what we are talking is a RAC Database and the Clusterware under a panic situation is Oracle Clusterware.

Here's the environment
Oracle RDBMS GI Node RAC Database with vault function enabledNetApp NFS Filer based Storage
What was the issue?
On Instance 3 I have noticed "IPC Send timeout". Fri Aug 14 04:46:05 2015 IPC Send timeout detected. Receiver ospid 20545 [ Fri Aug 14 04:49:55 2015 And then, Oracle has started suspending MMON actions one by one as we noticed below messages in the log Fri Aug 14 10:49:07 2015 Suspending M…