.
\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.
\n
\nHere 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":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[1012,96],"type_dbi":[],"class_list":["post-9643","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-nls","tag-oracle"],"acf":[],"yoast_head":"\n
NLS defaults for LANGUAGE and TERRITORY - dbi 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 Blog\" \/>\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: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\/66ab87129f2d357f09971bc7936a77ee\"},\"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,\"keywords\":[\"NLS\",\"Oracle\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"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 Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-01-04T12:09:27+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"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 Blog\",\"description\":\"\",\"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\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"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 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 Blog","article_published_time":"2017-01-04T12:09:27+00:00","author":"Oracle Team","twitter_card":"summary_large_image","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\/66ab87129f2d357f09971bc7936a77ee"},"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,"keywords":["NLS","Oracle"],"articleSection":["Database Administration & Monitoring"],"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 Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-01-04T12:09:27+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"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 Blog","description":"","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":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"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"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9643"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}