SQL> host echo $NLS_LANG
AMERICAN_SWITZERLAND

SQL> HOST ECHO %NLS_LANG%
%NLS_LANG%

SQL> @.[$NLS_LANG]
SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]"

SQL>  @.[%NLS_LANG%]
SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]"

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

1. 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’

   
2. 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
   
3. 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
4. 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>
5. Boris May 10, 2011 at 19:50

   For Windows “host set NLS_LANG” will do the job.
6. 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?
7. 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
   </pre>
8. 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
9. 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.
10. 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.
11. 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
12. 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>
13. Laurent Schneider Post authorMay 19, 2011 at 09:43

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

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