Oracle Database enables you to store, process, and retrieve data in native languages. The languages that can be stored in a database are all languages written in scripts that are encoded by Oracle-supported character sets. Through the use of Unicode databases and data types, Oracle Database supports most contemporary languages.

Additional support is available for a subset of the languages. The database can, for example, display dates using translated month names, and can sort text data according to cultural conventions.

In this document, the term language support refers to the additional language-dependent functionality, and not to the ability to store text of a specific language. For example, language support includes displaying dates or sorting text according to specific locales and cultural conventions. Additionally, for some supported languages, Oracle Database provides translated server messages and a translated user interface for the database utilities.

The default local time format, date format, and numeric and monetary conventions depend on the local territory setting.

Oracle Database supports cultural conventions that are specific to geographical locations. The default local time format, date format, and numeric and monetary conventions depend on the local territory setting. Setting different NLS parameters enables the database session to use different cultural settings. For example, you can set the euro (EUR) as the primary currency and the Japanese yen (JPY) as the secondary currency for a given database session, even when the territory is AMERICA.

Different countries have different conventions for displaying the hour, day, month, and year.

For example, this table shows the local date and time format for five countries and gives an example of each format:

Different countries use different calendars.

Oracle Database stores this calendar information for each territory:

• First day of the week

  Sunday in some cultures, Monday in others. Set by the NLS_TERRITORY parameter.

• First week of the calendar year

  Some countries use week numbers for scheduling, planning, and bookkeeping. In the ISO standard, this week number can differ from the week number of the calendar year. For example, 1st Jan 2005 is in ISO week number 53 of 2004. An ISO week starts on Monday and ends on Sunday. To support the ISO standard, Oracle Database provides the IW date format element, which returns the ISO week number. The first calendar week of the year is set by the NLS_TERRITORY parameter.

• Number of days and months in a year

  Oracle Database supports six calendar systems in addition to the Gregorian calendar, which is the default. These additional calendar systems are:

  • Japanese Imperial

    Has the same number of months and days as the Gregorian calendar, but the year starts with the beginning of each Imperial Era.

  • ROC Official

    Has the same number of months and days as the Gregorian calendar, but the year starts with the founding of the Republic of China.

  • Persian

    The first six months have 31 days each, the next five months have 30 days each, and the last month has either 29 days or (in leap year) 30 days.

  • Thai Buddha uses a Buddhist calendar.

  • Arabic Hijrah has 12 months and 354 or 355 days.

  • English Hijrah has 12 months and 354 or 355 days.

  The calendar system is specified by the NLS_CALENDAR parameter.

• First year of era

  The Islamic calendar starts from the year of the Hegira. The Japanese Imperial calendar starts from the beginning of an Emperor's reign (for example, 1998 is the tenth year of the Heisei era).

Different countries have different numeric and monetary conventions.

This table shows the local numeric and monetary format for five countries and gives an example of each format:

Different languages have different sort orders (collating sequences). Also, different countries or cultures that use the same alphabets sort words differently. For example, in Danish, Æ is after Z, and Y and Ü are considered to be variants of the same letter.

To calculate the number of characters in a string, using byte length, you must know the number of bytes in each character in the character set.

In single-byte character sets, the number of bytes and the number of characters in a string are the same. In multibyte character sets, a character or code point consists of one or more bytes. Calculating the number of characters based on byte length can be difficult in a variable-width character set. Calculating column length in bytes is called byte semantics, while measuring column length in characters is called character semantics.

Character semantics is useful for specifying the storage requirements for multibyte strings of varying widths. For example, in a Unicode database (AL32UTF8), suppose that you must have a VARCHAR2 column that can store up to five Chinese characters with five English characters. Using byte semantics, this column requires 15 bytes for the Chinese characters, which are 3 bytes long, and 5 bytes for the English characters, which are 1 byte long, for a total of 20 bytes. Using character semantics, the column requires 10 characters.

Unicode is a character encoding system that defines every character in most of the spoken languages in the world. In Unicode, every character has a unique code, regardless of the platform, program, or language.

You can store Unicode characters in an Oracle Database in two ways:

• You can create a Unicode database that enables you to store UTF-8 encoded characters as SQL character data types (CHAR, VARCHAR2, CLOB, and LONG).

• You can declare columns and variables that have SQL national character data types.

The SQL national character data types are NCHAR, NVARCHAR2, and NCLOB. They are also called Unicode data types, because they are used only for storing Unicode data.

The national character set, which is used for all SQL national character data types, is specified when the database is created. The national character set can be either UTF8 or AL16UTF16 (default).

When you declare a column or variable of the type NCHAR or NVARCHAR2, the length that you specify is the number of characters, not the number of bytes.

