This section contains:
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:
|
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 Locale and the NLS_LANG Parameter.
Sets default values of:
NLS_DATE_LANGUAGE, described in NLS_DATE_LANGUAGE Parameter.
NLS_SORT, described in NLS_SORT Parameter.
The following example shows how setting NLS_LANGUAGE to ITALIAN and GERMAN affects server messages and month abbreviations.
Example: NLS_LANGUAGE Affects Server Message and Month Abbreviations
Note the current value of NLS_LANGUAGE.
If the value in step 0 is not ITALIAN, change it:
ALTER SESSION SET NLS_LANGUAGE=ITALIAN;
Query a nonexistent table:
SELECT * FROM nonexistent_table;
Result:
SELECT * FROM nonexistent_table
*
ERROR at line 1:
ORA-00942: tabella o vista inesistente
Run this 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.
Change the value of NLS_LANGUAGE to GERMAN:
ALTER SESSION SET NLS_LANGUAGE=GERMAN;
Repeat the query from step 3.
Result:
SELECT * FROM nonexistent_table
*
ERROR at line 1:
ORA-00942: Tabelle oder View nicht vorhanden
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.
Set NLS_LANGUAGE to the value that it had at step 1.
Specifies: Default conventions for:
Date format
Timestamp 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 Locale and the NLS_LANG Parameter.
Sets default values of:
NLS_DATE_FORMAT, described in NLS_DATE_FORMAT Parameter.
NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT, described in NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT Parameters.
NLS_NUMERIC_CHARACTERS, described in NLS_NUMERIC_CHARACTERS Parameter.
NLS_CURRENCY, described in NLS_CURRENCY Parameter.
NLS_ISO_CURRENCY, described in NLS_ISO_CURRENCY Parameter.
NLS_DUAL_CURRENCY, described in NLS_DUAL_CURRENCY Parameter.
The following example shows how setting NLS_TERRITORY to JAPAN and AMERICA affects the currency symbol.
Example: NLS_TERRITORY Affects Currency Symbol
Note the current value of NLS_TERRITORY.
If the value in step 1 is not JAPAN, change it:
ALTER SESSION SET NLS_TERRITORY=JAPAN;
Run this 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.
Change the value of NLS_TERRITORY to AMERICA:
ALTER SESSION SET NLS_TERRITORY=AMERICA;
Repeat the query from step 3.
Result:
SALARY
--------------------
$24,000.00
$17,000.00
$17,000.00
3 rows selected.
Set NLS_TERRITORY to the value that it had at step 1.
Specifies: Default date format to use with the TO_CHAR and TO_DATE functions.
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 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.
The following example shows how setting NLS_TERRITORY to AMERICA and FRANCE affects the default, short, and long date formats.
Example: NLS_TERRITORY Affects Date Formats
Note the current value of NLS_TERRITORY.
If the value instep 1 is not AMERICA, change it:
ALTER SESSION SET NLS_TERRITORY=AMERICA;
Run this 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-97 9/30/1997 Tuesday, September 30, 1997 07-MAR-98 3/7/1998 Saturday, March 07, 1998 07-DEC-99 12/7/1999 Tuesday, December 07, 1999 3 rows selected.
Change the value of NLS_TERRITORY to FRANCE:
ALTER SESSION SET NLS_TERRITORY=FRANCE;
Repeat the query from step 3.
Result:
Default Short Long -------- ---------- --------------------------- 30/09/97 30/09/1997 tuesday 30 september 1997 07/03/98 07/03/1998 saturday 7 march 1998 07/12/99 07/12/1999 tuesday 7 december 1999 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.)
Set NLS_TERRITORY to the value that it had at step 1.
The following example changes the value of NLS_DATE_FORMAT, overriding the default value set by NLS_TERRITORY.
Example: NLS_DATE_FORMAT Overrides NLS_TERRITORY
Note the current values of NLS_TERRITORY and NLS_DATE_FORMAT.
If the value of NLS_TERRITORY in step 1 is not AMERICA, change it:
ALTER SESSION SET NLS_TERRITORY=AMERICA;
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';
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 ------------------------ ---------- ----------------------------- Tuesday September 30th 9/30/1997 Tuesday, September 30, 1997 Saturday March 07th 3/7/1998 Saturday, March 07, 1998 Tuesday December 07th 12/7/1999 Tuesday, December 07, 1999 3 rows selected.
Set NLS_TERRITORY and NLS_DATE_FORMAT to the values that they had at step 1.
Specifies: Language for names and abbreviations of days and months that are produced by:
SQL functions TO_CHAR and TO_DATE
Default date format (set by NLS_DATE_FORMAT, described in 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 "NLS_LANGUAGE Parameter".
The following example shows how setting NLS_DATE_LANGUAGE to FRENCH and SWEDISH affects the displayed system date.
Example: NLS_DATE_LANGUAGE Affects Displayed SYSDATE
Note the current value of NLS_DATE_LANGUAGE.
If the value of NLS_DATE_LANGUAGE in step 1 is not FRENCH, change it:
ALTER SESSION SET NLS_DATE_LANGUAGE=FRENCH;
Run this query:
SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') "System Date" FROM DUAL;
Result:
System Date -------------------------- Mercredi:01 Octobre 2008
Change the value of NLS_DATE_LANGUAGE to SWEDISH:
ALTER SESSION SET NLS_DATE_LANGUAGE=SWEDISH;
Repeat the query from step 3.
Result:
System Date ------------------------- Onsdag :01 Oktober 2008
Set NLS_DATE_LANGUAGE to the value that it had at step 1.
Specify: Default date format for:
TIMESTAMP datatype
TIMESTAMP WITH LOCAL TIME ZONE datatype
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 NLS_TERRITORY Parameter.
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
The following example shows how setting NLS_CALENDAR to 'English Hijrah' and Gregorian affects the displayed system date.
Example: NLS_CALENDAR Affects Displayed SYSDATE
Note the current value of NLS_CALENDAR.
If the value of NLS_CALENDAR in step 1 is not 'English Hijrah', change it:
ALTER SESSION SET NLS_CALENDAR='English Hijrah';
Run this query:
SELECT SYSDATE FROM DUAL;
Result:
SYSDATE
-------------------------
29 Ramadan 1429
Change the value of NLS_CALENDAR to 'Gregorian':
ALTER SESSION SET NLS_CALENDAR='Gregorian';
Run this query:
SELECT SYSDATE FROM DUAL;
Result:
SYSDATE
---------
30-SEP-08
Set NLS_CALENDAR to the value that it had at step 1.
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 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.
The following example shows how two different NLS_NUMERIC_CHARACTERS settings affect the displayed result of the same query.
Example: NLS_NUMERIC_CHARACTERS Affects Decimal Character and Group Separator
Note the current value of NLS_NUMERIC_CHARACTERS.
If the value of NLS_NUMERIC_CHARACTERS in step 1 is not ",." (decimal character is comma and group separator is period), change it:
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=",.";
Run this query:
SELECT TO_CHAR(4000, '9G999D99') "Number" FROM DUAL;
Result:
Number --------- 4.000,00
Change the value of NLS_NUMERIC_CHARACTERS to ",." (decimal character is period and group separator is comma):
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=".,";
Run this query:
SELECT TO_CHAR(4000, '9G999D99') "Number" FROM DUAL;
Result:
Number --------- 4,000.00
Set NLS_NUMERIC_CHARACTERS to the value that it had at step 1.
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 NLS_TERRITORY Parameter.
The following example changes the value of NLS_CURRENCY, overriding the default value set by NLS_TERRITORY.
Example: NLS_CURRENCY Overrides NLS_TERRITORY
Note the current values of NLS_TERRITORY and NLS_CURRENCY.
If the value of NLS_TERRITORY in step 1 is not AMERICA, change it:
ALTER SESSION SET NLS_TERRITORY=AMERICA;
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
Change the value of NLS_CURRENCY to '¥':
ALTER SESSION SET NLS_CURRENCY='¥';
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
Set NLS_TERRITORY and NLS_CURRENCY to the values that they had at step 1.
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 NLS_TERRITORY Parameter.
Local currency symbols can be ambiguous, but ISO currency symbols are unique.
The following example shows that the territories AUSTRALIA and AMERICA have the same local currency symbol, but different ISO currency symbols.
Example: NLS_ISO_CURRENCY
Note the current values of NLS_TERRITORY and NLS_ISO_CURRENCY.
If the value of NLS_TERRITORY in step 1 is not AUSTRALIA, change it:
ALTER SESSION SET NLS_TERRITORY=AUSTRALIA;
Run this 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
Change the value of NLS_TERRITORY to AMERICA:
ALTER SESSION SET NLS_TERRITORY=AMERICA;
Run this 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
Set NLS_TERRITORY and NLS_ISO_CURRENCY to the values that they had at step 1.
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 NLS_TERRITORY Parameter.
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 NLS_LANGUAGE Parameter.
The following example 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.
Example: NLS_SORT Affects Linguistic Sort Order
Create table for Spanish words:
CREATE TABLE temp (name VARCHAR2(15));
Populate table with some Spanish words:
INSERT INTO temp (name) VALUES ('laguna');
INSERT INTO temp (name) VALUES ('llama');
INSERT INTO temp (name) VALUES ('loco');
Note the current value of NLS_SORT.
If the value of NLS_SORT in step 3 is not BINARY, change it:
ALTER SESSION SET NLS_SORT=BINARY;
Run this query:
SELECT * FROM temp ORDER BY name;
Result:
NAME --------------- laguna llama loco
Change the value of NLS_SORT to SPANISH_M (Traditional Spanish):
ALTER SESSION SET NLS_SORT=SPANISH_M;
Repeat the query from step 5.
Result:
NAME --------------- laguna loco llama
Drop the table:
DROP TABLE temp;
Set NLS_SORT to the value that it had at step 3.
Operations inside Oracle Database are sensitive to the case and the accents (diacritics) of the characters. To perform a case-insensitive sort, append _CI to the value of the NLS_SORT parameter (for example, BINARY_CI or XGERMAN_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).
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 NLS_SORT Parameter.
ANSI
This value is provided only for backward compatibility.
Default Value: BINARY
The following example shows that the result of a query can depend on the NLS_COMP setting.
Example: NLS_COMP Affects SQL Character Comparison
Note the current values of NLS_SORT and NLS_COMP.
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;
Run this query:
SELECT LAST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE 'C%';
Result:
LAST_NAME ------------------------- Cabrio Cambrault Cambrault Chen Chung Colmenares 6 rows selected
Change the value of NLS_COMP to LINGUISTIC:
ALTER SESSION SET NLS_COMP=LINGUISTIC;
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.
Set NLS_SORT and NLS_COMP to the values that they had in step 1.
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
Example: NLS_LENGTH_SEMANTICS Affects Storage of VARCHAR2 Column
Note the current values of NLS_LENGTH_SEMANTICS.
If the value of NLS_LENGTH_SEMANTICS in step 1 is not BYTE, change it:
ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;
Create a table with a VARCHAR2 column:
CREATE TABLE SEMANTICS_BYTE(SOME_DATA VARCHAR2(20));
Click the tab Connections.
The Connections pane shows the connection hr_conn.
Expand hr_conn.
A list of schema object types appears, including Tables.
Expand Tables.
A list of tables appears, including SEMANTICS_BYTE.
Select SEMANTICS_BYTE.
To the right of the Connections pane, the Columns pane shows that for Column Name SOME_DATA, the Data Type is VARCHAR2(20 BYTE).
Change the value of NLS_LENGTH_SEMANTICS to CHAR:
ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;
Create another table with a VARCHAR2 column:
CREATE TABLE SEMANTICS_CHAR(SOME_DATA VARCHAR2(20));
In the Connections pane, click the Refresh icon.
The list of tables now includes SEMANTICS_CHAR.
Select SEMANTICS_CHAR.
The Columns pane shows that for Column Name SOME_DATA, the Data Type is VARCHAR2(20 CHAR).
Select SEMANTICS_BYTE again.
The Columns pane shows that for Column Name SOME_DATA, the Data Type is still VARCHAR2(20 BYTE).
Set the value of NLS_LENGTH_SEMANTICS to the value that it had in step 1.