Alex Lima

How to find the NLS_LANG to set for a database?

Published by

Alex Lima

October 22, 2008

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 responses to “How to find the NLS_LANG to set for a database?”

Setting NLS_LANG for export/import « Oracle Spin

November 17, 2008

[…] How to find the NLS_LANG to set for a database? […]

LikeLike

Reply
JC

February 6, 2012

Thanks a lot man!

LikeLike

Reply
Duygu

February 8, 2012

Thank you, that saved a lot of time and was very helpful.

LikeLike

Reply
Janeen

September 16, 2014

Buenas me ha gustado con tu página no te conocía a partir de hoy.
Mi más sincera felicitación tienes un blog
fantástico Gracias por compartir con todos.

LikeLike

Reply
brustvergrösserung

September 28, 2014

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.

LikeLike

Reply
jeroentje

March 2, 2015

NLS_Database_Parameters and v$nls_parameters

LikeLike

Reply
monde

October 20, 2017

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%’”

LikeLike

Reply
Jerome

April 4, 2019

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

LikeLike

Reply
Jerome

April 4, 2019

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

LikeLike

Reply