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

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

  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';
    
    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 :lol:

    PS: if you have “only” dba but not execute on DBMS_SYSTEM, it may even be useful :twisted:

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

  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
    
    $ set NLS_LANG=AMERICAN_AMERICA.UTF8
    
    $ echo host set NLS_LANG | sqlplus -s /@dev
    NLS_LANG=AMERICAN_AMERICA.UTF8
    </pre>

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

  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;

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

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

    AMERICAN_AMERICA.WE8MSWIN1252

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

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

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>