NLS formats

Specifying an NLS parameter for an SQL function means that any user session NLS parameters (or the lack of appropriate NLS parameters) will not affect evaluation of the function.

This feature may be important for SQL statements that contain numbers and dates as string literals. For example, the following query is evaluated correctly only if the language specified for dates is American:

SELECT ENAME FROM EMP
WHERE HIREDATE > '1-JAN-01'

This can be made independent of the current date language
by specifying NLS_DATE_LANGUAGE: 

SELECT ENAME FROM EMP
WHERE HIREDATE > TO_DATE('1-JAN-01','DD-MON-YY',
   'NLS_DATE_LANGUAGE = AMERICAN')


Of course TO_DATE() provides a simpler way of making this 
language-independent

SELECT ENAME FROM EMP
WHERE HIREDATE > TO_DATE('1-01-01','DD-MM-YY')


SELECT ENAME FROM EMP
WHERE HIREDATE > TO_DATE ('November 23, 1998', 'MONTH DD, YYYY')

NLS settings include Character set, Language and territory

The most common Character Sets are

WE8ISO8859P15 European English includes euro character
US7ASCII      American English

The DATE datatype always stores a four-digit year internally.

If you use the standard date format DD-MON-YY
YY will assume a year in the range 1900-1999 - it is strongly recommended you apply a specific format mask.

YEAR 2000 - Check

SELECT
to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),1 *
12),'DD-MON-YYYY') y1999,
to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),2 *
12),'DD-MON-YYYY') y2000,
to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),7 *
12),'DD-MON-YYYY') y2005,
to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),52 *
12),'DD-MON-YYYY') y2050
FROM
DUAL;

-- Expected output from script above

-- Y1999       Y2000       Y2005       Y2050
-- ----------- ----------- ----------- -----------
-- 01-JAN-1999 01-JAN-2000 01-JAN-2005 01-JAN-2050

Oracle Languages

e.g. NLS_LANGUAGE = ENGLISH

  us  AMERICAN
  ar  ARABIC

  bn  BENGALI
  ptb BRAZILIAN PORTUGUESE  
  bg  BULGARIAN

  frc CANADIAN FRENCH
  ca  CATALAN
  zhs SIMPLIFIED CHINESE 
  hr  CROATIAN
  cs  CZECH

  dk  DANISH
  nl  DUTCH

  eg  EGYPTIAN
  gb  ENGLISH
  et  ESTONIAN

  sf  FINNISH
  f   FRENCH

  din GERMAN DIN  
  d   GERMAN
  el  GREEK

  iw  HEBREW  
  hu  HUNGARIAN

  is  ICELANDIC
  in  INDONESIAN
  i   ITALIAN

  ja  JAPANESE

  ko  KOREAN

  esa LATIN AMERICAN SPANISH  
  lv  LATVIAN
  lt  LITHUANIAN

  ms  MALAY  
  esm MEXICAN SPANISH

  n   NORWEGIAN

  pl  POLISH
  pt  PORTUGUESE

  ro  ROMANIAN  
  ru  RUSSIAN

  sk  SLOVAK
  sl  SLOVENIAN  
  e   SPANISH
  s   SWEDISH

  th  THAI  
  zht TRADITIONAL CHINESE
  tr  TURKISH

  uk  UKRAINIAN

  vn  VIETNAMESE
 
The NLS_LANGUAGE above implicitly defines several other parameters:
NLS_DATE_LANGUAGE, NLS_SORT


Oracle Territories

e.g. NLS_TERRITORY = "UNITED KINGDOM"

  ALGERIA
  AMERICA
  AUSTRALIA
  AUSTRIA

  BAHRAIN
  BANGLADESH
  BELGIUM
  BRAZIL
  BULGARIA

  CANADA
  CATALONIA
  CHINA
  CIS
  CROATIA
  CYPRUS
  CZECH
  CZECHOSLOVAKIA

  DENMARK
  DJIBOUTI

  EGYPT
  ESTONIA

  FINLAND
  FRANCE

  GERMANY
  GREECE

  HONG KONG
  HUNGARY

  ICELAND
  INDONESIA
  IRAQ
  IRELAND
  ISRAEL
  ITALY

  JAPAN
  JORDAN

  KAZAKHSTAN
  KOREA
  KUWAIT

  LATVIA
  LEBANON
  LIBYA
  LITHUANIA
  LUXEMBOURG

  MALAYSIA
  MAURITANIA
  MEXICO
  MOROCCO

  NEW ZEALAND
  NORWAY

  OMAN

  POLAND
  PORTUGAL

  QATAR

  ROMANIA

  SAUDI ARABIA
  SINGAPORE
  SLOVAKIA
  SLOVENIA
  SOMALIA
  SOUTH AFRICA
  SPAIN
  SUDAN
  SWEDEN
  SWITZERLAND
  SYRIA

  TAIWAN
  THAILAND
  THE NETHERLANDS
  TUNISIA
  TURKEY

  UKRAINE
  UNITED ARAB EMIRATES
  UNITED KINGDOM
  UZBEKISTAN

  VIETNAM

  YEMEN

The NLS_TERRITORY implicitly defines several other parameters:
NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT
NLS_MONETARY_CHARACTERS, NLS_CREDIT, NLS_DEBIT

If necessary these can be explicitly defined
e.g. NLS_NUMERIC_CHARACTERS = ",."


NLS_COMP
This provides a simple alternative to specifying NLS_SORT in
an SQL WHERE clause

NLS formats will affect SQL statements in views, CHECK constraints, and triggers.

Related Commands

ALTER SESSION