|
|
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
