I faced some issues when I query the below table data is not fetching in my query:
SELECT * FROM hr_operating_units
SELECT * FROM MTL_CATEGORIES
Solution:
You just need to check the USER ENV Language from the below query.
SELECT USERENV('LANGUAGE') Language FROM DUAL;
After that run the below query(alter language)
ALTER session SET nls_language='AMERICAN'
then check in your table query output:
ex: SELECT * FROM hr_operating_units
------------------------------
SELECT * FROM hr_operating_units
SELECT * FROM MTL_CATEGORIES
Solution:
You just need to check the USER ENV Language from the below query.
SELECT USERENV('LANGUAGE') Language FROM DUAL;
After that run the below query(alter language)
ALTER session SET nls_language='AMERICAN'
then check in your table query output:
ex: SELECT * FROM hr_operating_units
------------------------------
SELECT USERENV('LANG') FROM DUAL;
USERENV(‘LANG’)
------------------------------
S
SELECT * FROM V$NLS_PARAMETERS
where parameter in('NLS_LANGUAGE','NLS_TERRITORY');
PARAMETER VALUE
--------------------------------------------------
NLS_LANGUAGE SWEDISH
NLS_TERRITORY SWEDEN
b. Set NLS_LANG value in client (This is permanent Solution)
Windows:
i. Go to Start-> run
ii. Type regedit and click ok
iii. Drill Down to HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE-> KEY_OraClientxxx_homeX (xxx is the oracle client version and X is the currently used home)
iv. Double click NLS_LANG and change Value data. For example in the example scenario updated NLS_LANG value to SWEDISH_SWEDEN.WE8MSWIN1252
v. Close regedit
Make sure you have backup windows registry before modifying it.
Or
i. Click on Computer, select Properties.
ii. Select Advance system settings
iii. In the Advance tab, select Environment Variables
iv. Select New
v. Set variable name NLS_LANG and variable value SWEDISH_SWEDEN.WE8MSWIN1252
vi. Select ok and you should now see the new environment variable that you just created.
Linux:
setenv NLS_LANG <NLS_LANG>
Example: setenv NLS_LANG SWEDISH_SWEDEN. WE8MSWIN1252
3. Restart the client. Examples, if you are using Toad restart TOAD for the changes to take effect.
http://www.nazmulhuda.info/setting-nls_lang-environment-variable-for-windows-and-unix-for-oracle-database
