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.


SQL> host echo $NLS_LANG



The correct setting is revealed by @.[%NLS_LANG%]

SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]"


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


  1. 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. 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';


    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 😈

  3. Yet another “just for fun”

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

    SQL*Plus: Release 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 – 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


  4. @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?

  5. @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


    $ echo host set NLS_LANG | sqlplus -s /@dev

  6. 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.

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

  8. alternative solution for linux clients:

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

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


    SQL> !(cat /proc/17916/environ; echo) | tr “00” “\n” | grep NLS_LANG

  9. 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;




    SQL> @.[%NLS_LANG%]
    SP2-0310: unable to open file “.[AMERICAN_AMERICA.WE8MSWIN1252]”

  10. select value from v$nls_parameters

  11. 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.

  12. 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)


  13. 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

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

Leave a comment

Your email address will not be published.