This was first published on https://blog.dbi-services.com/nls-defaults-for-language-and-territory (2017-01-04)
Republishing here for new followers. The content is related to the the versions available at the publication date
When you set NLS_LANGUAGE then the values of NLS_DATE_LANGUAGE and NLS_SORT are set to their defaults for the LANGUAGE you set. When you set NLS_TERRITORY then values of NLS_DATE_FORMAT, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY and NLS_ISO_CURRENCY are set to the defaults for the TERRITORY you set. For reference, I’ve listed the defaults in this post. Here for each value found from V$NLS_VALID_VALUES I set the session parameter and display the values that are derived, as well as an example of a date
The TERRITORY sets the decimal and thousand separators, the currency and the date format. I display the short date example of last day of 2016.
SQL> with 2 function nls_territory_defaults(t varchar2) return varchar2 as 3 s varchar2(2017):=''; 4 begin 5 execute immediate 'alter session set nls_territory='''||t||''''; 6 for i in (select * from nls_session_parameters where parameter in ('NLS_DATE_FORMAT','NLS_NUMERIC_CHARACTERS','NLS_CURRENCY','NLS_ISO_CURRENCY') order by parameter desc) 7 loop 8 s:=s||lpad(i.parameter,20)||'='||rpad(i.value,20); 9 end loop; 10 return s||' example: '||to_char(trunc(sysdate,'yy')-1,'ds'); 11 end; 12 select rpad(value,20)||nls_territory_defaults(value) "TERRITORY default NLS settings" from v$nls_valid_values where parameter='TERRITORY' order by 1 13 / TERRITORY default NLS settings ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- AFGHANISTAN NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=AFGHANISTAN NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=؋ example: 31/12/16 ALBANIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=ALBANIA NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=Lek example: 31.12.2016 ALGERIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=ALGERIA NLS_DATE_FORMAT=DD-MM-RR NLS_CURRENCY=د.ج. example: 31/12/2016 AMERICA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=AMERICA NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=$ example: 12/31/2016 ARGENTINA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=ARGENTINA NLS_DATE_FORMAT=DD/MM/RRRR NLS_CURRENCY=$ example: 31/12/2016 ARMENIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=ARMENIA NLS_DATE_FORMAT=DD.fmMM.RRRR NLS_CURRENCY=դր. example: 31.12.2016 AUSTRALIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=AUSTRALIA NLS_DATE_FORMAT=DD/MON/RR NLS_CURRENCY=$ example: 31/12/2016 AUSTRIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=AUSTRIA NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=€ example: 31.12.2016 AZERBAIJAN NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=AZERBAIJAN NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=₼ example: 31.12.2016 BAHAMAS NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=BAHAMAS NLS_DATE_FORMAT=DD/MM/RRRR NLS_CURRENCY=B$ example: 31/12/2016 BAHRAIN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=BAHRAIN NLS_DATE_FORMAT=DD-MM-RR NLS_CURRENCY=د.ب. example: 31/12/2016 BANGLADESH NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=BANGLADESH NLS_DATE_FORMAT=DD-MM-RRRR NLS_CURRENCY=৳ example: 31-12-2016 BELARUS NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=BELARUS NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=р. example: 31.12.2016 BELGIUM NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=BELGIUM NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=€ example: 31/12/2016 BELIZE NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=BELIZE NLS_DATE_FORMAT=fmDD/MM/RRRR NLS_CURRENCY=BZ$ example: 12/31/2016 BERMUDA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=BERMUDA NLS_DATE_FORMAT=DD/MM/RRRR NLS_CURRENCY=BD$ example: 31/12/2016 BOLIVIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=BOLIVIA NLS_DATE_FORMAT=fmDD-MM-RR NLS_CURRENCY=B$ example: 12-31-2016 BOSNIA AND HERZEGOVINLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=BOSNIA AND HERZEGOVI NLS_DATE_FORMAT=fmMM.DD.RRRR NLS_CURRENCY=КМ example: 12.31.2016 BRAZIL NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=BRAZIL NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=R$ example: 31/12/2016 BULGARIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=BULGARIA NLS_DATE_FORMAT=RRRR-MM-DD NLS_CURRENCY=лв example: 31.12.2016 CAMBODIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=CAMBODIA NLS_DATE_FORMAT=RRRR-fmMM-DD NLS_CURRENCY=៛ example: 2016-12-31 CAMEROON NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=CAMEROON NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=CFA example: 31/12/16 CANADA NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=CANADA NLS_DATE_FORMAT=RR-MM-DD NLS_CURRENCY=$ example: 2016-12-31 CATALONIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=CATALONIA NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=€ example: 31-12-16 CHILE NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=CHILE NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=$ example: 31-12-2016 CHINA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=CHINA NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=¥ example: 2016-12-31 CIS NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=CIS NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=р. example: 31.12.16 COLOMBIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=COLOMBIA NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=$ example: 31/12/2016 CONGO BRAZZAVILLE NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=CONGO BRAZZAVILLE NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=CFA example: 31/12/16 CONGO KINSHASA NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=CONGO KINSHASA NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=FrCD example: 31/12/16 COSTA RICA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=COSTA RICA NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=C example: 31/12/2016 CROATIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=CROATIA NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=kn example: 31.12.16 CYPRUS NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=CYPRUS NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=£ example: 31/12/2016 CZECH REPUBLIC NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=CZECH REPUBLIC NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=Kč example: 31.12.2016 CZECHOSLOVAKIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=CZECHOSLOVAKIA NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=Kčs example: 31.12.16 DENMARK NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=DENMARK NLS_DATE_FORMAT=RR-MM-DD NLS_CURRENCY=kr example: 31-12-2016 DJIBOUTI NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=DJIBOUTI NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=ف.ج. example: 31/12/2016 ECUADOR NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=ECUADOR NLS_DATE_FORMAT=DD/MM/RRRR NLS_CURRENCY=$ example: 31/12/2016 EGYPT NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=EGYPT NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=ج.م. example: 31/12/2016 EL SALVADOR NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=EL SALVADOR NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=C example: 31/12/2016 ESTONIA NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=ESTONIA NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=kr example: 31.12.2016 ETHIOPIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=ETHIOPIA NLS_DATE_FORMAT=fmDD/MM/RRRR NLS_CURRENCY=ብር example: 12/31/2016 FINLAND NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=FINLAND NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=€ example: 31.12.2016 FRANCE NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=FRANCE NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=€ example: 31/12/2016 FYR MACEDONIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=FYR MACEDONIA NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=ден. example: 31.12.2016 GABON NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=GABON NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=CFA example: 31/12/16 GERMANY NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=GERMANY NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=€ example: 31.12.2016 GREECE NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=GREECE NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=€ example: 31/12/2016 GUATEMALA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=GUATEMALA NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=Q example: 31/12/2016 HONDURAS NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=HONDURAS NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=L example: 31.12.2016 HONG KONG NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=HONG KONG NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=$ example: 31/12/2016 HUNGARY NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=HUNGARY NLS_DATE_FORMAT=RR-MON-DD NLS_CURRENCY=Ft example: 2016.12.31. ICELAND NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=ICELAND NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=kr. example: 31.12.2016 INDIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=INDIA NLS_DATE_FORMAT=DD-MM-RR NLS_CURRENCY=Rs example: 31/12/2016 INDONESIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=INDONESIA NLS_DATE_FORMAT=DD-MM-RRRR NLS_CURRENCY=Rp example: 31-12-2016 IRAN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=IRAN NLS_DATE_FORMAT=RRRR/fmMM/fmDD NLS_CURRENCY=ريال example: 2016/12/31 IRAQ NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=IRAQ NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=د.ع. example: 31/12/2016 IRELAND NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=IRELAND NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=€ example: 31/12/2016 ISRAEL NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=ISRAEL NLS_DATE_FORMAT=DD-MON-RRRR NLS_CURRENCY=ש"ח example: 31/12/2016 ITALY NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=ITALY NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=€ example: 31/12/2016 IVORY COAST NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=IVORY COAST NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=CFA example: 31/12/16 JAPAN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=JAPAN NLS_DATE_FORMAT=RR-MM-DD NLS_CURRENCY=¥ example: 2016/12/31 JORDAN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=JORDAN NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=د.ا. example: 31/12/2016 KAZAKHSTAN NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=KAZAKHSTAN NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=KZT example: 31.12.2016 KENYA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=KENYA NLS_DATE_FORMAT=fmMM/DD/RRRR NLS_CURRENCY=S example: 12/31/2016 KOREA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=KOREA NLS_DATE_FORMAT=RR/MM/DD NLS_CURRENCY=₩ example: 2016/12/31 KUWAIT NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=KUWAIT NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=د.ك. example: 31/12/2016 LAOS NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=LAOS NLS_DATE_FORMAT=DD/fmMM/RRRR NLS_CURRENCY=₭ example: 31/12/2016 LATVIA NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=LATVIA NLS_DATE_FORMAT=RRRR.MM.DD NLS_CURRENCY=Ls example: 2016.12.31 LEBANON NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=LEBANON NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=ل.ل. example: 31/12/2016 LIBYA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=LIBYA NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=د.ل. example: 31/12/2016 LITHUANIA NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=LITHUANIA NLS_DATE_FORMAT=RRRR.MM.DD NLS_CURRENCY=€ example: 2016-12-31 LUXEMBOURG NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=LUXEMBOURG NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=€ example: 31.12.2016 MACEDONIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=MACEDONIA NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=den. example: 31.12.2016 MALAYSIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=MALAYSIA NLS_DATE_FORMAT=DD/MM/RRRR NLS_CURRENCY=RM example: 31/12/2016 MALDIVES NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=MALDIVES NLS_DATE_FORMAT=fmDD/fmMM/RR NLS_CURRENCY=ރ. example: 12/31/16 MALTA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=MALTA NLS_DATE_FORMAT=fmDD/MM/RRRR NLS_CURRENCY=€ example: 12/31/2016 MAURITANIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=MAURITANIA NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=ا.م. example: 31/12/2016 MEXICO NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=MEXICO NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=$ example: 31/12/2016 MONTENEGRO NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=MONTENEGRO NLS_DATE_FORMAT=DD.MM.RRRR. NLS_CURRENCY=€ example: 31.12.2016. MOROCCO NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=MOROCCO NLS_DATE_FORMAT=DD-MM-RR NLS_CURRENCY=د.م. example: 31/12/2016 NEPAL NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=NEPAL NLS_DATE_FORMAT=fmDD/MM/RRRR NLS_CURRENCY=र example: 12/31/2016 NEW ZEALAND NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=NEW ZEALAND NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=$ example: 31/12/2016 NICARAGUA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=NICARAGUA NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=C$ example: 31/12/2016 NIGERIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=NIGERIA NLS_DATE_FORMAT=DD/fmMM/RRRR NLS_CURRENCY=₦ example: 31/12/2016 NORWAY NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=NORWAY NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=kr example: 31.12.2016 OMAN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=OMAN NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=ر.ع. example: 31/12/2016 PAKISTAN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=PAKISTAN NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=PKR example: 31/12/2016 PANAMA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=PANAMA NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=B/ example: 12/31/2016 PARAGUAY NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=PARAGUAY NLS_DATE_FORMAT=fmDD/MM/RR NLS_CURRENCY=G example: 12/31/16 PERU NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=PERU NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=S/ example: 31/12/2016 PHILIPPINES NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=PHILIPPINES NLS_DATE_FORMAT=MM/DD/RRRR NLS_CURRENCY=Php example: 12/31/2016 POLAND NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=POLAND NLS_DATE_FORMAT=RR/MM/DD NLS_CURRENCY=zł example: 2016-12-31 PORTUGAL NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=PORTUGAL NLS_DATE_FORMAT=RR.MM.DD NLS_CURRENCY=€ example: 31-12-2016 PUERTO RICO NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=PUERTO RICO NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=$ example: 31/12/2016 QATAR NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=QATAR NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=ر.ق. example: 31/12/2016 ROMANIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=ROMANIA NLS_DATE_FORMAT=DD-MM-RRRR NLS_CURRENCY=LEI example: 31.12.2016 RUSSIA NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=RUSSIA NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=₽ example: 31.12.2016 SAUDI ARABIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=SAUDI ARABIA NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=ر.س. example: 31/12/2016 SENEGAL NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=SENEGAL NLS_DATE_FORMAT=DD/MM/RRRR NLS_CURRENCY=CFA example: 31/12/2016 SERBIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=SERBIA NLS_DATE_FORMAT=DD.MM.RRRR. NLS_CURRENCY=RSD example: 31.12.2016. SERBIA AND MONTENEGRNLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=SERBIA AND MONTENEGR NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=din. example: 31.12.2016 SINGAPORE NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=SINGAPORE NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=S$ example: 31/12/2016 SLOVAKIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=SLOVAKIA NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=Sk example: 31.12.16 SLOVENIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=SLOVENIA NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=SIT example: 31.12.16 SOMALIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=SOMALIA NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=ش.ص. example: 31/12/2016 SOUTH AFRICA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=SOUTH AFRICA NLS_DATE_FORMAT=DD/MON/RR NLS_CURRENCY=R example: 2016/12/31 SPAIN NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=SPAIN NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=€ example: 31/12/2016 SRI LANKA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=SRI LANKA NLS_DATE_FORMAT=RRRR/fmMM/fmDD NLS_CURRENCY=ريال example: 2016/12/31 SUDAN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=SUDAN NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=ج.س. example: 31/12/2016 SWEDEN NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=SWEDEN NLS_DATE_FORMAT=RRRR-MM-DD NLS_CURRENCY=Kr example: 2016-12-31 SWITZERLAND NLS_NUMERIC_CHARACTE=.' NLS_ISO_CURRENCY=SWITZERLAND NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=SFr. example: 31.12.2016 SYRIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=SYRIA NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=ل.س. example: 31/12/2016 TAIWAN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=TAIWAN NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=NT$ example: 2016/12/31 TANZANIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=TANZANIA NLS_DATE_FORMAT=fmMM/DD/RRRR NLS_CURRENCY=TSh example: 12/31/2016 THAILAND NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=THAILAND NLS_DATE_FORMAT=DD MON RRRR NLS_CURRENCY=฿ example: 31/12/2016 THE NETHERLANDS NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=THE NETHERLANDS NLS_DATE_FORMAT=DD-MM-RR NLS_CURRENCY=€ example: 31-12-2016 TUNISIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=TUNISIA NLS_DATE_FORMAT=DD-MM-RR NLS_CURRENCY=د.ت. example: 31/12/2016 TURKEY NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=TURKEY NLS_DATE_FORMAT=DD/MM/RRRR NLS_CURRENCY=₺ example: 31.12.2016 UGANDA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=UGANDA NLS_DATE_FORMAT=DD/MM/RRRR NLS_CURRENCY=CFA example: 31/12/2016 UKRAINE NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=UKRAINE NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=грн. example: 31.12.2016 UNITED ARAB EMIRATESNLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=UNITED ARAB EMIRATES NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=د.إ. example: 31/12/2016 UNITED KINGDOM NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=UNITED KINGDOM NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=£ example: 31/12/2016 URUGUAY NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=URUGUAY NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=NU$ example: 31/12/2016 UZBEKISTAN NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=UZBEKISTAN NLS_DATE_FORMAT=DD/MON/RR NLS_CURRENCY=UZS example: 31/DEC/16 VENEZUELA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=VENEZUELA NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=Bs example: 31/12/2016 VIETNAM NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=VIETNAM NLS_DATE_FORMAT=DD-MM-RRRR NLS_CURRENCY=₫ example: 31-12-2016 YEMEN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=YEMEN NLS_DATE_FORMAT=DD/MM/RR NLS_CURRENCY=ر.ي. example: 31/12/2016 YUGOSLAVIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=YUGOSLAVIA NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=din. example: 31.12.2016 ZAMBIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=ZAMBIA NLS_DATE_FORMAT=DD/MM/RRRR NLS_CURRENCY=ZK example: 31/12/2016
The LANGUAGE sets the linguistic sorting and the language used for words in dates. I display the long date example of last day of 2016.
SQL> with 2 function nls_language_defaults(t varchar2) return varchar2 as 3 s varchar2(2017):=''; 4 begin 5 execute immediate 'alter session set nls_language='''||t||''''; 6 for i in (select * from nls_session_parameters where parameter in ('NLS_DATE_LANGUAGE','NLS_SORT') order by parameter desc) 7 loop 8 s:=s||lpad(i.parameter,20)||'='||rpad(i.value,20); 9 end loop; 10 return s||' example: '||to_char(trunc(sysdate,'yy')-1,'dl'); 11 end; 12 select rpad(value,20)||nls_language_defaults(value) "LANGUAGE default NLS settings" from v$nls_valid_values where parameter='LANGUAGE' order by 1 13 / LANGUAGE default NLS settings ----------------------------------------------------------------------------------------------------------------------------------------------- ALBANIAN NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=ALBANIAN example: E Shtunë, Dhjetor 31, 2016 AMERICAN NLS_SORT=BINARY NLS_DATE_LANGUAGE=AMERICAN example: Saturday, December 31, 2016 AMHARIC NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=AMHARIC example: ቅዳሜ, ዲሴምበር 31, 2016 ARABIC NLS_SORT=ARABIC NLS_DATE_LANGUAGE=ARABIC example: السبت, ديسمبر 31, 2016 ARMENIAN NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=ARMENIAN example: Շաբա, Դեկտեմբեր 31, 2016 ASSAMESE NLS_SORT=BINARY NLS_DATE_LANGUAGE=ASSAMESE example: শনিবাৰ, ডিচেম্বৰ 31, 2016 AZERBAIJANI NLS_SORT=AZERBAIJANI NLS_DATE_LANGUAGE=AZERBAIJANI example: Şənbə, Dekabr 31, 2016 BANGLA NLS_SORT=BINARY NLS_DATE_LANGUAGE=BANGLA example: শনিবার, ডিসেম্বর 31, 2016 BELARUSIAN NLS_SORT=RUSSIAN NLS_DATE_LANGUAGE=BELARUSIAN example: Субота, Сьнежань 31, 2016 BENGALI NLS_SORT=BENGALI NLS_DATE_LANGUAGE=BENGALI example: শনিবার, ডিসেমর 31, 2016 BRAZILIAN PORTUGUESE NLS_SORT=WEST_EUROPEAN NLS_DATE_LANGUAGE=BRAZILIAN PORTUGUESE example: Sábado, Dezembro 31, 2016 BULGARIAN NLS_SORT=BULGARIAN NLS_DATE_LANGUAGE=BULGARIAN example: Събота, Декември 31, 2016 CANADIAN FRENCH NLS_SORT=CANADIAN_M NLS_DATE_LANGUAGE=CANADIAN FRENCH example: Samedi, Décembre 31, 2016 CATALAN NLS_SORT=CATALAN NLS_DATE_LANGUAGE=CATALAN example: Dissabte, Desembre 31, 2016 CROATIAN NLS_SORT=CROATIAN NLS_DATE_LANGUAGE=CROATIAN example: Subota, Prosinac 31, 2016 CYRILLIC KAZAKH NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=CYRILLIC KAZAKH example: Сенбі, Желтоқсан 31, 2016 CYRILLIC SERBIAN NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=CYRILLIC SERBIAN example: Субота, Децембар 31, 2016 CYRILLIC UZBEK NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=CYRILLIC UZBEK example: Шанба, Декабр 31, 2016 CZECH NLS_SORT=CZECH NLS_DATE_LANGUAGE=CZECH example: Sobota, Prosinec 31, 2016 DANISH NLS_SORT=DANISH NLS_DATE_LANGUAGE=DANISH example: Lørdag, December 31, 2016 DARI NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=DARI example: شنبه, دسمبر 31, 2016 DIVEHI NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=DIVEHI example: ހޮނިހިރު, ޑިސެމްބަރ 31, 2016 DUTCH NLS_SORT=DUTCH NLS_DATE_LANGUAGE=DUTCH example: Zaterdag, December 31, 2016 EGYPTIAN NLS_SORT=ARABIC NLS_DATE_LANGUAGE=EGYPTIAN example: السبت, ديسمبر 31, 2016 ENGLISH NLS_SORT=BINARY NLS_DATE_LANGUAGE=ENGLISH example: Saturday, December 31, 2016 ESTONIAN NLS_SORT=ESTONIAN NLS_DATE_LANGUAGE=ESTONIAN example: Laupäev, Detsember 31, 2016 FINNISH NLS_SORT=FINNISH NLS_DATE_LANGUAGE=FINNISH example: Lauantai, Joulukuu 31, 2016 FRENCH NLS_SORT=FRENCH NLS_DATE_LANGUAGE=FRENCH example: Samedi, Décembre 31, 2016 GERMAN NLS_SORT=GERMAN NLS_DATE_LANGUAGE=GERMAN example: Samstag, Dezember 31, 2016 GERMAN DIN NLS_SORT=GERMAN NLS_DATE_LANGUAGE=GERMAN DIN example: Samstag, Dezember 31, 2016 GREEK NLS_SORT=GREEK NLS_DATE_LANGUAGE=GREEK example: Σάββατο, Δεκέμβριος 31, 2016 GUJARATI NLS_SORT=BINARY NLS_DATE_LANGUAGE=GUJARATI example: શનિવાર, ડિસેમ્બર 31, 2016 HEBREW NLS_SORT=HEBREW NLS_DATE_LANGUAGE=HEBREW example: שבת, דצמבר 31, 2016 HINDI NLS_SORT=BINARY NLS_DATE_LANGUAGE=HINDI example: शनिवार, दिसम्बर 31, 2016 HUNGARIAN NLS_SORT=HUNGARIAN NLS_DATE_LANGUAGE=HUNGARIAN example: Szombat, December 31, 2016 ICELANDIC NLS_SORT=ICELANDIC NLS_DATE_LANGUAGE=ICELANDIC example: Laugardagur, Desember 31, 2016 INDONESIAN NLS_SORT=INDONESIAN NLS_DATE_LANGUAGE=INDONESIAN example: Sabtu, Desember 31, 2016 IRISH NLS_SORT=BINARY NLS_DATE_LANGUAGE=IRISH example: Dé Sathairn, Nollaig 31, 2016 ITALIAN NLS_SORT=WEST_EUROPEAN NLS_DATE_LANGUAGE=ITALIAN example: Sabato, Dicembre 31, 2016 JAPANESE NLS_SORT=BINARY NLS_DATE_LANGUAGE=JAPANESE example: 土曜日, 12月 31, 2016 KANNADA NLS_SORT=BINARY NLS_DATE_LANGUAGE=KANNADA example: ಶನಿವಾರ, ಡಿಸೆಂಬರ್ 31, 2016 KHMER NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=KHMER example: ថ្ងៃសៅរ៍, ធ្នូ 31, 2016 KOREAN NLS_SORT=BINARY NLS_DATE_LANGUAGE=KOREAN example: 토요일, 12월 31, 2016 LAO NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=LAO example: ວັນເສົາ, ທັນວາ 31, 2016 LATIN AMERICAN SPANI NLS_SORT=SPANISH NLS_DATE_LANGUAGE=LATIN AMERICAN SPANI example: Sábado, Diciembre 31, 2016 LATIN BOSNIAN NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=LATIN BOSNIAN example: Subota, Decembar 31, 2016 LATIN SERBIAN NLS_SORT=BINARY NLS_DATE_LANGUAGE=LATIN SERBIAN example: Subota, Decembar 31, 2016 LATIN UZBEK NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=LATIN UZBEK example: Shanba, Dekabr 31, 2016 LATVIAN NLS_SORT=LATVIAN NLS_DATE_LANGUAGE=LATVIAN example: Sestdiena, Decembris 31, 2016 LITHUANIAN NLS_SORT=LITHUANIAN NLS_DATE_LANGUAGE=LITHUANIAN example: Šeštadienis, Gruodžio 31, 2016 MACEDONIAN NLS_SORT=BINARY NLS_DATE_LANGUAGE=MACEDONIAN example: Сабота, Декември 31, 2016 MALAY NLS_SORT=MALAY NLS_DATE_LANGUAGE=MALAY example: Sabtu, Disember 31, 2016 MALAYALAM NLS_SORT=BINARY NLS_DATE_LANGUAGE=MALAYALAM example: ശനിആഴ്ച, ഡിസംബര് 31, 2016 MALTESE NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=MALTESE example: Is-Sibt, Diċembru 31, 2016 MARATHI NLS_SORT=BINARY NLS_DATE_LANGUAGE=MARATHI example: शनिवार, डिसेंबर 31, 2016 MEXICAN SPANISH NLS_SORT=WEST_EUROPEAN NLS_DATE_LANGUAGE=MEXICAN SPANISH example: Sábado, Diciembre 31, 2016 NEPALI NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=NEPALI example: शनिबार, डिसेम्बर 31, 2016 NORWEGIAN NLS_SORT=NORWEGIAN NLS_DATE_LANGUAGE=NORWEGIAN example: Lørdag, Desember 31, 2016 ORIYA NLS_SORT=BINARY NLS_DATE_LANGUAGE=ORIYA example: ଶନିବାର, ଡିସେମ୍ବର 31, 2016 PERSIAN NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=PERSIAN example: شنبه, دسامبر 31, 2016 POLISH NLS_SORT=POLISH NLS_DATE_LANGUAGE=POLISH example: Sobota, Grudzień 31, 2016 PORTUGUESE NLS_SORT=WEST_EUROPEAN NLS_DATE_LANGUAGE=PORTUGUESE example: Sábado, Dezembro 31, 2016 PUNJABI NLS_SORT=BINARY NLS_DATE_LANGUAGE=PUNJABI example: ਸ਼ਨਿਚੱਰਵਾਰ, ਦਸੰਬਰ 31, 2016 ROMANIAN NLS_SORT=ROMANIAN NLS_DATE_LANGUAGE=ROMANIAN example: Sâmbătă, Decembrie 31, 2016 RUSSIAN NLS_SORT=RUSSIAN NLS_DATE_LANGUAGE=RUSSIAN example: Суббота, Декабрь 31, 2016 SIMPLIFIED CHINESE NLS_SORT=BINARY NLS_DATE_LANGUAGE=SIMPLIFIED CHINESE example: 星期六, 12月 31, 2016 SINHALA NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=SINHALA example: සෙනසුරාදා, දෙසැම්බර් 31, 2016 SLOVAK NLS_SORT=SLOVAK NLS_DATE_LANGUAGE=SLOVAK example: Sobota, December 31, 2016 SLOVENIAN NLS_SORT=SLOVENIAN NLS_DATE_LANGUAGE=SLOVENIAN example: Sobota, December 31, 2016 SPANISH NLS_SORT=SPANISH NLS_DATE_LANGUAGE=SPANISH example: Sábado, Diciembre 31, 2016 SWAHILI NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=SWAHILI example: Jumamosi, Desemba 31, 2016 SWEDISH NLS_SORT=SWEDISH NLS_DATE_LANGUAGE=SWEDISH example: Lördag, December 31, 2016 TAMIL NLS_SORT=BINARY NLS_DATE_LANGUAGE=TAMIL example: சனிக்கிழமை, டிசம்பர் 31, 2016 TELUGU NLS_SORT=BINARY NLS_DATE_LANGUAGE=TELUGU example: శనివారం, డిసెంబర్ 31, 2016 THAI NLS_SORT=THAI_DICTIONARY NLS_DATE_LANGUAGE=THAI example: เสาร์, ธันวาคม 31, 2016 TRADITIONAL CHINESE NLS_SORT=BINARY NLS_DATE_LANGUAGE=TRADITIONAL CHINESE example: 星期六, 12月 31, 2016 TURKISH NLS_SORT=TURKISH NLS_DATE_LANGUAGE=TURKISH example: Cumartesi, Aralık 31, 2016 UKRAINIAN NLS_SORT=UKRAINIAN NLS_DATE_LANGUAGE=UKRAINIAN example: Субота, Грудень 31, 2016 VIETNAMESE NLS_SORT=VIETNAMESE NLS_DATE_LANGUAGE=VIETNAMESE example: Thứ Bảy, Tháng MườI Hai 31, 2016
Note that you can also set the NLS settings with environment variables, but you can do that only if NLS_LANG is set. This means that you must set NLS_LANG first, which sets the LANGUAGE and TERRITORY, and then set for example NLS_DATE_FORMAT.
Here is an example. I set NLS_DATE_FORMAT from the environment:
[oracle@VM102 data]$ export NLS_DATE_FORMAT=DLbut the date format is the set in my session:
[oracle@VM102 data]$ sqlplus -s / as sysdba <<< "select sysdate from dual;" SYSDATE --------- 04-JAN-17Now I set NLS_LANG (and you don’t need to specify a LANGUAGE, TERRITORY or CHARACTERSET, just put the separators ‘_’ and ‘.’)
[oracle@VM102 data]$ export NLS_LANG="_."And now my NLS_DATE_FORMAT is taken into account
[oracle@VM102 data]$ sqlplus -s / as sysdba <<< "select sysdate from dual;" SYSDATE ----------------------------- Wednesday, January 04, 2017
In general you should set NLS_LANG on any client, to the LANGUAGE and TERRITORY you want to display to client (or store in file, or manage by the application server).
I wish you an Happy New Year 2017.