NLS Formats

NLS Formats
Specifying an NLS parameter for a SQL function means that a user session NLS parameter (or the lack of) 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 NAME FROM EMP WHERE HIREDATE > ‘1-JAN-01’

This can be made independent of the current date language by specifying nls_date_language as well as using to_date with a date format:
SELECT NAME FROM EMP WHERE HIREDATE > TO_DATE(‘1-JAN-01′,’DD-MON-YY’, ‘nls_date_language = AMERICAN’)

Of course a simpler way of making this language-independent is: SELECT ENAME FROM EMP WHERE HIREDATE > to_date(‘1-01-01′,’DD-MM-YY’)

NLS settings include character set, language and territory.

Character Set
NLS Character Set is derived from NLS_LANG environment variable. The most common character sets are:

US7ASCII ASCII 7-bit American English
WE8ISO8859P15 ISO 8859-15 West European English includes euro character. Is a superset of US7ASCII
UTF8 Unicode 3.0 UTF-8 Universal character set, CESU-8 compliant
UTFE EBCDIC form of Unicode 3.0 UTF-8 Universal character set

Language
nls_language is derived from NLS_LANG environment variable. nls_language also implicitly defines nls_date_language and nls_sort

us AMERICAN nl DUTCH is ICELANDIC pl POLISH
ar ARABIC eg EGYPTIAN in INDONESIAN pt PORTUGUESE
bn BENGALI gb ENGLISH i ITALIAN ro ROMANIAN
ptb BRAZILIAN PORTUGUESE et ESTONIAN ja JAPANESE ru RUSSIAN
bg BULGARIAN sf FINNISH ko KOREAN sk SLOVAK
frc CANADIAN FRENCH f FRENCH esa LATIN AMERICAN SPANISH sl SLOVENIAN
ca CATALAN din GERMAN DIN lv LATVIAN e SPANISH
zhs SIMPLIFIED CHINESE d GERMAN lt LITHUANIAN s SWEDISH
hr CROATIAN el GREEK ms MALAY th THAI
cs CZECH iw HEBREW esm MEXICAN SPANISH zht TRADITIONAL CHINESE
dk DANISH hu HUNGARIAN n NORWEGIAN tr TURKISH
uk UKRAINIAN vn VIETNAMESE

Territory
nls_territory is derived from NLS_LANG environment variable. nls_territory also implicitly defines several other parameters:
nls_numeric_characters Specifies the characters to use as the group separator and decimal character.
nls_currency Specifies the string to use as the local currency symbol for the L number format element.
nls_iso_currency Specifies the string to use as the international currency symbol for the C number format element.
nls_date_format Specifies the default date format to use with the to_char and to_date functions.

ALGERIA CHINA FRANCE JAPAN MAURITANIA SINGAPORE THAILAND
AMERICA CIS GERMANY JORDAN MEXICO SLOVAKIA THE NETHERLANDS
AUSTRALIA CROATIA GREECE KAZAKHSTAN MOROCCO SLOVENIA TUNISIA
AUSTRIA CYPRUS HONG KONG KOREA NEW ZEALAND SOMALIA TURKEY
BAHRAIN CZECH HUNGARY KUWAIT NORWAY SOUTH AFRICA UKRAINE
BANGLADESH CZECHOSLOVAKIA ICELAND LATVIA OMAN SPAIN UNITED ARAB EMIRATES
BELGIUM DENMARK INDONESIA LEBANON POLAND SUDAN UNITED KINGDOM
BRAZIL DJIBOUTI IRAQ LIBYA PORTUGAL SWEDEN UZBEKISTAN
BULGARIA EGYPT IRELAND LITHUANIA QATAR SWITZERLAND VIETNAM
CANADA ESTONIA ISRAEL LUXEMBOURG ROMANIA SYRIA YEMEN
CATALONIA FINLAND ITALY MALAYSIA SAUDI ARABIA TAIWAN

nls_comp provides a simple alternative to specifying nls_sort in a WHERE clause. NLS formats will affect SQL statements in views, CHECK constraints, and triggers.

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:

Y1999 Y2000 Y2005 Y2050
———– ———– ———– ———–
01-JAN-1999 01-JAN-2000 01-JAN-2005 01-JAN-2050
Related Parameters:
nls_calendar Specifies which calendar system Oracle uses.
nls_comp NLS comparison.
nls_currency Specifies the string to use as the local currency symbol for the L number format element.
nls_date_format Specifies the default date format to use with the TO_CHAR and TO_DATE functions.
nls_date_language Specifies the language to use for the spelling of day & month names and date abbreviations returned by TO_DATE and TO_CHAR functions.
nls_dual_currency Specifies the dual currency symbol (such as “Euro”) for the territory.
nls_iso_currency Specifies the string to use as the international currency symbol for the C number format element.
nls_language Specifies the default language of the database.
nls_length_semantics Enables creation of CHAR and VARCHAR2 columns using either byte or character length semantics.
nls_nchar_conv_excp Determines whether data loss during an implicit or explicit character type conversion will report an error.
nls_numeric_characters Specifies the characters to use as the group separator and decimal character.
nls_sort Specifies the collating sequence for ORDER BY queries.
nls_territory Specifies the name of the territory whose conventions are to be followed for day and week numbering.
nls_time_format Defines the default time format to use with the TO_CHAR and TO_TIMESTAMP functions.
nls_time_tz_format Defines tthe default time with time zone format to use with the TO_CHAR and TO_time functions.
nls_timestamp_format Defines the default timestamp format to use with the TO_CHAR and TO_TIMESTAMP functions.
nls_timestamp_tz_format Defines tthe default timestamp with time zone format to use with the TO_CHAR and TO_TIMESTAMP functions.

DATABASE_PROPERTIES Lists permanent database properties
NLS_DATABASE_PARAMETERS NLS parameters of the database.
NLS_INSTANCE_PARAMETERS NLS parameters of the instance.
NLS_SESSION_PARAMETERS NLS parameters of the user session.
V$NLS_PARAMETERS Contains current values of NLS parameters.
V$NLS_VALID_VALUES Contains all possible valid NLS parameter settings.
V$TIMEZONE_NAMES This view lists valid time zone names.
SYS.PROPS$ Database fixed properties.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: