What is the current setting of NLS_LANG in sqlplus?

Post author By Laurent Schneider
Post date May 10, 2011
18 Comments on What is the current setting of NLS_LANG in sqlplus?

I just learnt a neat trick from Oracle Support.

How do you see the current value of NLS_LANG in SQLPLUS ?

HOST is not the right answer.

E.g.:
Unix:
SQL> host echo $NLS_LANG AMERICAN_SWITZERLAND
Windows:
SQL> HOST ECHO %NLS_LANG% %NLS_LANG%

The correct setting is revealed by @.[%NLS_LANG%]
E.g.:
Unix:
SQL> @.[$NLS_LANG] SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]"
Windows:
SQL> @.[%NLS_LANG%] SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]"

It could well be that both return the same answer, but not necessarly, as shown above.

The unix discrepancy is related to the subshell created by HOST. The subshell may read some .profile and overwrite the value of NLS_LANG

In Windows, the NLS_LANG setting may be set by sqlplus according to some registry entries

18 thoughts on “What is the current setting of NLS_LANG in sqlplus?”

Maxim May 10, 2011 at 15:00

The oracle support surely can rely on the fact – they never login as sys and, there can impossible lie around an sql script with cryptic name “.[AMERICAN_AMERICA.WE8ISO8859P1]”
and content like ‘grant dba to public’ or ‘drop tablespace sysaux including contents and datafiles’

😉
Laurent Schneider Post authorMay 10, 2011 at 15:44

Indeed !

ok, a laurent variant then 😉

SQL> def _editor=echo SQL> ed $NLS_LANG AMERICAN_AMERICA.we8iso8859p1
Laurent Schneider Post authorMay 10, 2011 at 17:04

and to get the server setting
SQL> create tablespace t datafile '/tmp/$NLS_LANG' size 128K;
Tablespace created. SQL> select file_name from dba_data_files where tablespace_name='T'; FILE_NAME --------------------------------------- /tmp/AMERICAN_AMERICA.WE8ISO8859P1 SQL> drop tablespace t including contents and datafiles;
Tablespace dropped.

Of course exec sys.dbms_system.get_env('NLS_LANG',:v) is better, but create tablespace is so crazy, I had to mention this 😆

PS: if you have “only” dba but not execute on DBMS_SYSTEM, it may even be useful 😈
Maxim May 10, 2011 at 17:51

Yet another “just for fun”

ssh localhost
Last login: Tue May 10 17:47:55 2011 from localhost.localdomain
oracle@muclx01:~ >echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8
oracle@muclx01:~ >export NLS_LANG=GERMAN_RUSSIA.WE8ISO8859P15
oracle@muclx01:~ >sqlplus scott

SQL*Plus: Release 11.2.0.2.0 Production on Di Mai 10 17:48:34 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Kennwort eingeben:

Verbunden mit:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> !ps eww $PPID|sed “s/\s/\n/g”|grep -i nls_lang
NLS_LANG=GERMAN_RUSSIA.WE8ISO8859P15

SQL>
Boris May 10, 2011 at 19:50

For Windows “host set NLS_LANG” will do the job.
joel garry May 10, 2011 at 23:18

@Boris:

No, it won’t. Try it on XE, it will say Environment Variable nls_lang not defined. And even if it was, don’t you think it could be altered session only?
Boris May 11, 2011 at 09:16

@joel garry
Yes, it does 🙂
We want to check if the environment variable NLS_LANG is set; not the current session NLS settings that could be derived from many sources (registry, logon trigger etc). If the OS says the variable is not set it means that it is not set.

$ echo host set NLS_LANG | sqlplus -s /@dev Environment variable NLS_LANG not defined
$ set NLS_LANG=AMERICAN_AMERICA.UTF8
$ echo host set NLS_LANG | sqlplus -s /@dev NLS_LANG=AMERICAN_AMERICA.UTF8
Laurent Schneider Post authorMay 11, 2011 at 10:08

@Boris
if NLS_LANG is not set as an environment variable, then sqlplus will initialize NLS_LANG with a registry entry. Which you can see with ed/spool/@, but not with host
Boris May 11, 2011 at 10:59

OK, but what’s the point to see the derived value when you can see it using sys_context(‘USERENV’,’LANGUAGE’)? I’ve thought that the whole idea was to check whether the NLS value is set from the variable or not.
Laurent Schneider Post authorMay 11, 2011 at 12:31

sys_context(‘USERENV’,’LANGUAGE’) is good, it may also reflect some ALTER SESSION or database / instance parameters

@ or ED even works without a connection (sqlplus /nolog). So it is not “server” specific, but really “client” specific.
Sokrates May 13, 2011 at 13:51

alternative solution for linux clients:

$ uname -a
Linux devnull.market-maker.de 2.6.34.8-68.fc13.x86_64 #1 SMP Thu Feb 17 15:03:58 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux

$ export NLS_LANG=MALAYALAM_ECUADOR.UTF8
$ sqlplus sokrates@…
SQL> select process from v$session where sid = (select sid from v$mystat where rownum=1);

PROCESS
————————————————————————
17916

SQL> !(cat /proc/17916/environ; echo) | tr “00” “\n” | grep NLS_LANG
NLS_LANG=MALAYALAM_ECUADOR.UTF8
sriram May 19, 2011 at 05:15

http://www.oracle.com/technetwork/database/globalization/nls-lang-099431.html#_Toc110410545

And From Database

SQL> select ((select value from NLS_DATABASE_PARAMETERS where parameter = ‘NLS_LANGUAGE’)||’_’||
2 (select value from
3 NLS_DATABASE_PARAMETERS where parameter = ‘NLS_TERRITORY’)||’.’||
4 (select value from NLS_DATABASE_PARAMETERS where parameter
5 = ‘NLS_CHARACTERSET’)) from dual;

((SELECTVALUEFROMNLS_DATABASE_PARAMETERSWHEREPARAMETER=’NLS_LANGUAGE’)||’_’||(SE

——————————————————————————–

AMERICAN_AMERICA.WE8MSWIN1252

SQL> @.[%NLS_LANG%]
SP2-0310: unable to open file “.[AMERICAN_AMERICA.WE8MSWIN1252]”
SQL>
Laurent Schneider Post authorMay 19, 2011 at 09:43

thanks for tthe note, pretty good reference !
sunilbhola September 29, 2011 at 04:14

select value from v$nls_parameters
where parameter in (‘NLS_LANGUAGE’,’NLS_TERRITORY’,’NLS_CHARACTERSET’);
Kate Johnson August 13, 2015 at 23:54

You have no idea how helpful the simple “host echo $NLS_LANG” has been. So many factors come into play to dink around with its value by the time sqlplus makes its appearance. Thank you.
Seb April 24, 2019 at 15:55

And when NLS_LANG is not set ?, any idea where utilities get their messages language from ??
Cause on different servers over here we end up with French ” lignes selectionnees” instead
of ” rows selected”… On other servers message language is OK (English).

On our servers NLS_LANG is set *nowhere*, so by default where does SQL*Plus go get its language
from ? How come we experience such a difference ? Any idea ? (I’ve done all research on
environment variables I could imagine, grep -i lang, grep -i nls, grep -i locale etc. but
found no difference on a server where messages are in English and another one where they
are displayed in French ! – these are IBM AIX 7.2 machines)

Thanks.
Seb
Laurent Schneider Post authorApril 25, 2019 at 06:35

Hey Seb
Maybe NLS_LANGUAGE in the init.ora?
I’ll try to check what happend when NLS_LANG and NLS_LANGUAGE are not set and write a new post, stay tuned
Seb April 25, 2019 at 08:55

Indeed – something that escaped me… We accidentally added both NLS_LANGUAGE and TERRITORY to DB creation init.ora files…

Comments are closed.