About Individual NLS Parameters

Many individual NLS parameters are available.

See Also:

About Locale and the NLS_LANG Parameter

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.

See Also:

About the NLS_LANGUAGE Parameter

This parameter specifies the default language of the database.

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

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:

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.

Example 7-1 NLS_LANGUAGE Affects Server Message and Month Abbreviations

  1. Note the current value of NLS_LANGUAGE.

  2. If the value in step 1 is not ITALIAN, change it:

     ALTER SESSION SET NLS_LANGUAGE=ITALIAN;

  3. Query a nonexistent table:

     SELECT * FROM nonexistent_table;

    Result:

     SELECT * FROM nonexistent_table
             *
 ERROR at line 1:
 ORA-00942: tabella o vista inesistente

  4. Run the following query:

     SELECT LAST_NAME, HIRE_DATE
 FROM EMPLOYEES
 WHERE EMPLOYEE_ID IN (111, 112, 113);

    Result:

     LAST_NAME                 HIRE_DATE

 ------------------------- ---------
 Sciarra                   30-SET-97
 Urman                     07-MAR-98
 Popp                      07-DIC-99

 3 rows selected.

  5. Change the value of NLS_LANGUAGE to GERMAN:

     ALTER SESSION SET NLS_LANGUAGE=GERMAN;

  6. Repeat the query from step 3.

    Result:

     SELECT * FROM nonexistent_table
             *
 ERROR at line 1:
 ORA-00942: Tabelle oder View nicht vorhanden

  7. Repeat the query from step 4.

    Result:

     LAST_NAME                 HIRE_DATE

 ------------------------- ---------
 Sciarra                   30-SEP-97
 Urman                     07-MRZ-98
 Popp                      07-DEZ-99

 3 rows selected.
  8. Set NLS_LANGUAGE to the value that it had at step 1.

See Also:

About the NLS_TERRITORY Parameter

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:

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:

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.

Example 7-2 NLS_TERRITORY Affects Currency Symbol

  1. Note the current value of NLS_TERRITORY.

  2. If the value in step 1 is not JAPAN, change it:

     ALTER SESSION SET NLS_TERRITORY=JAPAN;

  3. Run the following query:

     SELECT TO_CHAR(SALARY,'L99G999D99') SALARY
 FROM EMPLOYEES
 WHERE EMPLOYEE_ID IN (100, 101, 102);

    Result:

     SALARY

 --------------------

         ©24,000.00

         ©17,000.00

         ©17,000.00

 3 rows selected.

  4. Change the value of NLS_TERRITORY to AMERICA:

     ALTER SESSION SET NLS_TERRITORY=AMERICA;

  5. Repeat the query from step 3.

    Result:

     SALARY

 --------------------

         $24,000.00

         $17,000.00

         $17,000.00

 3 rows selected.
  6. Set NLS_TERRITORY to the value that it had at step 1.

See Also:

About the NLS_DATE_FORMAT Parameter

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.

Example 7-3 NLS_TERRITORY Affects Date Formats

  1. Note the current value of NLS_TERRITORY.

  2. If the value in step 1 is not AMERICA, change it:

     ALTER SESSION SET NLS_TERRITORY=AMERICA;

  3. Run the following query:

     SELECT hire_date "Default",
     TO_CHAR(hire_date,'DS') "Short",
     TO_CHAR(hire_date,'DL') "Long"
 FROM employees
 WHERE employee_id IN (111, 112, 113);

    Result:

     Default    Short      Long

 --------- ---------- -----------------------------

 30-SEP-05 9/30/2005  Friday, September 30, 2005

 07-MAR-98 3/7/2006   Tuesday, March 07, 2006

 07-DEC-99 12/7/2007  Friday, December 07, 2007

 3 rows selected.

  4. Change the value of NLS_TERRITORY to FRANCE:

     ALTER SESSION SET NLS_TERRITORY=FRANCE;

  5. Repeat the query from step 3.

    Result:

     Default  Short      Long

 -------- ---------- ---------------------------

 30/09/05 30/09/2005 friday 30 september 2005

 07/03/06 07/03/2006 tuesday 7 march 2006

 07/12/07 07/12/2007 friday 7 december 2007

 3 rows selected.

    (To get the names of the days and months in French, you must set either NLS_LANGUAGE or NLS_DATE_LANGUAGE to FRENCH before running the query.)

  6. Set NLS_TERRITORY to the value that it had at step 1.

