|
Specifying an NLS parameter for an SQL function means that any user session
NLS parameters (or the lack of appropriate NLS parameters) will not affect evaluation
of the function.
This feature may be important for SQL statements that contain numbers and dates
as string literals. For example, the following query is evaluated correctly
only if the language specified for dates is American:
SELECT ENAME FROM EMP WHERE HIREDATE > '1-JAN-01' This can be made independent of the current date language by specifying NLS_DATE_LANGUAGE: SELECT ENAME FROM EMP WHERE HIREDATE > TO_DATE('1-JAN-01','DD-MON-YY', 'NLS_DATE_LANGUAGE = AMERICAN') Of course TO_DATE() provides a simpler way of making this language-independent SELECT ENAME FROM EMP WHERE HIREDATE > TO_DATE('1-01-01','DD-MM-YY') SELECT ENAME FROM EMP WHERE HIREDATE > TO_DATE ('November 23, 1998', 'MONTH DD, YYYY') NLS settings include Character set, Language and territory The most common Character Sets are WE8ISO8859P15 European English includes euro character US7ASCII American English
The DATE datatype always stores a four-digit year internally.
If you use the standard date format DD-MON-YY
YY will assume a year in the range 1900-1999 -
it is strongly recommended you apply a specific format mask.
YEAR 2000 - Check
SELECT to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),1 * 12),'DD-MON-YYYY') y1999, to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),2 * 12),'DD-MON-YYYY') y2000, to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),7 * 12),'DD-MON-YYYY') y2005, to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),52 * 12),'DD-MON-YYYY') y2050 FROM DUAL; -- Expected output from script above -- Y1999 Y2000 Y2005 Y2050 -- ----------- ----------- ----------- ----------- -- 01-JAN-1999 01-JAN-2000 01-JAN-2005 01-JAN-2050 Oracle Languages e.g. NLS_LANGUAGE = ENGLISH us AMERICAN ar ARABIC bn BENGALI ptb BRAZILIAN PORTUGUESE bg BULGARIAN frc CANADIAN FRENCH ca CATALAN zhs SIMPLIFIED CHINESE hr CROATIAN cs CZECH dk DANISH nl DUTCH eg EGYPTIAN gb ENGLISH et ESTONIAN sf FINNISH f FRENCH din GERMAN DIN d GERMAN el GREEK iw HEBREW hu HUNGARIAN is ICELANDIC in INDONESIAN i ITALIAN ja JAPANESE ko KOREAN esa LATIN AMERICAN SPANISH lv LATVIAN lt LITHUANIAN ms MALAY esm MEXICAN SPANISH n NORWEGIAN pl POLISH pt PORTUGUESE ro ROMANIAN ru RUSSIAN sk SLOVAK sl SLOVENIAN e SPANISH s SWEDISH th THAI zht TRADITIONAL CHINESE tr TURKISH uk UKRAINIAN vn VIETNAMESE The NLS_LANGUAGE above implicitly defines several other parameters: NLS_DATE_LANGUAGE, NLS_SORT Oracle Territories e.g. NLS_TERRITORY = "UNITED KINGDOM" ALGERIA AMERICA AUSTRALIA AUSTRIA BAHRAIN BANGLADESH BELGIUM BRAZIL BULGARIA CANADA CATALONIA CHINA CIS CROATIA CYPRUS CZECH CZECHOSLOVAKIA DENMARK DJIBOUTI EGYPT ESTONIA FINLAND FRANCE GERMANY GREECE HONG KONG HUNGARY ICELAND INDONESIA IRAQ IRELAND ISRAEL ITALY JAPAN JORDAN KAZAKHSTAN KOREA KUWAIT LATVIA LEBANON LIBYA LITHUANIA LUXEMBOURG MALAYSIA MAURITANIA MEXICO MOROCCO NEW ZEALAND NORWAY OMAN POLAND PORTUGAL QATAR ROMANIA SAUDI ARABIA SINGAPORE SLOVAKIA SLOVENIA SOMALIA SOUTH AFRICA SPAIN SUDAN SWEDEN SWITZERLAND SYRIA TAIWAN THAILAND THE NETHERLANDS TUNISIA TURKEY UKRAINE UNITED ARAB EMIRATES UNITED KINGDOM UZBEKISTAN VIETNAM YEMEN The NLS_TERRITORY implicitly defines several other parameters: NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT NLS_MONETARY_CHARACTERS, NLS_CREDIT, NLS_DEBIT If necessary these can be explicitly defined e.g. NLS_NUMERIC_CHARACTERS = ",." NLS_COMP This provides a simple alternative to specifying NLS_SORT in an SQL WHERE clause
NLS formats will affect SQL statements in
views, CHECK constraints, and triggers.
Related Commands
ALTER SESSION