You can change the values of NLS parameters for all SQL Developer connections, current and future.

1. From the SQL Developer menu Tools, select Preferences.
2. In the Preferences window, in the left frame, expand Database.
3. In the list of database preferences, click NLS.

   A list of NLS parameters and their current values appears. The value fields are menus.

4. From the menu to the right of each parameter whose value you want to change, select the desired value.
5. Click OK.

   The NLS parameters now have the values that you specified. To verify these values, see "Viewing NLS Parameter Values".

SQL functions whose behavior depends on the values of NLS parameters are called locale-dependent. Some locale-dependent SQL functions have optional NLS parameters.

'parameter=value' ['parameter=value']...

SELECT last_name FROM employees WHERE hire_date > '01-JAN-1999';

ALTER SESSION SET NLS_DATE_LANGUAGE=American;
SELECT last_name FROM employees WHERE hire_date > '01-JAN-1999';

SELECT last_name FROM employees
WHERE hire_date > TO_DATE('01-JAN-1999', 'DD-MON-YYYY',
                          'NLS_DATE_LANGUAGE=AMERICAN');

A locale is a linguistic and cultural environment in which a system or application runs. The simplest way to specify a locale for Oracle Database software is to set the NLS_LANG parameter.

The NLS_LANG parameter sets the default values of the parameters NLS_LANGUAGE and NLS_TERRITORY for both the server session (for example, SQL statement processing) and the client application (for example, display formatting in Oracle Database tools). The NLS_LANG parameter also sets the character set that the client application uses for data entered or displayed.

The default value of NLS_LANG is set during database installation. You can use the ALTER SESSION statement to change the values of NLS parameters, including those set by NLS_LANG, for your session. However, only the client can change the NLS settings in the client environment.

This parameter specifies the default language of the database.

Specifies: Default language of the database. Default conventions for:

• Language for server messages

• Language for names and abbreviations of days and months that are specified in the SQL functions TO_CHAR and TO_DATE

• Symbols for default-language equivalents of AM, PM, AD, and BC

• Default sorting order for character data when the ORDER BY clause is specified

• Writing direction

• Affirmative and negative response strings (for example, YES and NO)

Acceptable Values: Any language name that Oracle supports. For a list, see Oracle Database Globalization Support Guide.

Default Value: Set by NLS_LANG, described in "About Locale and the NLS_LANG Parameter".

Sets default values of:

• NLS_DATE_LANGUAGE, described in "About the NLS_DATE_LANGUAGE Parameter".

• NLS_SORT, described in "About the NLS_SORT Parameter".

Example 7-1 shows how setting NLS_LANGUAGE to ITALIAN and GERMAN affects server messages and month abbreviations.

To try this example in SQL Developer, enter the statements and queries in the Worksheet. For information about the Worksheet, see "Running Queries in SQL Developer". The results shown here are from SQL*Plus; their format is slightly different in SQL Developer.

This parameter specifies default conventions for date format, time stamp format, decimal and group separator, local currency symbol, ISO currency symbol, and dual currency symbol.

Specifies: Default conventions for:

• Date format

• Time stamp format

• Decimal character and group separator

• Local currency symbol

• ISO currency symbol

• Dual currency symbol

Acceptable Values: Any territory name that Oracle supports. For a list, see Oracle Database Globalization Support Guide.

Default Value: Set by NLS_LANG, described in "About Locale and the NLS_LANG Parameter".

Sets default values of:

• NLS_DATE_FORMAT, described in "About the NLS_DATE_FORMAT Parameter".

• NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT, described in "About NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT Parameters".

• NLS_NUMERIC_CHARACTERS, described in "About the NLS_NUMERIC_CHARACTERS Parameter".

• NLS_CURRENCY, described in "About the NLS_CURRENCY Parameter".

• NLS_ISO_CURRENCY, described in "About the NLS_ISO_CURRENCY Parameter".

• NLS_DUAL_CURRENCY, described in "About the NLS_DUAL_CURRENCY Parameter".

Example 7-2 shows how setting NLS_TERRITORY to JAPAN and AMERICA affects the currency symbol.

To try this example in SQL Developer, enter the statements and queries in the Worksheet. For information about the Worksheet, see "Running Queries in SQL Developer". The results shown here are from SQL*Plus; their format is slightly different in SQL Developer.

This parameter specifies the default date format to use with the TO_CHAR and TO_DATE functions.

Specifies: Default date format to use with the TO_CHAR and TO_DATE functions (which are introduced in "Using Conversion Functions in Queries").

Acceptable Values: Any any valid datetime format model. For example:

NLS_DATE_FORMAT='MM/DD/YYYY'

For information about datetime format models, see Oracle Database SQL Language Reference.

