.
\nWhen you set NLS_LANGUAGE then the values of NLS_DATE_LANGUAGE and NLS_SORT are set to their defaults for the LANGUAGE you set.
\nWhen 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.<\/p>\n
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<\/p>\n
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.<\/p>\n
\nSQL> with \n 2 function nls_territory_defaults(t varchar2) return varchar2 as \n 3 s varchar2(2017):='';\n 4 begin \n 5 execute immediate 'alter session set nls_territory='''||t||''''; \n 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)\n 7 loop\n 8 s:=s||lpad(i.parameter,20)||'='||rpad(i.value,20);\n 9 end loop;\n 10 return s||' example: '||to_char(trunc(sysdate,'yy')-1,'ds');\n 11 end;\n 12 select rpad(value,20)||nls_territory_defaults(value) \"TERRITORY default NLS settings\" from v$nls_valid_values where parameter='TERRITORY' order by 1\n 13 \/\n \nTERRITORY default NLS settings \n----------------------------------------------------------------------------------------------------------------------------------------------------------------------\nAFGHANISTAN NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=AFGHANISTAN NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u060b example: 31\/12\/16\nALBANIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=ALBANIA NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=Lek example: 31.12.2016\nALGERIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=ALGERIA NLS_DATE_FORMAT=DD-MM-RR NLS_CURRENCY=\u062f.\u062c. example: 31\/12\/2016\nAMERICA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=AMERICA NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=$ example: 12\/31\/2016\nARGENTINA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=ARGENTINA NLS_DATE_FORMAT=DD\/MM\/RRRR NLS_CURRENCY=$ example: 31\/12\/2016\nARMENIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=ARMENIA NLS_DATE_FORMAT=DD.fmMM.RRRR NLS_CURRENCY=\u0564\u0580. example: 31.12.2016\nAUSTRALIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=AUSTRALIA NLS_DATE_FORMAT=DD\/MON\/RR NLS_CURRENCY=$ example: 31\/12\/2016\nAUSTRIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=AUSTRIA NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=\u20ac example: 31.12.2016\nAZERBAIJAN NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=AZERBAIJAN NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=\u20bc example: 31.12.2016\nBAHAMAS NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=BAHAMAS NLS_DATE_FORMAT=DD\/MM\/RRRR NLS_CURRENCY=B$ example: 31\/12\/2016\nBAHRAIN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=BAHRAIN NLS_DATE_FORMAT=DD-MM-RR NLS_CURRENCY=\u062f.\u0628. example: 31\/12\/2016\nBANGLADESH NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=BANGLADESH NLS_DATE_FORMAT=DD-MM-RRRR NLS_CURRENCY=\u09f3 example: 31-12-2016\nBELARUS NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=BELARUS NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=\u0440. example: 31.12.2016\nBELGIUM NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=BELGIUM NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u20ac example: 31\/12\/2016\nBELIZE NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=BELIZE NLS_DATE_FORMAT=fmDD\/MM\/RRRR NLS_CURRENCY=BZ$ example: 12\/31\/2016\nBERMUDA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=BERMUDA NLS_DATE_FORMAT=DD\/MM\/RRRR NLS_CURRENCY=BD$ example: 31\/12\/2016\nBOLIVIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=BOLIVIA NLS_DATE_FORMAT=fmDD-MM-RR NLS_CURRENCY=B$ example: 12-31-2016\nBOSNIA AND HERZEGOVINLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=BOSNIA AND HERZEGOVI NLS_DATE_FORMAT=fmMM.DD.RRRR NLS_CURRENCY=\u041a\u041c example: 12.31.2016\nBRAZIL NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=BRAZIL NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=R$ example: 31\/12\/2016\nBULGARIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=BULGARIA NLS_DATE_FORMAT=RRRR-MM-DD NLS_CURRENCY=\u043b\u0432 example: 31.12.2016\nCAMBODIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=CAMBODIA NLS_DATE_FORMAT=RRRR-fmMM-DD NLS_CURRENCY=\u17db example: 2016-12-31\nCAMEROON NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=CAMEROON NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=CFA example: 31\/12\/16\nCANADA NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=CANADA NLS_DATE_FORMAT=RR-MM-DD NLS_CURRENCY=$ example: 2016-12-31\nCATALONIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=CATALONIA NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u20ac example: 31-12-16\nCHILE NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=CHILE NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=$ example: 31-12-2016\nCHINA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=CHINA NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=\uffe5 example: 2016-12-31\nCIS NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=CIS NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=\u0440. example: 31.12.16\nCOLOMBIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=COLOMBIA NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=$ example: 31\/12\/2016\nCONGO BRAZZAVILLE NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=CONGO BRAZZAVILLE NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=CFA example: 31\/12\/16\nCONGO KINSHASA NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=CONGO KINSHASA NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=FrCD example: 31\/12\/16\nCOSTA RICA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=COSTA RICA NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=C example: 31\/12\/2016\nCROATIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=CROATIA NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=kn example: 31.12.16\nCYPRUS NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=CYPRUS NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u00a3 example: 31\/12\/2016\nCZECH REPUBLIC NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=CZECH REPUBLIC NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=K\u010d example: 31.12.2016\nCZECHOSLOVAKIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=CZECHOSLOVAKIA NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=K\u010ds example: 31.12.16\nDENMARK NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=DENMARK NLS_DATE_FORMAT=RR-MM-DD NLS_CURRENCY=kr example: 31-12-2016\nDJIBOUTI NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=DJIBOUTI NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u0641.\u062c. example: 31\/12\/2016\nECUADOR NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=ECUADOR NLS_DATE_FORMAT=DD\/MM\/RRRR NLS_CURRENCY=$ example: 31\/12\/2016\nEGYPT NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=EGYPT NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u062c.\u0645. example: 31\/12\/2016\nEL SALVADOR NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=EL SALVADOR NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=C example: 31\/12\/2016\nESTONIA NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=ESTONIA NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=kr example: 31.12.2016\nETHIOPIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=ETHIOPIA NLS_DATE_FORMAT=fmDD\/MM\/RRRR NLS_CURRENCY=\u1265\u122d example: 12\/31\/2016\nFINLAND NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=FINLAND NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=\u20ac example: 31.12.2016\nFRANCE NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=FRANCE NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u20ac example: 31\/12\/2016\nFYR MACEDONIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=FYR MACEDONIA NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=\u0434\u0435\u043d. example: 31.12.2016\nGABON NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=GABON NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=CFA example: 31\/12\/16\nGERMANY NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=GERMANY NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=\u20ac example: 31.12.2016\nGREECE NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=GREECE NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u20ac example: 31\/12\/2016\nGUATEMALA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=GUATEMALA NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=Q example: 31\/12\/2016\nHONDURAS NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=HONDURAS NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=L example: 31.12.2016\nHONG KONG NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=HONG KONG NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=$ example: 31\/12\/2016\nHUNGARY NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=HUNGARY NLS_DATE_FORMAT=RR-MON-DD NLS_CURRENCY=Ft example: 2016.12.31.\nICELAND NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=ICELAND NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=kr. example: 31.12.2016\nINDIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=INDIA NLS_DATE_FORMAT=DD-MM-RR NLS_CURRENCY=Rs example: 31\/12\/2016\nINDONESIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=INDONESIA NLS_DATE_FORMAT=DD-MM-RRRR NLS_CURRENCY=Rp example: 31-12-2016\nIRAN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=IRAN NLS_DATE_FORMAT=RRRR\/fmMM\/fmDD NLS_CURRENCY=\u0631\u064a\u0627\u0644 example: 2016\/12\/31 \nIRAQ NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=IRAQ NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u062f.\u0639. example: 31\/12\/2016\nIRELAND NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=IRELAND NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=\u20ac example: 31\/12\/2016\nISRAEL NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=ISRAEL NLS_DATE_FORMAT=DD-MON-RRRR NLS_CURRENCY=\u05e9\"\u05d7 example: 31\/12\/2016\nITALY NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=ITALY NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=\u20ac example: 31\/12\/2016\nIVORY COAST NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=IVORY COAST NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=CFA example: 31\/12\/16\nJAPAN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=JAPAN NLS_DATE_FORMAT=RR-MM-DD NLS_CURRENCY=\u00a5 example: 2016\/12\/31\nJORDAN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=JORDAN NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u062f.\u0627.\u200f example: 31\/12\/2016 \nKAZAKHSTAN NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=KAZAKHSTAN NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=KZT example: 31.12.2016\nKENYA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=KENYA NLS_DATE_FORMAT=fmMM\/DD\/RRRR NLS_CURRENCY=S example: 12\/31\/2016\nKOREA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=KOREA NLS_DATE_FORMAT=RR\/MM\/DD NLS_CURRENCY=\uffe6 example: 2016\/12\/31\nKUWAIT NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=KUWAIT NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u062f.\u0643. example: 31\/12\/2016\nLAOS NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=LAOS NLS_DATE_FORMAT=DD\/fmMM\/RRRR NLS_CURRENCY=\u20ad example: 31\/12\/2016\nLATVIA NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=LATVIA NLS_DATE_FORMAT=RRRR.MM.DD NLS_CURRENCY=Ls example: 2016.12.31\nLEBANON NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=LEBANON NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u0644.\u0644. example: 31\/12\/2016\nLIBYA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=LIBYA NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u062f.\u0644. example: 31\/12\/2016\nLITHUANIA NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=LITHUANIA NLS_DATE_FORMAT=RRRR.MM.DD NLS_CURRENCY=\u20ac example: 2016-12-31\nLUXEMBOURG NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=LUXEMBOURG NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u20ac example: 31.12.2016\nMACEDONIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=MACEDONIA NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=den. example: 31.12.2016\nMALAYSIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=MALAYSIA NLS_DATE_FORMAT=DD\/MM\/RRRR NLS_CURRENCY=RM example: 31\/12\/2016\nMALDIVES NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=MALDIVES NLS_DATE_FORMAT=fmDD\/fmMM\/RR NLS_CURRENCY=\u0783. example: 12\/31\/16\nMALTA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=MALTA NLS_DATE_FORMAT=fmDD\/MM\/RRRR NLS_CURRENCY=\u20ac example: 12\/31\/2016\nMAURITANIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=MAURITANIA NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u0627.\u0645. example: 31\/12\/2016\nMEXICO NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=MEXICO NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=$ example: 31\/12\/2016\nMONTENEGRO NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=MONTENEGRO NLS_DATE_FORMAT=DD.MM.RRRR. NLS_CURRENCY=\u20ac example: 31.12.2016.\nMOROCCO NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=MOROCCO NLS_DATE_FORMAT=DD-MM-RR NLS_CURRENCY=\u062f.\u0645. example: 31\/12\/2016\nNEPAL NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=NEPAL NLS_DATE_FORMAT=fmDD\/MM\/RRRR NLS_CURRENCY=\u0930 example: 12\/31\/2016\nNEW ZEALAND NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=NEW ZEALAND NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=$ example: 31\/12\/2016\nNICARAGUA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=NICARAGUA NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=C$ example: 31\/12\/2016\nNIGERIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=NIGERIA NLS_DATE_FORMAT=DD\/fmMM\/RRRR NLS_CURRENCY=\u20a6 example: 31\/12\/2016\nNORWAY NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=NORWAY NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=kr example: 31.12.2016\nOMAN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=OMAN NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u0631.\u0639. example: 31\/12\/2016\nPAKISTAN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=PAKISTAN NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=PKR example: 31\/12\/2016\nPANAMA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=PANAMA NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=B\/ example: 12\/31\/2016\nPARAGUAY NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=PARAGUAY NLS_DATE_FORMAT=fmDD\/MM\/RR NLS_CURRENCY=G example: 12\/31\/16\nPERU NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=PERU NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=S\/ example: 31\/12\/2016\nPHILIPPINES NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=PHILIPPINES NLS_DATE_FORMAT=MM\/DD\/RRRR NLS_CURRENCY=Php example: 12\/31\/2016\nPOLAND NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=POLAND NLS_DATE_FORMAT=RR\/MM\/DD NLS_CURRENCY=z\u0142 example: 2016-12-31\nPORTUGAL NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=PORTUGAL NLS_DATE_FORMAT=RR.MM.DD NLS_CURRENCY=\u20ac example: 31-12-2016\nPUERTO RICO NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=PUERTO RICO NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=$ example: 31\/12\/2016\nQATAR NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=QATAR NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u0631.\u0642. example: 31\/12\/2016\nROMANIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=ROMANIA NLS_DATE_FORMAT=DD-MM-RRRR NLS_CURRENCY=LEI example: 31.12.2016\nRUSSIA NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=RUSSIA NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=\u20bd example: 31.12.2016\nSAUDI ARABIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=SAUDI ARABIA NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u0631.\u0633. example: 31\/12\/2016\nSENEGAL NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=SENEGAL NLS_DATE_FORMAT=DD\/MM\/RRRR NLS_CURRENCY=CFA example: 31\/12\/2016\nSERBIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=SERBIA NLS_DATE_FORMAT=DD.MM.RRRR. NLS_CURRENCY=RSD example: 31.12.2016.\nSERBIA AND MONTENEGRNLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=SERBIA AND MONTENEGR NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=din. example: 31.12.2016\nSINGAPORE NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=SINGAPORE NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=S$ example: 31\/12\/2016\nSLOVAKIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=SLOVAKIA NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=Sk example: 31.12.16\nSLOVENIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=SLOVENIA NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=SIT example: 31.12.16\nSOMALIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=SOMALIA NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u0634.\u0635. example: 31\/12\/2016\nSOUTH AFRICA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=SOUTH AFRICA NLS_DATE_FORMAT=DD\/MON\/RR NLS_CURRENCY=R example: 2016\/12\/31\nSPAIN NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=SPAIN NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u20ac example: 31\/12\/2016\nSRI LANKA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=SRI LANKA NLS_DATE_FORMAT=RRRR\/fmMM\/fmDD NLS_CURRENCY=\u0631\u064a\u0627\u0644 example: 2016\/12\/31 \nSUDAN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=SUDAN NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u062c.\u0633. example: 31\/12\/2016\nSWEDEN NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=SWEDEN NLS_DATE_FORMAT=RRRR-MM-DD NLS_CURRENCY=Kr example: 2016-12-31\nSWITZERLAND NLS_NUMERIC_CHARACTE=.' NLS_ISO_CURRENCY=SWITZERLAND NLS_DATE_FORMAT=DD.MM.RR NLS_CURRENCY=SFr. example: 31.12.2016\nSYRIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=SYRIA NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u0644.\u0633. example: 31\/12\/2016\nTAIWAN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=TAIWAN NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=NT$ example: 2016\/12\/31\nTANZANIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=TANZANIA NLS_DATE_FORMAT=fmMM\/DD\/RRRR NLS_CURRENCY=TSh example: 12\/31\/2016\nTHAILAND NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=THAILAND NLS_DATE_FORMAT=DD MON RRRR NLS_CURRENCY=\u0e3f example: 31\/12\/2016\nTHE NETHERLANDS NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=THE NETHERLANDS NLS_DATE_FORMAT=DD-MM-RR NLS_CURRENCY=\u20ac example: 31-12-2016\nTUNISIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=TUNISIA NLS_DATE_FORMAT=DD-MM-RR NLS_CURRENCY=\u062f.\u062a. example: 31\/12\/2016\nTURKEY NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=TURKEY NLS_DATE_FORMAT=DD\/MM\/RRRR NLS_CURRENCY=\u20ba example: 31.12.2016\nUGANDA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=UGANDA NLS_DATE_FORMAT=DD\/MM\/RRRR NLS_CURRENCY=CFA example: 31\/12\/2016\nUKRAINE NLS_NUMERIC_CHARACTE=, NLS_ISO_CURRENCY=UKRAINE NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=\u0433\u0440\u043d. example: 31.12.2016\nUNITED ARAB EMIRATESNLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=UNITED ARAB EMIRATES NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u062f.\u0625. example: 31\/12\/2016\nUNITED KINGDOM NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=UNITED KINGDOM NLS_DATE_FORMAT=DD-MON-RR NLS_CURRENCY=\u00a3 example: 31\/12\/2016\nURUGUAY NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=URUGUAY NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=NU$ example: 31\/12\/2016\nUZBEKISTAN NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=UZBEKISTAN NLS_DATE_FORMAT=DD\/MON\/RR NLS_CURRENCY=UZS example: 31\/DEC\/16\nVENEZUELA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=VENEZUELA NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=Bs example: 31\/12\/2016\nVIETNAM NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=VIETNAM NLS_DATE_FORMAT=DD-MM-RRRR NLS_CURRENCY=\u20ab example: 31-12-2016\nYEMEN NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=YEMEN NLS_DATE_FORMAT=DD\/MM\/RR NLS_CURRENCY=\u0631.\u064a. example: 31\/12\/2016\nYUGOSLAVIA NLS_NUMERIC_CHARACTE=,. NLS_ISO_CURRENCY=YUGOSLAVIA NLS_DATE_FORMAT=DD.MM.RRRR NLS_CURRENCY=din. example: 31.12.2016\nZAMBIA NLS_NUMERIC_CHARACTE=., NLS_ISO_CURRENCY=ZAMBIA NLS_DATE_FORMAT=DD\/MM\/RRRR NLS_CURRENCY=ZK example: 31\/12\/2016\n<\/code><\/pre>\nLANGUAGE<\/h3>\n
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.<\/p>\n
\nSQL> with \n 2 function nls_language_defaults(t varchar2) return varchar2 as \n 3 s varchar2(2017):='';\n 4 begin \n 5 execute immediate 'alter session set nls_language='''||t||''''; \n 6 for i in (select * from nls_session_parameters where parameter in ('NLS_DATE_LANGUAGE','NLS_SORT') order by parameter desc)\n 7 loop\n 8 s:=s||lpad(i.parameter,20)||'='||rpad(i.value,20);\n 9 end loop;\n 10 return s||' example: '||to_char(trunc(sysdate,'yy')-1,'dl'); \n 11 end;\n 12 select rpad(value,20)||nls_language_defaults(value) \"LANGUAGE default NLS settings\" from v$nls_valid_values where parameter='LANGUAGE' order by 1\n 13 \/\n \nLANGUAGE default NLS settings \n-----------------------------------------------------------------------------------------------------------------------------------------------\nALBANIAN NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=ALBANIAN example: E Shtun\u00eb, Dhjetor 31, 2016\nAMERICAN NLS_SORT=BINARY NLS_DATE_LANGUAGE=AMERICAN example: Saturday, December 31, 2016\nAMHARIC NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=AMHARIC example: \u1245\u12f3\u121c, \u12f2\u1234\u121d\u1260\u122d 31, 2016\nARABIC NLS_SORT=ARABIC NLS_DATE_LANGUAGE=ARABIC example: \u0627\u0644\u0633\u0628\u062a, \u062f\u064a\u0633\u0645\u0628\u0631 31, 2016\nARMENIAN NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=ARMENIAN example: \u0547\u0561\u0562\u0561, \u0534\u0565\u056f\u057f\u0565\u0574\u0562\u0565\u0580 31, 2016\nASSAMESE NLS_SORT=BINARY NLS_DATE_LANGUAGE=ASSAMESE example: \u09b6\u09a8\u09bf\u09ac\u09be\u09f0, \u09a1\u09bf\u099a\u09c7\u09ae\u09cd\u09ac\u09f0 31, 2016 \nAZERBAIJANI NLS_SORT=AZERBAIJANI NLS_DATE_LANGUAGE=AZERBAIJANI example: \u015e\u0259nb\u0259, Dekabr 31, 2016\nBANGLA NLS_SORT=BINARY NLS_DATE_LANGUAGE=BANGLA example: \u09b6\u09a8\u09bf\u09ac\u09be\u09b0, \u09a1\u09bf\u09b8\u09c7\u09ae\u09cd\u09ac\u09b0 31, 2016 \nBELARUSIAN NLS_SORT=RUSSIAN NLS_DATE_LANGUAGE=BELARUSIAN example: \u0421\u0443\u0431\u043e\u0442\u0430, \u0421\u044c\u043d\u0435\u0436\u0430\u043d\u044c 31, 2016\nBENGALI NLS_SORT=BENGALI NLS_DATE_LANGUAGE=BENGALI example: \u09b6\u09a8\u09bf\u09ac\u09be\u09b0, \u09a1\u09bf\u09b8\u09c7\u09ae\uf8a0\u09b0 31, 2016\nBRAZILIAN PORTUGUESE NLS_SORT=WEST_EUROPEAN NLS_DATE_LANGUAGE=BRAZILIAN PORTUGUESE example: S\u00e1bado, Dezembro 31, 2016\nBULGARIAN NLS_SORT=BULGARIAN NLS_DATE_LANGUAGE=BULGARIAN example: \u0421\u044a\u0431\u043e\u0442\u0430, \u0414\u0435\u043a\u0435\u043c\u0432\u0440\u0438 31, 2016\nCANADIAN FRENCH NLS_SORT=CANADIAN_M NLS_DATE_LANGUAGE=CANADIAN FRENCH example: Samedi, D\u00e9cembre 31, 2016\nCATALAN NLS_SORT=CATALAN NLS_DATE_LANGUAGE=CATALAN example: Dissabte, Desembre 31, 2016\nCROATIAN NLS_SORT=CROATIAN NLS_DATE_LANGUAGE=CROATIAN example: Subota, Prosinac 31, 2016\nCYRILLIC KAZAKH NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=CYRILLIC KAZAKH example: \u0421\u0435\u043d\u0431\u0456, \u0416\u0435\u043b\u0442\u043e\u049b\u0441\u0430\u043d 31, 2016\nCYRILLIC SERBIAN NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=CYRILLIC SERBIAN example: \u0421\u0443\u0431\u043e\u0442\u0430, \u0414\u0435\u0446\u0435\u043c\u0431\u0430\u0440 31, 2016\nCYRILLIC UZBEK NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=CYRILLIC UZBEK example: \u0428\u0430\u043d\u0431\u0430, \u0414\u0435\u043a\u0430\u0431\u0440 31, 2016\nCZECH NLS_SORT=CZECH NLS_DATE_LANGUAGE=CZECH example: Sobota, Prosinec 31, 2016\nDANISH NLS_SORT=DANISH NLS_DATE_LANGUAGE=DANISH example: L\u00f8rdag, December 31, 2016\nDARI NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=DARI example: \u0634\u0646\u0628\u0647, \u062f\u0633\u0645\u0628\u0631 31, 2016\nDIVEHI NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=DIVEHI example: \u0780\u07ae\u0782\u07a8\u0780\u07a8\u0783\u07aa, \u0791\u07a8\u0790\u07ac\u0789\u07b0\u0784\u07a6\u0783 31, 2016\nDUTCH NLS_SORT=DUTCH NLS_DATE_LANGUAGE=DUTCH example: Zaterdag, December 31, 2016\nEGYPTIAN NLS_SORT=ARABIC NLS_DATE_LANGUAGE=EGYPTIAN example: \u0627\u0644\u0633\u0628\u062a, \u062f\u064a\u0633\u0645\u0628\u0631 31, 2016\nENGLISH NLS_SORT=BINARY NLS_DATE_LANGUAGE=ENGLISH example: Saturday, December 31, 2016\nESTONIAN NLS_SORT=ESTONIAN NLS_DATE_LANGUAGE=ESTONIAN example: Laup\u00e4ev, Detsember 31, 2016\nFINNISH NLS_SORT=FINNISH NLS_DATE_LANGUAGE=FINNISH example: Lauantai, Joulukuu 31, 2016\nFRENCH NLS_SORT=FRENCH NLS_DATE_LANGUAGE=FRENCH example: Samedi, D\u00e9cembre 31, 2016\nGERMAN NLS_SORT=GERMAN NLS_DATE_LANGUAGE=GERMAN example: Samstag, Dezember 31, 2016\nGERMAN DIN NLS_SORT=GERMAN NLS_DATE_LANGUAGE=GERMAN DIN example: Samstag, Dezember 31, 2016\nGREEK NLS_SORT=GREEK NLS_DATE_LANGUAGE=GREEK example: \u03a3\u03ac\u03b2\u03b2\u03b1\u03c4\u03bf, \u0394\u03b5\u03ba\u03ad\u03bc\u03b2\u03c1\u03b9\u03bf\u03c2 31, 2016\nGUJARATI NLS_SORT=BINARY NLS_DATE_LANGUAGE=GUJARATI example: \u0ab6\u0aa8\u0abf\u0ab5\u0abe\u0ab0, \u0aa1\u0abf\u0ab8\u0ac7\u0aae\u0acd\u0aac\u0ab0 31, 2016 \nHEBREW NLS_SORT=HEBREW NLS_DATE_LANGUAGE=HEBREW example: \u05e9\u05d1\u05ea, \u05d3\u05e6\u05de\u05d1\u05e8 31, 2016\nHINDI NLS_SORT=BINARY NLS_DATE_LANGUAGE=HINDI example: \u0936\u0928\u093f\u0935\u093e\u0930, \u0926\u093f\u0938\u092e\u094d\u092c\u0930 31, 2016 \nHUNGARIAN NLS_SORT=HUNGARIAN NLS_DATE_LANGUAGE=HUNGARIAN example: Szombat, December 31, 2016\nICELANDIC NLS_SORT=ICELANDIC NLS_DATE_LANGUAGE=ICELANDIC example: Laugardagur, Desember 31, 2016\nINDONESIAN NLS_SORT=INDONESIAN NLS_DATE_LANGUAGE=INDONESIAN example: Sabtu, Desember 31, 2016\nIRISH NLS_SORT=BINARY NLS_DATE_LANGUAGE=IRISH example: D\u00e9 Sathairn, Nollaig 31, 2016\nITALIAN NLS_SORT=WEST_EUROPEAN NLS_DATE_LANGUAGE=ITALIAN example: Sabato, Dicembre 31, 2016\nJAPANESE NLS_SORT=BINARY NLS_DATE_LANGUAGE=JAPANESE example: \u571f\u66dc\u65e5, 12\u6708 31, 2016\nKANNADA NLS_SORT=BINARY NLS_DATE_LANGUAGE=KANNADA example: \u0cb6\u0ca8\u0cbf\u0cb5\u0cbe\u0cb0, \u0ca1\u0cbf\u0cb8\u0cc6\u0c82\u0cac\u0cb0\u0ccd 31, 2016 \nKHMER NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=KHMER example: \u1790\u17d2\u1784\u17c3\u179f\u17c5\u179a\u17cd, \u1792\u17d2\u1793\u17bc 31, 2016 \nKOREAN NLS_SORT=BINARY NLS_DATE_LANGUAGE=KOREAN example: \ud1a0\uc694\uc77c, 12\uc6d4 31, 2016\nLAO NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=LAO example: \u0ea7\u0eb1\u0e99\u0ec0\u0eaa\u0ebb\u0eb2, \u0e97\u0eb1\u0e99\u0ea7\u0eb2 31, 2016\nLATIN AMERICAN SPANI NLS_SORT=SPANISH NLS_DATE_LANGUAGE=LATIN AMERICAN SPANI example: S\u00e1bado, Diciembre 31, 2016\nLATIN BOSNIAN NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=LATIN BOSNIAN example: Subota, Decembar 31, 2016\nLATIN SERBIAN NLS_SORT=BINARY NLS_DATE_LANGUAGE=LATIN SERBIAN example: Subota, Decembar 31, 2016\nLATIN UZBEK NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=LATIN UZBEK example: Shanba, Dekabr 31, 2016\nLATVIAN NLS_SORT=LATVIAN NLS_DATE_LANGUAGE=LATVIAN example: Sestdiena, Decembris 31, 2016\nLITHUANIAN NLS_SORT=LITHUANIAN NLS_DATE_LANGUAGE=LITHUANIAN example: \u0160e\u0161tadienis, Gruod\u017eio 31, 2016\nMACEDONIAN NLS_SORT=BINARY NLS_DATE_LANGUAGE=MACEDONIAN example: \u0421\u0430\u0431\u043e\u0442\u0430, \u0414\u0435\u043a\u0435\u043c\u0432\u0440\u0438 31, 2016\nMALAY NLS_SORT=MALAY NLS_DATE_LANGUAGE=MALAY example: Sabtu, Disember 31, 2016\nMALAYALAM NLS_SORT=BINARY NLS_DATE_LANGUAGE=MALAYALAM example: \u0d36\u0d28\u0d3f\u0d06\u0d34\u0d4d\u0d1a, \u0d21\u0d3f\u0d38\u0d02\u0d2c\u0d30\u0d4d 31, 2016 \nMALTESE NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=MALTESE example: Is-Sibt, Di\u010bembru 31, 2016\nMARATHI NLS_SORT=BINARY NLS_DATE_LANGUAGE=MARATHI example: \u0936\u0928\u093f\u0935\u093e\u0930, \u0921\u093f\u0938\u0947\u0902\u092c\u0930 31, 2016 \nMEXICAN SPANISH NLS_SORT=WEST_EUROPEAN NLS_DATE_LANGUAGE=MEXICAN SPANISH example: S\u00e1bado, Diciembre 31, 2016\nNEPALI NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=NEPALI example: \u0936\u0928\u093f\u092c\u093e\u0930, \u0921\u093f\u0938\u0947\u092e\u094d\u092c\u0930 31, 2016 \nNORWEGIAN NLS_SORT=NORWEGIAN NLS_DATE_LANGUAGE=NORWEGIAN example: L\u00f8rdag, Desember 31, 2016\nORIYA NLS_SORT=BINARY NLS_DATE_LANGUAGE=ORIYA example: \u0b36\u0b28\u0b3f\u0b2c\u0b3e\u0b30, \u0b21\u0b3f\u0b38\u0b47\u0b2e\u0b4d\u0b2c\u0b30 31, 2016 \nPERSIAN NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=PERSIAN example: \u0634\u0646\u0628\u0647, \u062f\u0633\u0627\u0645\u0628\u0631 31, 2016\nPOLISH NLS_SORT=POLISH NLS_DATE_LANGUAGE=POLISH example: Sobota, Grudzie\u0144 31, 2016\nPORTUGUESE NLS_SORT=WEST_EUROPEAN NLS_DATE_LANGUAGE=PORTUGUESE example: S\u00e1bado, Dezembro 31, 2016\nPUNJABI NLS_SORT=BINARY NLS_DATE_LANGUAGE=PUNJABI example: \u0a36\u0a28\u0a3f\u0a1a\u0a71\u0a30\u0a35\u0a3e\u0a30, \u0a26\u0a38\u0a70\u0a2c\u0a30 31, 2016\nROMANIAN NLS_SORT=ROMANIAN NLS_DATE_LANGUAGE=ROMANIAN example: S\u00e2mb\u0103t\u0103, Decembrie 31, 2016\nRUSSIAN NLS_SORT=RUSSIAN NLS_DATE_LANGUAGE=RUSSIAN example: \u0421\u0443\u0431\u0431\u043e\u0442\u0430, \u0414\u0435\u043a\u0430\u0431\u0440\u044c 31, 2016\nSIMPLIFIED CHINESE NLS_SORT=BINARY NLS_DATE_LANGUAGE=SIMPLIFIED CHINESE example: \u661f\u671f\u516d, 12\u6708 31, 2016\nSINHALA NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=SINHALA example: \u0dc3\u0dd9\u0db1\u0dc3\u0dd4\u0dbb\u0dcf\u0daf\u0dcf, \u0daf\u0dd9\u0dc3\u0dd0\u0db8\u0dca\u0db6\u0dbb\u0dca 31, 2016 \nSLOVAK NLS_SORT=SLOVAK NLS_DATE_LANGUAGE=SLOVAK example: Sobota, December 31, 2016\nSLOVENIAN NLS_SORT=SLOVENIAN NLS_DATE_LANGUAGE=SLOVENIAN example: Sobota, December 31, 2016\nSPANISH NLS_SORT=SPANISH NLS_DATE_LANGUAGE=SPANISH example: S\u00e1bado, Diciembre 31, 2016\nSWAHILI NLS_SORT=GENERIC_M NLS_DATE_LANGUAGE=SWAHILI example: Jumamosi, Desemba 31, 2016\nSWEDISH NLS_SORT=SWEDISH NLS_DATE_LANGUAGE=SWEDISH example: L\u00f6rdag, December 31, 2016\nTAMIL NLS_SORT=BINARY NLS_DATE_LANGUAGE=TAMIL example: \u0b9a\u0ba9\u0bbf\u0b95\u0bcd\u0b95\u0bbf\u0bb4\u0bae\u0bc8, \u0b9f\u0bbf\u0b9a\u0bae\u0bcd\u0baa\u0bb0\u0bcd 31, 2016 \nTELUGU NLS_SORT=BINARY NLS_DATE_LANGUAGE=TELUGU example: \u0c36\u0c28\u0c3f\u0c35\u0c3e\u0c30\u0c02, \u0c21\u0c3f\u0c38\u0c46\u0c02\u0c2c\u0c30\u0c4d 31, 2016 \nTHAI NLS_SORT=THAI_DICTIONARY NLS_DATE_LANGUAGE=THAI example: \u0e40\u0e2a\u0e32\u0e23\u0e4c, \u0e18\u0e31\u0e19\u0e27\u0e32\u0e04\u0e21 31, 2016 \nTRADITIONAL CHINESE NLS_SORT=BINARY NLS_DATE_LANGUAGE=TRADITIONAL CHINESE example: \u661f\u671f\u516d, 12\u6708 31, 2016\nTURKISH NLS_SORT=TURKISH NLS_DATE_LANGUAGE=TURKISH example: Cumartesi, Aral\u0131k 31, 2016\nUKRAINIAN NLS_SORT=UKRAINIAN NLS_DATE_LANGUAGE=UKRAINIAN example: \u0421\u0443\u0431\u043e\u0442\u0430, \u0413\u0440\u0443\u0434\u0435\u043d\u044c 31, 2016\nVIETNAMESE NLS_SORT=VIETNAMESE NLS_DATE_LANGUAGE=VIETNAMESE example: Th\u1ee9 B\u1ea3y, Th\u00e1ng M\u01b0\u01a1\u0300I Hai 31, 2016\n<\/code><\/pre>\nNLS_LANG<\/h3>\n
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.<\/p>\n
Here is an example.
\nI set NLS_DATE_FORMAT from the environment:<\/p>\n
\n[oracle@VM102 data]$ export NLS_DATE_FORMAT=DL\n<\/code><\/pre>\nbut the date format is the set in my session:<\/p>\n
[oracle@VM102 data]$ sqlplus -s \/ as sysdba <<< "select sysdate from dual;"\n \nSYSDATE\n---------\n04-JAN-17<\/code><\/pre>\nNow I set NLS_LANG (and you don’t need to specify a LANGUAGE, TERRITORY or CHARACTERSET, just put the separators ‘_’ and ‘.’)<\/p>\n
\n[oracle@VM102 data]$ export NLS_LANG=\"_.\"\n<\/code><\/pre>\nAnd now my NLS_DATE_FORMAT is taken into account<\/p>\n
\n[oracle@VM102 data]$ sqlplus -s \/ as sysdba <<< "select sysdate from dual;"\n \nSYSDATE\n-----------------------------\nWednesday, January 04, 2017\n<\/code><\/pre>\nIn 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).<\/p>\n
I wish you an Happy New Year 2017.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . 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… <\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[4133],"tags":[],"class_list":["post-9643","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"\n
NLS defaults for LANGUAGE and TERRITORY - dbi services | Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/nls-defaults-for-language-and-territory\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"NLS defaults for LANGUAGE and TERRITORY\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . 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… \" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/nls-defaults-for-language-and-territory\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi services | Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2017-01-04T12:09:27+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"19 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/nls-defaults-for-language-and-territory\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/nls-defaults-for-language-and-territory\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bf7c7eb0bec165183207237c9d0ee1d5\"},\"headline\":\"NLS defaults for LANGUAGE and TERRITORY\",\"datePublished\":\"2017-01-04T12:09:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/nls-defaults-for-language-and-territory\\\/\"},\"wordCount\":294,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"articleSection\":[\"Database\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/nls-defaults-for-language-and-territory\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/nls-defaults-for-language-and-territory\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/nls-defaults-for-language-and-territory\\\/\",\"name\":\"NLS defaults for LANGUAGE and TERRITORY - dbi services | Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2017-01-04T12:09:27+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/nls-defaults-for-language-and-territory\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/nls-defaults-for-language-and-territory\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/nls-defaults-for-language-and-territory\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"NLS defaults for LANGUAGE and TERRITORY\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"alternateName\":\"dbi Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\",\"name\":\"dbi services\",\"alternateName\":\"dbi\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bf7c7eb0bec165183207237c9d0ee1d5\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/oracle-square.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/oracle-square.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/oracle-square.png\",\"caption\":\"Oracle Team\"},\"description\":\"Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"NLS defaults for LANGUAGE and TERRITORY - dbi services | Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/nls-defaults-for-language-and-territory\/","og_locale":"en_US","og_type":"article","og_title":"NLS defaults for LANGUAGE and TERRITORY","og_description":"By Franck Pachot . 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… ","og_url":"https:\/\/www.dbi-services.com\/blog\/nls-defaults-for-language-and-territory\/","og_site_name":"dbi services | Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2017-01-04T12:09:27+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"19 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/nls-defaults-for-language-and-territory\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/nls-defaults-for-language-and-territory\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bf7c7eb0bec165183207237c9d0ee1d5"},"headline":"NLS defaults for LANGUAGE and TERRITORY","datePublished":"2017-01-04T12:09:27+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/nls-defaults-for-language-and-territory\/"},"wordCount":294,"commentCount":1,"publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"articleSection":["Database"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/nls-defaults-for-language-and-territory\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/nls-defaults-for-language-and-territory\/","url":"https:\/\/www.dbi-services.com\/blog\/nls-defaults-for-language-and-territory\/","name":"NLS defaults for LANGUAGE and TERRITORY - dbi services | Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-01-04T12:09:27+00:00","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/nls-defaults-for-language-and-territory\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/nls-defaults-for-language-and-territory\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/nls-defaults-for-language-and-territory\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"NLS defaults for LANGUAGE and TERRITORY"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"alternateName":"dbi Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.dbi-services.com\/blog\/#organization","name":"dbi services","alternateName":"dbi","url":"https:\/\/www.dbi-services.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bf7c7eb0bec165183207237c9d0ee1d5","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/oracle-square.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/oracle-square.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/oracle-square.png","caption":"Oracle Team"},"description":"Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.","url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9643","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=9643"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9643\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9643"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9643"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9643"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}