Local Langugae in Oracle using NLS_LANG

There might be cases where you may need to extract the data stored in the database in the local language along with the English for some reporting purpose Here is the way how it can be achieved. Pre-requisite is that data must be stored in the Data Base in the local language character set.
1) Change the language setting from the control panel.

Regional and Language Options -->
Regional Options
 form the lov {customize} (in this case I have chosen “Chinese”)

Advanced In the lov select
System will restart...
and the new language setting will be activated.

2) Change the variable NLS_LANG in registry... for the sql*plus where you want to run the query.

Get the vale from the database using the following query..

SELECT nls_language || '.' || nls_codeset AS nls_lang

  FROM fnd_languages
 WHERE nls_territory LIKE '%CHIN%';

Run ----> regedit--> 
HKEY_LOCAL_MACHINE --->
SOFTWARE -->
oracle -->

search for NLS_LANG Variable in the registry for the oracle home where you want to run the query.

To know more about the NLS_LANG click

NLS_LANG is a client side environmental variable. To specify the locale behavior- setting the NLS_LANG environment parameter is the simplest way.

With the setting of NLS_LANG parameter on client machine it is specified the language, territory and character set used by the client application. As through NLS_LANG parameter, client character set is also specified so oracle has an idea which is the character set for data entered or displayed by a client program as well as Oracle can do (if needed) conversion from the client's character set to the database character set.

After changing the settings re-launch the sql*plus session and start getting the output in whatever local language it may be..

Note: Dont forget to chnage back to the original values once you have completed your activity.

~~~ Cheers