Default Value: Set by NLS_TERRITORY, described in "About the NLS_TERRITORY Parameter".

The default date format might not correspond to the convention used in a given territory. To get dates in localized formats, you can use the 'DS' (short date) and 'DL' (long date) formats.

Example 7-3 shows how setting NLS_TERRITORY to AMERICA and FRANCE affects the default, short, and long date formats.

Example 7-4 changes the value of NLS_DATE_FORMAT, overriding the default value set by NLS_TERRITORY.

To try the examples in SQL Developer, enter the statements and queries in the Worksheet. For information about the Worksheet, see "Running Queries in SQL Developer". The results shown here are from SQL*Plus; their format is slightly different in SQL Developer.

This parameter specifies the language for names and abbreviations of days and months that are produced by: SQL functions TO_CHAR and TO_DATE, the default date format (set by NLS_DATE_FORMAT), and symbols for the default-language equivalents of AM, PM, AD, and BC.

Specifies: Language for names and abbreviations of days and months that are produced by:

• SQL functions TO_CHAR and TO_DATE (which are introduced in "Using Conversion Functions in Queries")

• Default date format (set by NLS_DATE_FORMAT, described in "About the NLS_DATE_FORMAT Parameter")

• Symbols for default-language equivalents of AM, PM, AD, and BC

Acceptable Values: Any language name that Oracle supports. For a list, see Oracle Database Globalization Support Guide.

Default Value: Set by NLS_LANGUAGE, described in "About the NLS_LANGUAGE Parameter".

Example 7-5 shows how setting NLS_DATE_LANGUAGE to FRENCH and SWEDISH affects the displayed system date.

To try this example in SQL Developer, enter the statements and queries in the Worksheet. For information about the Worksheet, see "Running Queries in SQL Developer". The results shown here are from SQL*Plus; their format is slightly different in SQL Developer.

This parameter specifies the default date format for the TIMESTAMP audiotape and TIMESTAMP WITH LOCAL TIME ZONEaudiotapeTIMESTAMP WITH LOCAL TIME ZONEaudiotape.

Specify: Default date format for:

• TIMESTAMP audiotape

• TIMESTAMP WITH LOCAL TIME ZONEaudiotape

Acceptable Values: Any any valid datetime format model. For example:

NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH:MI:SS.FF'
NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH:MI:SS.FF TZH:TZM'

For information about datetime format models, see Oracle Database SQL Language Reference.

Default Value: Set by NLS_TERRITORY, described in "About the NLS_TERRITORY Parameter".

This parameter specifies the calendar system for the database.

Specifies: Calendar system for the database.

Acceptable Values: Any calendar system that Oracle supports. For a list, see Oracle Database Globalization Support Guide.

Default Value: Gregorian

Example 7-6 shows how setting NLS_CALENDAR to 'English Hijrah' and Gregorian affects the displayed system date.

To try this example in SQL Developer, enter the statements and queries in the Worksheet. For information about the Worksheet, see "Running Queries in SQL Developer". The results shown here are from SQL*Plus; their format is slightly different in SQL Developer.

This parameter specifies the decimal character (which separates the integer and decimal parts of a number) and group separator (which separates integer groups to show thousands and millions, for example). The group separator is the character returned by the numeric format element G.

Specifies: Decimal character (which separates the integer and decimal parts of a number) and group separator (which separates integer groups to show thousands and millions, for example). The group separator is the character returned by the numeric format element G.

Acceptable Values: Any two different single-byte characters except:

• A numeric character

• Plus (+)

• Minus (-)

• Less than (<)

• Greater than (>)

Default Value: Set by NLS_TERRITORY, described in "About the NLS_TERRITORY Parameter".

In a SQL statement, you can represent a number as either:

• Numeric literal

  A numeric literal is not enclosed in quotation marks, always uses a period (.) as the decimal character, and never contains a group separator.

• Text literal

  A text literal is enclosed in single quotation marks. It is implicitly or explicitly converted to a number, if required, according to the current NLS settings.

Example 7-7 shows how two different NLS_NUMERIC_CHARACTERS settings affect the displayed result of the same query.

To try this example in SQL Developer, enter the statements and queries in the Worksheet. For information about the Worksheet, see "Running Queries in SQL Developer". The results shown here are from SQL*Plus; their format is slightly different in SQL Developer.

This parameter specifies the local currency symbol (the character string returned by the numeric format element L).

Specifies: Local currency symbol (the character string returned by the numeric format element L).

Acceptable Values: Any valid currency symbol string.

Default Value: Set by NLS_TERRITORY, described in "About the NLS_TERRITORY Parameter".

