Alex Lima

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

  7. 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%'”

  8. 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]”

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