NLS_LANG and related settinngs

View NLS settings

Some of NLS parameters cannot be changed at the session level.

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;
PARAMETER                      VALUE
------------------------------ -------------------------------
NLS_CALENDAR                   GREGORIAN
NLS_CHARACTERSET               WE8ISO8859P1
NLS_COMP                       BINARY
NLS_CURRENCY                   $
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_DUAL_CURRENCY              $
NLS_ISO_CURRENCY               AMERICA
NLS_LANGUAGE                   AMERICAN
NLS_LENGTH_SEMANTICS           CHAR
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NUMERIC_CHARACTERS         .,
NLS_RDBMS_VERSION              11.1.0.7.0
NLS_SORT                       BINARY
NLS_TERRITORY                  AMERICA
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR

20 rows selected

SQL> SELECT * FROM NLS_SESSION_PARAMETERS;
PARAMETER                      VALUE
------------------------------ -------------------------------
NLS_CALENDAR                   GREGORIAN
NLS_COMP                       BINARY
NLS_CURRENCY                   $
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_DUAL_CURRENCY              $
NLS_ISO_CURRENCY               AMERICA
NLS_LANGUAGE                   AMERICAN
NLS_LENGTH_SEMANTICS           CHAR
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NUMERIC_CHARACTERS         .,
NLS_SORT                       BINARY
NLS_TERRITORY                  AMERICA
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR

17 rows selected

SQL> 



To change sessions parameter:

To change specific value:

ALTER SESSION set NLS_DATE_FORMAT = 'DD/MM/YYYY';

NLS_LANG

Get current Oracle NLS_LANG value

COL PARAMETER FOR A30

COL VALUE FOR A30

set pagesize 1000

SELECT * FROM V$NLS_PARAMETERS

PARAMETER                      VALUE

------------------------------ ------------------------------

NLS_LANGUAGE                   HEBREW

NLS_TERRITORY                  ISRAEL

NLS_CURRENCY                   ┐"┐

NLS_ISO_CURRENCY               ISRAEL

NLS_NUMERIC_CHARACTERS         .,

NLS_CALENDAR                   GREGORIAN

NLS_DATE_FORMAT                DD-MON-RRRR

NLS_DATE_LANGUAGE              HEBREW

NLS_CHARACTERSET               WE8ISO8859P1

NLS_SORT                       HEBREW

NLS_TIME_FORMAT                HH24:MI:SSXFF

NLS_TIMESTAMP_FORMAT           DD-MON-RRRR HH24:MI:SSXFF

NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR

NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RRRR HH24:MI:SSXFF TZR

NLS_DUAL_CURRENCY              ┐

NLS_NCHAR_CHARACTERSET         AL16UTF16

NLS_COMP                       BINARY

NLS_LENGTH_SEMANTICS           CHAR

NLS_NCHAR_CONV_EXCP            FALSE

19 rows selected.

Control Panel > System > Advanced > Environment Variables > System Variables > New > NLS_LANG= [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET]

for example:

NLS_LANG=HEBREW_ISRAEL.WE8ISO8859P1

or

NLS_LANG=AMERICAN_AMERICA.UTF8

Note the change in NLS_CURRENCY from garbage to $, after setting NLS_LANG to AMERICAN_AMERICA.UTF8

PARAMETER                      VALUE

------------------------------ ------------------------------

NLS_LANGUAGE                   AMERICAN

NLS_TERRITORY                  AMERICA

NLS_CURRENCY                   $

NLS_ISO_CURRENCY               AMERICA

NLS_NUMERIC_CHARACTERS         .,

NLS_CALENDAR                   GREGORIAN

NLS_DATE_FORMAT                DD-MON-RR

NLS_DATE_LANGUAGE              AMERICAN

NLS_CHARACTERSET               WE8ISO8859P1

NLS_SORT                       BINARY

NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE

------------------------------ ------------------------------

NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY              $

NLS_NCHAR_CHARACTERSET         AL16UTF16

NLS_COMP                       BINARY

NLS_LENGTH_SEMANTICS           CHAR

NLS_NCHAR_CONV_EXCP            FALSE

19 rows selected.

get the LANG settings from the host.

page code

run command "chcp" to see whet is your page code is

If code page is not 437, Oracle Client is not set to default American. 

It allows Oracle to display foreign language text stored in the database, but also might cause displayed data to be garbaged.

To change session parameters:
ALTER SESSION SET NLS_LANGUAGE='AMERICAN';
ALTER SESSION SET NLS_TERRITORY='AMERICA';
ALTER SESSION SET NLS_CURRENCY='$';
ALTER SESSION SET NLS_ISO_CURRENCY='AMERICA';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,';
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR';
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'; 
ALTER SESSION SET NLS_SORT='BINARY';