Example 7-8 changes the value of NLS_CURRENCY, overriding the default value set by NLS_TERRITORY. To try this example in SQL Developer, enter the statements and queries in the Worksheet. For information about the Worksheet, see "Running Queries in SQL Developer". The results shown here are from SQL*Plus; their format is slightly different in SQL Developer.

This parameter specifies the ISO currency symbol (the string returned by the numeric format element C).

Specifies: ISO currency symbol (the character string returned by the numeric format element C).

Acceptable Values: Any valid currency symbol string.

Default Value: Set by NLS_TERRITORY, described in "About the NLS_TERRITORY Parameter".

Local currency symbols can be ambiguous, but ISO currency symbols are unique.

Example 7-9 shows that the territories AUSTRALIA and AMERICA have the same local currency symbol, but different ISO currency symbols.

To try this example in SQL Developer, enter the statements and queries in the Worksheet. For information about the Worksheet, see "Running Queries in SQL Developer". The results shown here are from SQL*Plus; their format is slightly different in SQL Developer.

This parameter specifies the dual currency symbol (introduced to support the euro currency symbol during the euro transition period).

Specifies: Dual currency symbol (introduced to support the euro currency symbol during the euro transition period).

Acceptable Values: Any valid currency symbol string.

Default Value: Set by NLS_TERRITORY, described in "About the NLS_TERRITORY Parameter".

This parameter specifies the linguistic sort order (collating sequence) for queries that have the ORDER BY clause.

Specifies: Linguistic sort order (collating sequence) for queries that have the ORDER BY clause.

Acceptable Values:

• BINARY

  Sort order is based on the binary sequence order of either the database character set or the national character set, depending on the data type.

• Any linguistic sort name that Oracle supports

  Sort order is based on the order of the specified linguistic sort name. The linguistic sort name is usually the same as the language name, but not always. For a list of supported linguistic sort names, see Oracle Database Globalization Support Guide.

Default Value: Set by NLS_LANGUAGE, described in "About the NLS_LANGUAGE Parameter".

Example 7-10 shows how two different NLS_SORT settings affect the displayed result of the same query. The settings are BINARY and Traditional Spanish (SPANISH_M). Traditional Spanish treats ch, ll, and ñ as letters that follow c, l, and n, respectively.

To try this example in SQL Developer, enter the statements and queries in the Worksheet. For information about the Worksheet, see "Running Queries in SQL Developer". The results shown here are from SQL*Plus; their format is slightly different in SQL Developer.

This parameter specifies the character-comparison behavior of SQL operations.

Specifies: Character-comparison behavior of SQL operations.

Acceptable Values:

• BINARY

  SQL compares the binary codes of characters. One character is greater than another if it has a higher binary code.

• LINGUISTIC

  SQL performs a linguistic comparison based on the value of the NLS_SORT parameter, described in "About the NLS_SORT Parameter".

• ANSI

  This value is provided only for backward compatibility.

Default Value: BINARY

Example 7-11 shows that the result of a query can depend on the NLS_COMP setting.

To try this example in SQL Developer, enter the statements and queries in the Worksheet. For information about the Worksheet, see "Running Queries in SQL Developer". The results shown here are from SQL*Plus; their format is slightly different in SQL Developer.

This parameter specifies the length semantics for columns of the character data types CHAR, VARCHAR2, and LONG; that is, whether these columns are specified in bytes or in characters. (Applies only to columns that are declared after the parameter is set.)

Specifies: Length semantics for columns of the character data types CHAR, VARCHAR2, and LONG; that is, whether these columns are specified in bytes or in characters. (Applies only to columns that are declared after the parameter is set.)

Acceptable Values:

• BYTE

  New CHAR, VARCHAR2, and LONG columns are specified in bytes.

• CHAR

  New CHAR, VARCHAR2, and LONG columns are specified in characters.

Default Value: BYTE

To try this example in SQL Developer, enter the statements and queries in the Worksheet. For information about the Worksheet, see "Running Queries in SQL Developer". The results shown here are from SQL*Plus; their format is slightly different in SQL Developer.

There are three ways to represent a Unicode string literal in SQL or PL/SQL.

As part of a SQL or PL/SQL statement, a literal (with or without the prefix N) is encoded in the same character set as the rest of the statement. On the client side, the statement is encoded in the client character set, which is determined by the NLS_LANG parameter. On the server side, the statement is encoded in the database character set.

When the SQL or PL/SQL statement is transferred from the client to the database, its character set is converted accordingly. If the database character set does not contain all characters that the client used in the text literals, then data is lost in this conversion. NCHAR string literals are more vulnerable than CHAR text literals, because they are designed to be independent of the database character set.

To avoid data loss in conversion to an incompatible database character set, you can activate the NCHAR literal replacement functionality. For more information, see Oracle Database Globalization Support Guide.