Example 7-4 NLS_DATE_FORMAT Overrides NLS_TERRITORY

  1. Note the current values of NLS_TERRITORY and NLS_DATE_FORMAT.

  2. If the value of NLS_TERRITORY in step 1 is not AMERICA, change it:

     ALTER SESSION SET NLS_TERRITORY=AMERICA;

  3. If the value of NLS_DATE_FORMAT in step 1 is not 'Day Month ddth', change it:

     ALTER SESSION SET NLS_DATE_FORMAT='Day Month ddth';

  4. Run this query (from previous example, step 3):

     SELECT hire_date "Default",
     TO_CHAR(hire_date,'DS') "Short",
     TO_CHAR(hire_date,'DL') "Long"
 FROM employees
 WHERE employee_id IN (111, 112, 113);

    Result:

     Default                  Short      Long

 ------------------------ ---------- -----------------------------

 Friday    September 30th 9/30/2005  Tuesday, September 30, 2005

 Tuesday   March     07th 3/7/2006   Saturday, March 07, 2006

 Friday    December  07th 12/7/2007  Tuesday, December 07, 2007

 3 rows selected.
  5. Set NLS_TERRITORY and NLS_DATE_FORMAT to the values that they had at step 1.

See Also:

About the NLS_DATE_LANGUAGE Parameter

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:

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.

Example 7-5 NLS_DATE_LANGUAGE Affects Displayed SYSDATE

  1. Note the current value of NLS_DATE_LANGUAGE.

  2. If the value of NLS_DATE_LANGUAGE in step 1 is not FRENCH, change it:

     ALTER SESSION SET NLS_DATE_LANGUAGE=FRENCH;

  3. Run this query:

     SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') "System Date"
 FROM DUAL;

    Result:

     System Date

 --------------------------

 Vendredi:28 December   2012

  4. Change the value of NLS_DATE_LANGUAGE to SWEDISH:

     ALTER SESSION SET NLS_DATE_LANGUAGE=SWEDISH;

  5. Repeat the query from step 3.

    Result:

     System Date

 -------------------------

 Fredag :28 December   2012
  6. Set NLS_DATE_LANGUAGE to the value that it had at step 1.

See Also:

About NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT Parameters

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:

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

See Also:

About the NLS_CALENDAR 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.

Example 7-6 NLS_CALENDAR Affects Displayed SYSDATE

  1. Note the current value of NLS_CALENDAR.

  2. If the value of NLS_CALENDAR in step 1 is not 'English Hijrah', change it:

     ALTER SESSION SET NLS_CALENDAR='English Hijrah';

  3. Run the following query:

     SELECT SYSDATE FROM DUAL;

    Result:

     SYSDATE

 -------------------------

 17 Safar             1434

  4. Change the value of NLS_CALENDAR to 'Gregorian':

     ALTER SESSION SET NLS_CALENDAR='Gregorian';

  5. Run the following query:

     SELECT SYSDATE FROM DUAL;

    Result:

     SYSDATE

 ---------

 31-DEC-12
  6. Set NLS_CALENDAR to the value that it had at step 1.

See Also:

About the NLS_NUMERIC_CHARACTERS Parameter

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:

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

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

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.

