How to find the NLS_LANG to set for a database?

One can use the following v$nls_parameters view to find the database LANGUAGE, TERRITORY and CHARACTER SET.

select DECODE(parameter, 'NLS_CHARACTERSET', 'CHARACTER SET',
'NLS_LANGUAGE', 'LANGUAGE',
'NLS_TERRITORY', 'TERRITORY') name,
value from v$nls_parameters
WHERE parameter IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY')
/

NAME VALUE
————- —————–
LANGUAGE AMERICAN
TERRITORY AMERICA
CHARACTER SET WE8ISO8859P1

export NLS_LANG=<language>_<territory>.<character set>

9 comments

  1. I’m no longer certain the place you’re getting your information,
    however great topic. I must spend a while finding out much more or understanding
    more. Thanks for great info I used to be looking for this info for my mission.

  2. Hi;

    i have to set the fields in this SQLwith NLS_LANG bec the turkish characters are comming wrong, but ı dont know how to do please can you help?

    qry.SQL=”select TO_CHAR(TELEFON_NUMARASI), TO_CHAR(INTERNAL), TO_CHAR(AD_SOYAD), TO_CHAR(ADRES) from TLF.TLFTELEFON_V WHERE TIP_NO=1 AND ADRES IS NOT NULL AND ADRES LIKE ‘%SİTELER%'”

  3. Important notice: the CHARACTER SET you will get from the query is the one inherited from the DB characterset, not the one from the NLS_LANG environment variable.

    You can see/check the difference by invoking a pseudo sql file like that:

    @”[%NLS_LANG%]”.sql

    In my below example, you will see that the one from the DB is AL32UTF8 while the one from NLS_LANG is WE8MSWIN1252:

    SQL> select DECODE(parameter, ‘NLS_CHARACTERSET’, ‘CHARACTER SET’,
    2 ‘NLS_LANGUAGE’, ‘LANGUAGE’,
    3 ‘NLS_TERRITORY’, ‘TERRITORY’) name,
    4 value from v$nls_parameters
    5 WHERE parameter IN ( ‘NLS_CHARACTERSET’, ‘NLS_LANGUAGE’, ‘NLS_TERRITORY’);

    NAME VALUE
    ————- —————————————————————-
    LANGUAGE FRENCH
    TERRITORY FRANCE
    CHARACTER SET AL32UTF8

    SQL> @”[%NLS_LANG%]”.sql
    SP2-0310: impossible d’ouvrir le fichier “[FRENCH_FRANCE.WE8MSWIN1252]”

  4. Following some research it seems that the following query does the job:

    SELECT DISTINCT client_charset FROM v$session_connect_info WHERE sid = sys_context(‘USERENV’,’SID’);

    The only risk maybe due to unsufficient privileges

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.