Categories
linux sqlplus unix windows

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

By Laurent Schneider

Oracle Certified Master

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

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’

😉

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 😈

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:

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?

@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

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.

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.

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

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>

select value from v$nls_parameters
where parameter in (‘NLS_LANGUAGE’,’NLS_TERRITORY’,’NLS_CHARACTERSET’);

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.

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

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

Leave a Reply

Your email address will not be published.