Example 7-7 NLS_NUMERIC_CHARACTERS Affects Decimal Character and Group Separator

  1. Note the current value of NLS_NUMERIC_CHARACTERS.

  2. If the value of NLS_NUMERIC_CHARACTERS in step 1 is not ",." (decimal character is comma and group separator is period), use the following command to change it:

     ALTER SESSION SET NLS_NUMERIC_CHARACTERS=",.";

  3. Run the following query:

     SELECT TO_CHAR(4000, '9G999D99') "Number" FROM DUAL;

    Result:

     Number

 ---------
 4.000,00

  4. Change the value of NLS_NUMERIC_CHARACTERS to ",." (decimal character is period and group separator is comma):

     ALTER SESSION SET NLS_NUMERIC_CHARACTERS=".,";

  5. Run the following query:

     SELECT TO_CHAR(4000, '9G999D99') "Number" FROM DUAL;

    Result:

     Number

 ---------
 4,000.00
  6. Set NLS_NUMERIC_CHARACTERS to the value that it had at step 1.

See Also:

About the NLS_CURRENCY Parameter

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.

Example 7-8 NLS_CURRENCY Overrides NLS_TERRITORY

  1. Note the current values of NLS_TERRITORY and NLS_CURRENCY.

  2. If the value of NLS_TERRITORY in step 1 is not AMERICA, change it:

     ALTER SESSION SET NLS_TERRITORY=AMERICA;

  3. Run this query:

     SELECT TO_CHAR(salary, 'L099G999D99') "Salary"
 FROM EMPLOYEES
 WHERE salary > 13000;

    Result:

     Salary

 ---------------------

         $024,000.00

         $017,000.00

         $017,000.00

         $014,000.00

         $013,500.00

  4. Change the value of NLS_CURRENCY to '©':

     ALTER SESSION SET NLS_CURRENCY='©';

  5. Run this query:

     SELECT TO_CHAR(salary, 'L099G999D99') "Salary"
 FROM EMPLOYEES
 WHERE salary > 13000;

    Result:

     Salary

 ---------------------

         ©024,000.00

         ©017,000.00

         ©017,000.00

         ©014,000.00

         ©013,500.00
  6. Set NLS_TERRITORY and NLS_CURRENCY to the values that they had at step 1.

See Also:

About the NLS_ISO_CURRENCY Parameter

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.

Example 7-9 NLS_ISO_CURRENCY

  1. Note the current values of NLS_TERRITORY and NLS_ISO_CURRENCY.

  2. If the value of NLS_TERRITORY in step 1 is not AUSTRALIA, change it:

     ALTER SESSION SET NLS_TERRITORY=AUSTRALIA;

  3. Run the following query:

     SELECT TO_CHAR(salary, 'L099G999D99') "Local",
         TO_CHAR(salary, 'C099G999D99') "ISO"
 FROM EMPLOYEES
 WHERE salary > 15000;

    Result:

     Local                 ISO

 --------------------- ------------------

         $024,000.00      AUD024,000.00

         $017,000.00      AUD017,000.00

         $017,000.00      AUD017,000.00

  4. Change the value of NLS_TERRITORY to AMERICA:

     ALTER SESSION SET NLS_TERRITORY=AMERICA;

  5. Run the following query:

     SELECT TO_CHAR(salary, 'L099G999D99') "Local",
         TO_CHAR(salary, 'C099G999D99') "ISO"
 FROM EMPLOYEES
 WHERE salary > 15000;

    Result:

     Local                 ISO

 --------------------- ------------------

         $024,000.00      USD024,000.00

         $017,000.00      USD017,000.00

         $017,000.00      USD017,000.00
  6. Set NLS_TERRITORY and NLS_ISO_CURRENCY to the values that they had at step 1.

See Also:

About the NLS_DUAL_CURRENCY Parameter

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

See Also:

About the NLS_SORT 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:

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.

Case-Insensitive and Accent-Insensitive Sorts

Operations inside Oracle Database are sensitive to the case and the accents of the characters. To perform a case-insensitive sort, append _CI to the value of the NLS_SORT parameter (for example, BINARY_CI or GERMAN_CI). To perform a sort that is both case-insensitive and accent-insensitive, append _AI to the value of the NLS_SORT parameter (for example, BINARY_AI or FRENCH_M_AI).

Example 7-10 NLS_SORT Affects Linguistic Sort Order

  1. Create a table for Spanish words:

     CREATE TABLE temp (name VARCHAR2(15));

  2. Populate the table with some Spanish words:

     INSERT INTO temp (name) VALUES ('laguna');
 INSERT INTO temp (name) VALUES ('llama');
 INSERT INTO temp (name) VALUES ('loco');
  3. Note the current value of NLS_SORT.

  4. If the value of NLS_SORT in step 3 is not BINARY, change it:

     ALTER SESSION SET NLS_SORT=BINARY;

  5. Run the following query:

     SELECT * FROM temp ORDER BY name;

    Result:

     NAME

 ---------------
 laguna

 llama

 loco

  6. Change the value of NLS_SORT to SPANISH_M (Traditional Spanish):

     ALTER SESSION SET NLS_SORT=SPANISH_M;

  7. Repeat the query from step 5.

    Result:

     NAME

 ---------------
 laguna

 loco

 llama

  8. Drop the table:

     DROP TABLE temp;
  9. Set NLS_SORT to the value that it had at step 3.

See Also:

About the NLS_COMP Parameter

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

Specifies: Character-comparison behavior of SQL operations.

Acceptable Values:

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.

Example 7-11 NLS_COMP Affects SQL Character Comparison

  1. Note the current values of NLS_SORT and NLS_COMP.

  2. If the values of NLS_SORT and NLS_COMP in step 1 are not SPANISH_M (Traditional Spanish) and BINARY, respectively, change them:

     ALTER SESSION SET NLS_SORT=SPANISH_M NLS_COMP=BINARY;

  3. Run the following query:

     SELECT LAST_NAME FROM EMPLOYEES
 WHERE LAST_NAME LIKE 'C%';

    Result:

     LAST_NAME

 -------------------------
 Cabrio
 Cambrault
 Cambrault
 Chen
 Chung
 Colmenares

 6 rows selected

  4. Change the value of NLS_COMP to LINGUISTIC:

     ALTER SESSION SET NLS_COMP=LINGUISTIC;

  5. Repeat the query from step 3.

    Result:

     LAST_NAME

 -------------------------
 Cabrio
 Cambrault
 Cambrault
 Colmenares

 4 rows selected

    This time, Chen and Chung are not returned because Traditional Spanish treats ch as a single character that follows c.

  6. Set NLS_SORT and NLS_COMP to the values that they had in step 1.

See Also:

About the NLS_LENGTH_SEMANTICS Parameter

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:

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.

Example 7-12 NLS_LENGTH_SEMANTICS Affects Storage of VARCHAR2 Column

  1. Note the current values of NLS_LENGTH_SEMANTICS.

  2. If the value of NLS_LENGTH_SEMANTICS in step 1 is not BYTE, change it:

     ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

  3. Create a table with a VARCHAR2 column:

     CREATE TABLE SEMANTICS_BYTE(SOME_DATA VARCHAR2(20));

  4. Click the Connections tab.

  5. In the Connections frame, expand hr_conn.

  6. In the list of schema object types, expand Tables.

  7. In the list of tables, select SEMANTICS_BYTE.

    To the right of the Connections frame, the Columns pane shows that for Column Name SOME_DATA, the Data Type is VARCHAR2(20 BYTE).

  8. Change the value of NLS_LENGTH_SEMANTICS to CHAR:

     ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;

  9. Create another table with a VARCHAR2 column:

     CREATE TABLE SEMANTICS_CHAR(SOME_DATA VARCHAR2(20));

  10. In the Connections frame, click the Refresh icon.

    The list of tables now includes SEMANTICS_CHAR.

  11. Select SEMANTICS_CHAR.

    The Columns pane shows that for Column Name SOME_DATA, the Data Type is VARCHAR2(20 CHAR).

  12. Select SEMANTICS_BYTE again.

    The Columns pane shows that for Column Name SOME_DATA, the Data Type is still VARCHAR2(20 BYTE).

  13. Set the value of NLS_LENGTH_SEMANTICS to the value that it had in step 1.

See Also: