Skip Headers

Oracle® Database Globalization Support Guide
10g Release 1 (10.1)

Part Number B10749-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

3
Setting Up a Globalization Support Environment

This chapter tells how to set up a globalization support environment. It includes the following topics:

Setting NLS Parameters

NLS parameters determine the locale-specific behavior on both the client and the server. NLS parameters can be specified in the following ways:

Table 3-1 shows the precedence order of the different methods of setting NLS parameters. Higher priority settings override lower priority settings. For example, a default value has the lowest priority and can be overridden by any other method.

Table 3-1 Methods of Setting NLS Parameters and Their Priorities  
Priority Method

1 (highest)

Explicitly set in SQL functions

2

Set by an ALTER SESSION statement

3

Set as an environment variable

4

Specified in the initialization parameter file

5

Default

Table 3-2 lists the available NLS parameters. Because the SQL function NLS parameters can be specified only with specific functions, the table does not show the SQL function scope.

Table 3-2 NLS Parameters  
Parameter Description Default Scope: I = Initialization Parameter File
E = Environment Variable
A = ALTER SESSION

NLS_CALENDAR

Calendar system

Gregorian

I, E, A

NLS_COMP

SQL, PL/SQL operator comparison

BINARY

I, E, A

NLS_CREDIT

Credit accounting symbol

Derived from NLS_TERRITORY

E

NLS_CURRENCY

Local currency symbol

Derived from NLS_TERRITORY

I, E, A

NLS_DATE_FORMAT

Date format

Derived from NLS_TERRITORY

I, E, A

NLS_DATE_LANGUAGE

Language for day and month names

Derived from NLS_LANGUAGE

I, E, A

NLS_DEBIT

Debit accounting symbol

Derived from NLS_TERRITORY

E

NLS_ISO_CURRENCY

ISO international currency symbol

Derived from NLS_TERRITORY

I, E, A

NLS_LANG

See Also: "Choosing a Locale with the NLS_LANG Environment Variable"

Language, territory, character set

AMERICAN_AMERICA.
US7ASCII

E

NLS_LANGUAGE

Language

Derived from NLS_LANG

I, A

NLS_LENGTH_SEMANTICS

How strings are treated

BYTE

I, A

NLS_LIST_SEPARATOR

Character that separates items in a list

Derived from NLS_TERRITORY

E

NLS_MONETARY_CHARACTERS

Monetary symbol for dollar and cents (or their equivalents)

Derived from NLS_TERRITORY

E

NLS_NCHAR_CONV_EXCP

Reports data loss during a character type conversion

FALSE

I, A

NLS_NUMERIC_CHARACTERS

Decimal character and group separator

Derived from NLS_TERRITORY

I, E, A

NLS_SORT

Character sort sequence

Derived from NLS_LANGUAGE

I, E, A

NLS_TERRITORY

Territory

Derived from NLS_LANG

I, A

NLS_TIMESTAMP_FORMAT

Timestamp

Derived from NLS_TERRITORY

I, E, A

NLS_TIMESTAMP_TZ_FORMAT

Timestamp with time zone

Derived from NLS_TERRITORY

I, E, A

NLS_DUAL_CURRENCY

Dual currency symbol

Derived from NLS_TERRITORY

I, E, A

Choosing a Locale with the NLS_LANG Environment Variable

A locale is a linguistic and cultural environment in which a system or program is running. Setting the NLS_LANG environment parameter is the simplest way to specify locale behavior for Oracle software. It sets the language and territory used by the client application and the database server. It also sets the client's character set, which is the character set for data entered or displayed by a client program.

NLS_LANG is set as a local environment variable on UNIX platforms. NLS_LANG is set in the registry on Windows platforms.

The NLS_LANG parameter has three components: language, territory, and character set. Specify it in the following format, including the punctuation:

NLS_LANG = language_territory.charset

For example, if the Oracle Installer does not populate NLS_LANG, then its value by default is AMERICAN_AMERICA.US7ASCII. The language is AMERICAN, the territory is AMERICA, and the character set is US7ASCII.

Each component of the NLS_LANG parameter controls the operation of a subset of globalization support features:

The three components of NLS_LANG can be specified in many combinations, as in the following examples:

NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252

NLS_LANG = FRENCH_CANADA.WE8ISO8859P1

NLS_LANG = JAPANESE_JAPAN.JA16EUC

Note that illogical combinations can be set but do not work properly. For example, the following specification tries to support Japanese by using a Western European character set:

NLS_LANG = JAPANESE_JAPAN.WE8ISO8859P1

Because the WE8ISO8859P1 character set does not support any Japanese characters, you cannot store or display Japanese data if you use this definition for NLS_LANG.

The rest of this section includes the following topics:

Specifying the Value of NLS_LANG

Set NLS_LANG as an environment variable. For example, in a UNIX operating system C-shell session, you can specify the value of NLS_LANG by entering a statement similar to the following:

% setenv NLS_LANG FRENCH_FRANCE.WE8ISO8859P1

Because NLS_LANG is an environment variable, it is read by the client application at startup time. The client communicates the information defined by NLS_LANG to the server when it connects to the database server.

The following examples show how date and number formats are affected by the NLS_LANG parameter.

Example 3-1 Setting NLS_LANG to American_America.WE8ISO8859P1

Set NLS_LANG so that the language is AMERICAN, the territory is AMERICA, and the Oracle character set is WE8ISO8859P1:

% setenv NLS_LANG American_America.WE8ISO8859P1

Enter a SELECT statement:

SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees;

You should see results similar to the following:

LAST_NAME                 HIRE_DATE     SALARY
------------------------- --------- ----------
Sciarra                   30-SEP-97      962.5
Urman                     07-MAR-98        975
Popp                      07-DEC-99      862.5

Example 3-2 Setting NLS_LANG to French_France.WE8ISO8859P1

Set NLS_LANG so that the language is FRENCH, the territory is FRANCE, and the Oracle character set is WE8ISO8859P1:

% setenv NLS_LANG French_France.WE8ISO8859P1

Then the query shown in Example 3-1 returns the following output:

LAST_NAME                 HIRE_DAT     SALARY
------------------------- -------- ----------
Sciarra                   30/09/97      962,5
Urman                     07/03/98        975
Popp                      07/12/99      862,5

Note that the date format and the number format have changed. The numbers have not changed, because the underlying data is the same.

Overriding Language and Territory Specifications

The NLS_LANG parameter sets the language and territory environment used by both the server session (for example, SQL command execution) and the client application (for example, display formatting in Oracle tools). Using this parameter ensures that the language environments of both the database and the client application are automatically the same.

The language and territory components of the NLS_LANG parameter determine the default values for other detailed NLS parameters, such as date format, numeric characters, and linguistic sorting. Each of these detailed parameters can be set in the client environment to override the default values if the NLS_LANG parameter has already been set.

If the NLS_LANG parameter is not set, then the server session environment remains initialized with values of NLS_LANGUAGE, NLS_TERRRITORY, and other NLS instance parameters from the initialization parameter file. You can modify these parameters and restart the instance to change the defaults.

You might want to modify the NLS environment dynamically during the session. To do so, you can use the ALTER SESSION statement to change NLS_LANGUAGE, NLS_TERRITORY, and other NLS parameters.


Note:

You cannot modify the setting for the client character set with the ALTER SESSION statement.


The ALTER SESSION statement modifies only the session environment. The local client NLS environment is not modified, unless the client explicitly retrieves the new settings and modifies its local environment.

See Also:

Locale Variants

Before Oracle Database 10g Release 1 (10.1), Oracle defined language and territory definitions separately. This resulted in the definition of a territory being independent of the language setting of the user. In Oracle Database 10g Release 1 (10.1), some territories can have different date, time, number, and monetary formats based on the language setting of a user. This type of language-dependent territory definition is called a locale variant.

For the variant to work properly, both NLS_TERRITORY and NLS_LANGUAGE must be specified. For example, if NLS_LANGUAGE is specified as DUTCH and NLS_TERRITORY is not set, then the territory behavior is THE NETHERLANDS. If NLS_TERRITORY is set to BELGIUM and NLS_LANGUAGE is not set or it is set to DUTCH, then date, time, number, and monetary formats are based on DUTCH behavior. By contrast, if NLS_TERRITORY is set to BELGIUM and NLS_LANGUAGE is set to FRENCH, then date, time, number, and monetary formats are based on FRENCH behavior.

Table 3-3 shows the territories that have been enhanced to support variations. Default territory behaviors are noted. They occur when NLS_LANGUAGE is not specified.

Table 3-3 Oracle Locale Variants  
Oracle Territory Oracle Language

BELGIUM

DUTCH (default)

BELGIUM

FRENCH

BELGIUM

GERMAN

CANADA

FRENCH (default)

CANADA

ENGLISH

DJIBOUTI

FRENCH (default)

DJIBOUTI

ARABIC

FINLAND

FINLAND (default)

FINLAND

SWEDISH

HONG KONG

TRADITIONAL CHINESE (default)

HONG KONG

ENGLISH

INDIA

ENGLISH (default)

INDIA

ASSAMESE

INDIA

BANGLA

INDIA

GUJARATI

INDIA

HINDI

INDIA

KANNADA

INDIA

MALAYALAM

INDIA

MARATHI

INDIA

ORIYA

INDIA

PUNJABI

INDIA

TAMIL

INDIA

TELUGU

LUXEMBOURG

GERMAN (default)

LUXEMBOURG

FRENCH

SINGAPORE

ENGLISH (default)

SINGAPORE

MALAY

SINGAPORE

SIMPLIFIED CHINESE

SINGAPORE

TAMIL

SWITZERLAND

GERMAN (default)

SWITZERLAND

FRENCH

SWITZERLAND

ITALIAN

Should the NLS_LANG Setting Match the Database Character Set?

The NLS_LANG character set should reflect the setting of the operating system character set of the client. For example, if the database character set is AL32UTF8 and the client is running on a Windows operating system, then you should not set AL32UTF8 as the client character set in the NLS_LANG parameter because there are no UTF-8 WIN32 clients. Instead the NLS_LANG setting should reflect the code page of the client. For example, on an English Windows client, the code page is 1252. An appropriate setting for NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252.

Setting NLS_LANG correctly allows proper conversion from the client operating system character set to the database character set. When these settings are the same, Oracle assumes that the data being sent or received is encoded in the same character set as the database character set, so no validation or conversion is performed. This can lead to corrupt data if the client code page and the database character set are different and conversions are necessary.

See Also:

Oracle Database Installation Guide for Windows for more information about commonly used values of the NLS_LANG parameter in Windows

NLS Database Parameters

When a new database is created during the execution of the CREATE DATABASE statement, the NLS-related database configuration is established. The current NLS instance parameters are stored in the data dictionary along with the database and national character sets. The NLS instance parameters are read from the initialization parameter file at instance startup.

You can find the values for NLS parameters by using:

NLS Data Dictionary Views

Applications can check the session, instance, and database NLS parameters by querying the following data dictionary views:

NLS Dynamic Performance Views

Applications can check the following NLS dynamic performance views:

OCINlsGetInfo() Function

User applications can query client NLS settings with the OCINlsGetInfo() function.

See Also:

"Getting Locale Information in OCI" for the description of OCINlsGetInfo()

Language and Territory Parameters

This section contains information about the following parameters:

NLS_LANGUAGE

Property Description

Parameter type

String

Parameter scope

Initialization parameter and ALTER SESSION

Default value

Derived from NLS_LANG

Range of values

Any valid language name

NLS_LANGUAGE specifies the default conventions for the following session characteristics:

The value specified for NLS_LANGUAGE in the initialization parameter file is the default for all sessions in that instance. For example, to specify the default session language as French, the parameter should be set as follows:

NLS_LANGUAGE = FRENCH

Consider the following server message:

ORA-00942:  table or view does not exist

When the language is French, the server message appears as follows:

ORA-00942:  table ou vue inexistante

Messages used by the server are stored in binary-format files that are placed in the $ORACLE_HOME/product_name/mesg directory, or the equivalent for your operating system. Multiple versions of these files can exist, one for each supported language, using the following filename convention:

<product_id><language_abbrev>.MSB

For example, the file containing the server messages in French is called oraf.msb, because ORA is the product ID (<product_id>) and F is the language abbreviation (<language_abbrev>) for French. The product_name is rdbms, so it is in the $ORACLE_HOME/rdbms/mesg directory.

If NLS_LANG is specified in the client environment, then the value of NLS_LANGUAGE in the initialization parameter file is overridden at connection time.

Messages are stored in these files in one specific character set, depending on the language and the operating system. If this character set is different from the database character set, then message text is automatically converted to the database character set. If necessary, it is then converted to the client character set if the client character set is different from the database character set. Hence, messages are displayed correctly at the user's terminal, subject to the limitations of character set conversion.

The language-specific binary message files that are actually installed depend on the languages that the user specifies during product installation. Only the English binary message file and the language-specific binary message files specified by the user are installed.

The default value of NLS_LANGUAGE may be specific to the operating system. You can alter the NLS_LANGUAGE parameter by changing its value in the initialization parameter file and then restarting the instance.

See Also:

Your operating system-specific Oracle documentation for more information about the default value of NLS_LANGUAGE

All messages and text should be in the same language. For example, when you run an Oracle Developer application, the messages and boilerplate text that you see originate from three sources:

NLS_LANGUAGE determines the language used for the first two kinds of text. The application is responsible for the language used in its messages and boilerplate text.

The following examples show behavior that results from setting NLS_LANGUAGE to different values.

Example 3-3 NLS_LANGUAGE=ITALIAN

Use the ALTER SESSION statement to set NLS_LANGUAGE to Italian:

ALTER SESSION SET NLS_LANGUAGE=Italian;

Enter a SELECT statement:

SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees;

You should see results similar to the following:

LAST_NAME                 HIRE_DATE     SALARY
------------------------- --------- ----------
Sciarra                   30-SET-97      962.5
Urman                     07-MAR-98        975
Popp                      07-DIC-99      862.5

Note that the month name abbreviations are in Italian.

See Also:

"Overriding Default Values for NLS_LANGUAGE and NLS_TERRITORY During a Session" for more information about using the ALTER SESSION statement

Example 3-4 NLS_LANGUAGE=GERMAN

Use the ALTER SESSION statement to change the language to German:

SQL> ALTER SESSION SET NLS_LANGUAGE=German;

Enter the same SELECT statement:

SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees;

You should see results similar to the following:

LAST_NAME                 HIRE_DATE     SALARY
------------------------- --------- ----------
Sciarra                   30-SEP-97      962.5
Urman                     07-MÄR-98        975
Popp                      07-DEZ-99      862.5

Note that the language of the month abbreviations has changed.

NLS_TERRITORY

Property Description

Parameter type

String

Parameter scope

Initialization parameter and ALTER SESSION

Default value

Derived from NLS_LANG

Range of values

Any valid territory name

NLS_TERRITORY specifies the conventions for the following default date and numeric formatting characteristics:

The value specified for NLS_TERRITORY in the initialization parameter file is the default for the instance. For example, to specify the default as France, the parameter should be set as follows:

NLS_TERRITORY = FRANCE

When the territory is FRANCE, numbers are formatted using a comma as the decimal character.

You can alter the NLS_TERRITORY parameter by changing the value in the initialization parameter file and then restarting the instance. The default value of NLS_TERRITORY can be specific to the operating system.

If NLS_LANG is specified in the client environment, then the value of NLS_TERRITORY in the initialization parameter file is overridden at connection time.

The territory can be modified dynamically during the session by specifying the new NLS_TERRITORY value in an ALTER SESSION statement. Modifying NLS_TERRITORY resets all derived NLS session parameters to default values for the new territory.

To change the territory to France during a session, issue the following ALTER SESSION statement:

ALTER SESSION SET NLS_TERRITORY = France;

The following examples show behavior that results from different settings of NLS_TERRITORY and NLS_LANGUAGE.

Example 3-5 NLS_LANGUAGE=AMERICAN, NLS_TERRITORY=AMERICA

Enter the following SELECT statement:

SQL> SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees;

When NLS_TERRITORY is set to AMERICA and NLS_LANGUAGE is set to AMERICAN, results similar to the following should appear:

SALARY
--------------------
$24,000.00
$17,000.00
$17,000.00

Example 3-6 NLS_LANGUAGE=AMERICAN, NLS_TERRITORY=GERMANY

Use an ALTER SESSION statement to change the territory to Germany:

ALTER SESSION SET NLS_TERRITORY = Germany;
Session altered.

Enter the same SELECT statement as before:

SQL> SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees;

You should see results similar to the following:

SALARY
--------------------
€24.000,00
€17.000,00
€17.000,00

Note that the currency symbol has changed from $ to €. The numbers have not changed because the underlying data is the same.

See Also:

"Overriding Default Values for NLS_LANGUAGE and NLS_TERRITORY During a Session" for more information about using the ALTER SESSION statement

Example 3-7 NLS_LANGUAGE=GERMAN, NLS_TERRITORY=GERMANY

Use an ALTER SESSION statement to change the language to German:

ALTER SESSION SET NLS_LANGUAGE = German;
Sitzung wurde geändert.

Note that the server message now appears in German.

Enter the same SELECT statement as before:

SQL> SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees;

You should see the same results as in Example 3-6:

SALARY
--------------------
€24.000,00
€17.000,00
€17.000,00

Example 3-8 NLS_LANGUAGE=GERMAN, NLS_TERRITORY=AMERICA

Use an ALTER SESSION statement to change the territory to America:

ALTER SESSION SET NLS_TERRITORY = America;
Sitzung wurde geändert.

Enter the same SELECT statement as in the other examples:

SQL> SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees;

You should see output similar to the following:

SALARY
--------------------
$24,000.00
$17,000.00
$17,000.00

Note that the currency symbol changed from € to $ because the territory changed from Germany to America.

Overriding Default Values for NLS_LANGUAGE and NLS_TERRITORY During a Session

Default values for NLS_LANGUAGE and NLS_TERRITORY and default values for specific formatting parameters can be overridden during a session by using the ALTER SESSION statement.

Example 3-9 NLS_LANG=ITALIAN_ITALY.WE8DEC

Set the NLS_LANG environment variable so that the language is Italian, the territory is Italy, and the character set is WE8DEC:

% setenv NLS_LANG Italian_Italy.WE8DEC

Enter a SELECT statement:

SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees;

You should see output similar to the following:

LAST_NAME                 HIRE_DATE     SALARY
------------------------- --------- ----------
Sciarra                   30-SET-97      962,5
Urman                     07-MAR-98        975
Popp                      07-DIC-99      862,5

Note the language of the month abbreviations and the decimal character.

Example 3-10 Change Language, Date Format, and Decimal Character

Use ALTER SESSION statements to change the language, the date format, and the decimal character:

SQL> ALTER SESSION SET NLS_LANGUAGE=german;

Session wurde geändert.

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD.MON.YY';

Session wurde geändert.

SQL> ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,';

Session wurde geändert.

Enter the SELECT statement shown in Example 3-9:

SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees;

You should see output similar to the following:

LAST_NAME                 HIRE_DATE     SALARY
------------------------- --------- ----------
Sciarra                   30.SEP.97      962.5
Urman                     07.MÄR.98        975
Popp                      07.DEZ.99      862.5

Note that the language of the month abbreviations is German and the decimal character is a period.

The behavior of the NLS_LANG environment variable implicitly determines the language environment of the database for each session. When a session connects to a database, an ALTER SESSION statement is automatically executed to set the values of the database parameters NLS_LANGUAGE and NLS_TERRITORY to those specified by the language and territory arguments of NLS_LANG. If NLS_LANG is not defined, then no implicit ALTER SESSION statement is executed.

When NLS_LANG is defined, the implicit ALTER SESSION is executed for all instances to which the session connects, for both direct and indirect connections. If the values of NLS parameters are changed explicitly with ALTER SESSION during a session, then the changes are propagated to all instances to which that user session is connected.

Date and Time Parameters

Oracle enables you to control the display of date and time. This section contains the following topics:

Date Formats

Different date formats are shown in Table 3-4.

Table 3-4 Date Formats  
Country Description Example

Estonia

dd.mm.yyyy

28.02.2003

Germany

dd-mm-rr

28-02-03

Japan

rr-mm-dd

03-02-28

UK

dd-mon-rr

28-Feb-03

US

dd-mon-rr

28-Feb-03

This section includes the following parameters:

NLS_DATE_FORMAT

Property Description

Parameter type

String

Parameter scope

Initialization parameter, environment variable, and ALTER SESSION

Default value

Derived from NLS_TERRITORY

Range of values

Any valid date format mask

The NLS_DATE_FORMAT parameter defines the default date format to use with the TO_CHAR and TO_DATE functions. The NLS_TERRITORY parameter determines the default value of NLS_DATE_FORMAT. The value of NLS_DATE_FORMAT can be any valid date format mask. For example:

NLS_DATE_FORMAT = "MM/DD/YYYY"

To add string literals to the date format, enclose the string literal with double quotes. Note that when double quotes are included in the date format, the entire value must be enclosed by single quotes. For example:

NLS_DATE_FORMAT = '"Date: "MM/DD/YYYY'

Example 3-11 Setting the Date Format to Display Roman Numerals

To set the default date format to display Roman numerals for the month, include the following line in the initialization parameter file:

NLS_DATE_FORMAT = "DD RM YYYY"

Enter the following SELECT statement:

SELECT TO_CHAR(SYSDATE) currdate FROM dual;

You should see the following output if today's date is February 12, 1997:

CURRDATE
---------
12 II 1997

The value of NLS_DATE_FORMAT is stored in the internal date format. Each format element occupies two bytes, and each string occupies the number of bytes in the string plus a terminator byte. Also, the entire format mask has a two-byte terminator. For example, "MM/DD/YY" occupies 14 bytes internally because there are three format elements (month, day, and year), two 3-byte strings (the two slashes), and the two-byte terminator for the format mask. The format for the value of NLS_DATE_FORMAT cannot exceed 24 bytes.

You can alter the default value of NLS_DATE_FORMAT by:

If a table or index is partitioned on a date column, and if the date format specified by NLS_DATE_FORMAT does not specify the first two digits of the year, then you must use the TO_DATE function with a 4-character format mask for the year.

For example:

TO_DATE('11-jan-1997', 'dd-mon-yyyy')
See Also:

Oracle Database SQL Reference for more information about partitioning tables and indexes and using TO_DATE

NLS_DATE_LANGUAGE

Property Description

Parameter type

String

Parameter scope

Initialization parameter, environment variable, ALTER SESSION, and SQL functions

Default value

Derived from NLS_LANGUAGE

Range of values

Any valid language name

The NLS_DATE_LANGUAGE parameter specifies the language for the day and month names produced by the TO_CHAR and TO_DATE functions. NLS_DATE_LANGUAGE overrides the language that is specified implicitly by NLS_LANGUAGE. NLS_DATE_LANGUAGE has the same syntax as the NLS_LANGUAGE parameter, and all supported languages are valid values.

NLS_DATE_LANGUAGE also determines the language used for:

Example 3-12 NLS_DATE_LANGUAGE=FRENCH, Month and Day Names

Set the date language to French:

ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH

Enter a SELECT statement:

SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') FROM dual;

You should see output similar to the following:

TO_CHAR(SYSDATE,'DAY:DDMONTHYYYY')
------------------------------------------------------------
Vendredi:07 Décembre  2001

When numbers are spelled in words using the TO_CHAR function, the English spelling is always used. For example, enter the following SELECT statement:

SQL> SELECT TO_CHAR(TO_DATE('12-Oct-2001'),'Day: ddspth Month') FROM dual;

You should see output similar to the following:

TO_CHAR(TO_DATE('12-OCT-2001'),'DAY:DDSPTHMONTH')
--------------------------------------------------------------------
Vendredi: twelfth Octobre

Example 3-13 NLS_DATE_LANGUAGE=FRENCH, Month and Day Abbreviations

Month and day abbreviations are determined by NLS_DATE_LANGUAGE. Enter the following SELECT statement:

SELECT TO_CHAR(SYSDATE, 'Dy:dd Mon yyyy') FROM dual;

You should see output similar to the following:

TO_CHAR(SYSDATE,'DY:DDMO
------------------------
Ve:07 Dec 2001

Example 3-14 NLS_DATE_LANGUAGE=FRENCH, Default Date Format

The default date format uses the month abbreviations determined by NLS_DATE_LANGUAGE. For example, if the default date format is DD-MON-YYYY, then insert a date as follows:

INSERT INTO tablename VALUES ('12-Fév-1997');
See Also:

Oracle Database SQL Reference

Time Formats

Different time formats are shown in Table 3-5.

Table 3-5 Time Formats  
Country Description Example

Estonia

hh24:mi:ss

13:50:23

Germany

hh24:mi:ss

13:50:23

Japan

hh24:mi:ss

13:50:23

UK

hh24:mi:ss

13:50:23

US

hh:mi:ssxff am

1:50:23.555 PM

This section contains information about the following parameters:

NLS_TIMESTAMP_FORMAT

Property Description

Parameter type

String

Parameter scope

Initialization parameter, environment variable, and ALTER SESSION

Default value

Derived from NLS_TERRITORY

Range of values

Any valid datetime format mask

NLS_TIMESTAMP_FORMAT defines the default date format for the TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE datatypes. The following example shows a value for NLS_TIMESTAMP_FORMAT:

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

Example 3-15 Timestamp Format

SQL> SELECT TO_TIMESTAMP('11-nov-2000 01:00:00.336', 'dd-mon-yyyy hh:mi:ss.ff')

FROM dual;

You should see output similar to the following:

TO_TIMESTAMP('11-NOV-200001:00:00.336','DD-MON-YYYYHH:MI:SS.FF')
---------------------------------------------------------------------------
11-NOV-00 01:00:00.336000000

You can specify the value of NLS_TIMESTAMP_FORMAT by setting it in the initialization parameter file. You can specify its value for a client as a client environment variable.

You can also alter the value of NLS_TIMESTAMP_FORMAT by:

NLS_TIMESTAMP_TZ_FORMAT

Property Description

Parameter type

String

Parameter scope

Initialization parameter, environment variable, and ALTER SESSION

Default value

Derived from NLS_TERRITORY

Range of values

Any valid datetime format mask

NLS_TIMESTAMP_TZ_FORMAT defines the default date format for the TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE datatypes. It is used with the TO_CHAR and TO_TIMESTAMP_TZ functions.

You can specify the value of NLS_TIMESTAMP_TZ_FORMAT by setting it in the initialization parameter file. You can specify its value for a client as a client environment variable.

Example 3-16 Setting NLS_TIMESTAMP_TZ_FORMAT

The format value must be surrounded by quotation marks. For example:

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

The following example of the TO_TIMESTAMP_TZ function uses the format value that was specified for NLS_TIMESTAMP_TZ_FORMAT:

SQL> SELECT TO_TIMESTAMP_TZ('2000-08-20, 05:00:00.55 America/Los_Angeles', 
'yyyy-mm-dd hh:mi:ss.ff TZR') FROM dual;

You should see output similar to the following:

TO_TIMESTAMP_TZ('2000-08-20,05:00:00.55AMERICA/LOS_ANGELES','YYYY-MM-DDHH:M
---------------------------------------------------------------------------
20-AUG-00 05:00:00.550000000 AM AMERICA/LOS_ANGELES

You can change the value of NLS_TIMESTAMP_TZ_FORMAT by:

See Also:

Calendar Definitions

This section includes the following topics:

Calendar Formats

The following calendar information is stored for each territory:

First Day of the Week

Some cultures consider Sunday to be the first day of the week. Others consider Monday to be the first day of the week. A German calendar starts with Monday, as shown in Table 3-6.

Table 3-6 German Calendar Example: March 1998
Mo Di Mi Do Fr Sa So

-

-

-

-

-

-

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

-

-

-

-

-

The first day of the week is determined by the NLS_TERRITORY parameter.

See Also:

"NLS_TERRITORY"

First Calendar Week of the Year

Some countries use week numbers for scheduling, planning, and bookkeeping. Oracle supports this convention. In the ISO standard, the week number can be different from the week number of the calendar year. For example, 1st Jan 1988 is in ISO week number 53 of 1987. An ISO week always starts on a Monday and ends on a Sunday.

To support the ISO standard, Oracle provides the IW date format element. It returns the ISO week number.

Table 3-7 shows an example in which January 1 occurs in a week that has four or more days in the first calendar week of the year. The week containing January 1 is the first ISO week of 1998.

Table 3-7 First ISO Week of the Year: Example 1, January 1998  
Mo Tu We Th Fr Sa Su ISO Week

-

-

-

1

2

3

4

First ISO week of 1998

5

6

7

8

9

10

11

Second ISO week of 1998

12

13

14

15

16

17

18

Third ISO week of 1998

19

20

21

22

23

24

25

Fourth ISO week of 1998

26

27

28

29

30

31

-

Fifth ISO week of 1998

Table 3-8 shows an example in which January 1 occurs in a week that has three or fewer days in the first calendar week of the year. The week containing January 1 is the 53rd ISO week of 1998, and the following week is the first ISO week of 1999.

Table 3-8 First ISO Week of the Year: Example 2, January 1999  
Mo Tu We Th Fr Sa Su ISO Week

-

-

-

-

1

2

3

Fifty-third ISO week of 1998

4

5

6

7

8

9

10

First ISO week of 1999

11

12

13

14

15

16

17

Second ISO week of 1999

18

19

20

21

22

23

24

Third ISO week of 1999

25

26

27

28

29

30

31

Fourth ISO week of 1999

The first calendar week of the year is determined by the NLS_TERRITORY parameter.

See Also:

"NLS_TERRITORY"

Number of Days and Months in a Year

Oracle supports six calendar systems in addition to Gregorian, the default:

The calendar system is specified by the NLS_CALENDAR parameter.

See Also:

"NLS_CALENDAR"

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. It should be noted, however, that the Gregorian system is also widely understood in Japan, so both 98 and Heisei 10 can be used to represent 1998.

NLS_CALENDAR

Property Description

Parameter type

String

Parameter scope

Initialization parameter, environment variable, ALTER SESSION, and SQL functions

Default value

Gregorian

Range of values

Any valid calendar format name

Many different calendar systems are in use throughout the world. NLS_CALENDAR specifies which calendar system Oracle uses.

NLS_CALENDAR can have one of the following values:

Example 3-17 NLS_CALENDAR='English Hijrah'

Set NLS_CALENDAR to English Hijrah.

SQL> ALTER SESSION SET NLS_CALENDAR='English Hijrah';

Enter a SELECT statement to display SYSDATE:

SELECT SYSDATE FROM dual;

You should see output similar to the following:

SYSDATE
--------------------
24 Ramadan     1422

Numeric and List Parameters

This section includes the following topics:

Numeric Formats

The database must know the number-formatting convention used in each session to interpret numeric strings correctly. For example, the database needs to know whether numbers are entered with a period or a comma as the decimal character (234.00 or 234,00). Similarly, applications must be able to display numeric information in the format expected at the client site.

Examples of numeric formats are shown in Table 3-9.

Table 3-9 Examples of Numeric Formats  
Country Numeric Formats

Estonia

1 234 567,89

Germany

1.234.567,89

Japan

1,234,567.89

UK

1,234,567.89

US

1,234,567.89

Numeric formats are derived from the setting of the NLS_TERRITORY parameter, but they can be overridden by the NLS_NUMERIC_CHARACTERS parameter.

See Also:

"NLS_TERRITORY"

NLS_NUMERIC_CHARACTERS

Property Description

Parameter type

String

Parameter scope

Initialization parameter, environment variable, ALTER SESSION, and SQL functions

Default value

Default decimal character and group separator for a particular territory

Range of values

Any two valid numeric characters

This parameter specifies the decimal character and group separator. The group separator is the character that separates integer groups to show thousands and millions, for example. The group separator is the character returned by the G number format mask. The decimal character separates the integer and decimal parts of a number. Setting NLS_NUMERIC_CHARACTERS overrides the values derived from the setting of NLS_TERRITORY.

Any character can be the decimal character or group separator. The two characters specified must be single-byte, and the characters must be different from each other. The characters cannot be any numeric character or any of the following characters: plus (+), hyphen (-), less than sign (<), greater than sign (>). Either character can be a space.

Example 3-18 Setting NLS_NUMERIC_CHARACTERS

To set the decimal character to a comma and the grouping separator to a period, define NLS_NUMERIC_CHARACTERS as follows:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",.";

SQL statements can include numbers represented as numeric or text literals. Numeric literals are not enclosed in quotes. They are part of the SQL language syntax and always use a dot as the decimal character and never contain a group separator. Text literals are enclosed in single quotes. They are implicitly or explicitly converted to numbers, if required, according to the current NLS settings.

The following SELECT statement formats the number 4000 with the decimal character and group separator specified in the ALTER SESSION statement:

SELECT TO_CHAR(4000, '9G999D99') FROM dual;

You should see output similar to the following:

TO_CHAR(4
---------
 4.000,00

You can change the default value of NLS_NUMERIC_CHARACTERS by:

NLS_LIST_SEPARATOR

Property Description

Parameter type

String

Parameter scope

Environment variable

Default value

Derived from NLS_TERRITORY

Range of values

Any valid character

NLS_LIST_SEPARATOR specifies the character to use to separate values in a list of values (usually , or . or ; or :). Its default value is derived from the value of NLS_TERRITORY. For example, a list of numbers from 1 to 5 can be expressed as 1,2,3,4,5 or 1.2.3.4.5 or 1;2;3;4;5 or 1:2:3:4:5.

The character specified must be single-byte and cannot be the same as either the numeric or monetary decimal character, any numeric character, or any of the following characters: plus (+), hyphen (-), less than sign (<), greater than sign (>), period (.).

Monetary Parameters

This section includes the following topics:

Currency Formats

Different currency formats are used throughout the world. Some typical ones are shown in Table 3-10.

Table 3-10 Currency Format Examples
Country Example

Estonia

1 234,56 kr

Germany

1.234,56€

Japan

¥1,234.56

UK

£1,234.56

US

$1,234.56

NLS_CURRENCY

Property Description

Parameter type

String

Parameter scope

Initialization parameter, environment variable, ALTER SESSION, and SQL functions

Default value

Derived from NLS_TERRITORY

Range of values

Any valid currency symbol string

NLS_CURRENCY specifies the character string returned by the L number format mask, the local currency symbol. Setting NLS_CURRENCY overrides the setting defined implicitly by NLS_TERRITORY.

Example 3-19 Displaying the Local Currency Symbol

Connect to the sample order entry schema:

SQL> connect oe/oe
Connected.

Enter a SELECT statement similar to the following:

SQL> SELECT TO_CHAR(order_total, 'L099G999D99') "total" FROM orders 

WHERE order_id > 2450;

You should see output similar to the following:

total
---------------------
          $078,279.60
          $006,653.40
          $014,087.50
          $010,474.60
          $012,589.00
          $000,129.00
          $003,878.40
          $021,586.20

You can change the default value of NLS_CURRENCY by:

NLS_ISO_CURRENCY

Property Description

Parameter type

String

Parameter scope

Initialization parameter, environment variable, ALTER SESSION, and SQL functions

Default value

Derived from NLS_TERRITORY

Range of values

Any valid string

NLS_ISO_CURRENCY specifies the character string returned by the C number format mask, the ISO currency symbol. Setting NLS_ISO_CURRENCY overrides the value defined implicitly by NLS_TERRITORY.

Local currency symbols can be ambiguous. For example, a dollar sign ($) can refer to US dollars or Australian dollars. ISO specifications define unique currency symbols for specific territories or countries. For example, the ISO currency symbol for the US dollar is USD. The ISO currency symbol for the Australian dollar is AUD.

More ISO currency symbols are shown in Table 3-11.

Table 3-11 ISO Currency Examples  
Country Example

Estonia

1 234 567,89 EEK

Germany

1.234.567,89 EUR

Japan

1,234,567.89 JPY

UK

1,234,567.89 GBP

US

1,234,567.89 USD

NLS_ISO_CURRENCY has the same syntax as the NLS_TERRITORY parameter, and all supported territories are valid values.

Example 3-20 Setting NLS_ISO_CURRENCY

This example assumes that you are connected as oe/oe in the sample schema.

To specify the ISO currency symbol for France, set NLS_ISO_CURRENCY as follows:

ALTER SESSION SET NLS_ISO_CURRENCY = FRANCE;

Enter a SELECT statement:

SQL> SELECT TO_CHAR(order_total, 'C099G999D99') "TOTAL" FROM orders

WHERE customer_id = 146;

You should see output similar to the following:

TOTAL
------------------
EUR017,848.20
EUR027,455.30
EUR029,249.10
EUR013,824.00
EUR000,086.00

You can change the default value of NLS_ISO_CURRENCY by:

NLS_DUAL_CURRENCY

Property Description

Parameter type

String

Parameter scope

Initialization parameter, environmental variable, ALTER SESSION, and SQL functions

Default value

Derived from NLS_TERRITORY

Range of values

Any valid symbol

Use NLS_DUAL_CURRENCY to override the default dual currency symbol defined implicitly by NLS_TERRITORY.

NLS_DUAL_CURRENCY was introduced to support the euro currency symbol during the euro transition period. Table 3-12 lists the character sets that support the euro symbol.

Table 3-12 Character Sets that Support the Euro Symbol  
Character Set Name Description Hexadecimal Code Value of the Euro Symbol

D8EBCDIC1141

EBCDIC Code Page 1141 8-bit Austrian German

9F

DK8EBCDIC1142

EBCDIC Code Page 1142 8-bit Danish

5A

S8EBCDIC1143

EBCDIC Code Page 1143 8-bit Swedish

5A

I8EBCDIC1144

EBCDIC Code Page 1144 8-bit Italian

9F

F8EBCDIC1147

EBCDIC Code Page 1147 8-bit French

9F

WE8PC858

IBM-PC Code Page 858 8-bit West European

DF

WE8ISO8859P15

ISO 8859-15 West European

A4

EE8MSWIN1250

MS Windows Code Page 1250 8-bit East European

80

CL8MSWIN1251

MS Windows Code Page 1251 8-bit Latin/Cyrillic

88

WE8MSWIN1252

MS Windows Code Page 1252 8-bit West European

80

EL8MSWIN1253

MS Windows Code Page 1253 8-bit Latin/Greek

80

WE8EBCDIC1047E

Latin 1/Open Systems 1047

9F

WE8EBCDIC1140

EBCDIC Code Page 1140 8-bit West European

9F

WE8EBCDIC1140C

EBCDIC Code Page 1140 Client 8-bit West European

9F

WE8EBCDIC1145

EBCDIC Code Page 1145 8-bit West European

9F

WE8EBCDIC1146

EBCDIC Code Page 1146 8-bit West European

9F

WE8EBCDIC1148

EBCDIC Code Page 1148 8-bit West European

9F

WE8EBCDIC1148C

EBCDIC Code Page 1148 Client 8-bit West European

9F

EL8ISO8859P7

ISO 8859-7 Latin/Greek

A4

IW8MSWIN1255

MS Windows Code Page 1255 8-bit Latin/Hebrew

80

AR8MSWIN1256

MS Windows Code Page 1256 8-Bit Latin/Arabic

80

TR8MSWIN1254

MS Windows Code Page 1254 8-bit Turkish

80

BLT8MSWIN1257

MS Windows Code Page 1257 Baltic

80

VN8MSWIN1258

MS Windows Code Page 1258 8-bit Vietnamese

80

TH8TISASCII

Thai Industrial 620-2533 - ASCII 8-bit

80

AL32UTF8

Unicode 3.2 UTF-8 Universal character set

E282AC

UTF8

CESU-8

E282AC

AL16UTF16

Unicode 3.2 UTF-16 Universal character set

20AC

UTFE

UTF-EBCDIC encoding of Unicode 3.0

CA4653

ZHT16HKSCS

MS Windows Code Page 950 with Hong Kong Supplementary Character Set

A3E1

ZHS32GB18030

GB18030-2000

A2E3

WE8BS2000E

Siemens EBCDIC.DF.04 8-bit West European

9F

Oracle Support for the Euro

Twelve members of the European Monetary Union (EMU) have adopted the euro as their currency. Setting NLS_TERRITORY to correspond to a country in the EMU (Austria, Belgium, Finland, France, Germany, Greece, Ireland, Italy, Luxembourg, the Netherlands, Portugal, and Spain) results in the default values for NLS_CURRENCY and NLS_DUAL_CURRENCY being set to EUR.

During the transition period (1999 through 2001), Oracle support for the euro was provided in Oracle8i and later as follows:

Beginning with Oracle9i release 2 (9.2), the value of NLS_ISO_CURRENCY results in the ISO currency symbol being set to EUR for EMU member countries who use the euro. For example, suppose NLS_ISO_CURRENCY is set to FRANCE. Enter the following SELECT statement:

SELECT TO_CHAR(TOTAL, 'C099G999D99') "TOTAL" FROM orders WHERE customer_id=585;

You should see output similar to the following:

TOTAL
-------
EUR12.673,49

Customers who must retain their obsolete local currency symbol can override the default for NLS_DUAL_CURRENCY or NLS_CURRENCY by defining them as parameters in the initialization file on the server and as environment variables on the client.


Note:

NLS_LANG must also be set on the client for NLS_CURRENCY or NLS_DUAL_CURRENCY to take effect.


It is not possible to override the ISO currency symbol that results from the value of NLS_ISO_CURRENCY.

NLS_MONETARY_CHARACTERS

Property Description

Parameter type

String

Parameter scope

Environment variable

Default value

Derived from NLS_TERRITORY

Range of values

Any valid character

NLS_MONETARY_CHARACTERS specifies the character that separates groups of numbers in monetary expressions. For example, when the territory is America, the thousands separator is a comma, and the decimal separator is a period.

NLS_CREDIT

Property Description

Parameter type

String

Parameter scope

Environment variable

Default value

Derived from NLS_TERRITORY

Range of values

Any string, maximum of 9 bytes (not including null)

NLS_CREDIT sets the symbol that displays a credit in financial reports. The default value of this parameter is determined by NLS_TERRITORY. For example, a space is a valid value of NLS_CREDIT.

This parameter can be specified only in the client environment.

It can be retrieved through the OCIGetNlsInfo() function.

NLS_DEBIT

Property Description

Parameter type

String

Parameter scope

Environment variable

Default value

Derived from NLS_TERRITORY

Range of values

Any string, maximum or 9 bytes (not including null)

NLS_DEBIT sets the symbol that displays a debit in financial reports. The default value of this parameter is determined by NLS_TERRITORY. For example, a minus sign (-) is a valid value of NLS_DEBIT.

This parameter can be specified only in the client environment.

It can be retrieved through the OCIGetNlsInfo() function.

Linguistic Sort Parameters

You can choose how to sort data by using linguistic sort parameters.

This section includes the following topics:

NLS_SORT

Property Description

Parameter type

String

Parameter scope

Initialization parameter, environment variable, ALTER SESSION, and SQL functions

Default value

Derived from NLS_LANGUAGE

Range of values

BINARY or any valid linguistic sort name

NLS_SORT specifies the type of sort for character data. It overrides the default value that is derived from NLS_LANGUAGE.

The syntax of NLS_SORT is:

NLS_SORT = BINARY | sort_name

BINARY specifies a binary sort. sort_name specifies a linguistic sort sequence.

The value of NLS_SORT affects the following SQL operations: WHERE, START WITH, IN/OUT, BETWEEN, CASE WHEN, HAVING, ORDER BY. All other SQL operators make comparisons in binary mode only.

Example 3-21 Setting NLS_SORT

To specify the German linguistic sort sequence, set NLS_SORT as follows:

NLS_SORT = German

Note:

When the NLS_SORT parameter is set to BINARY, the optimizer can, in some cases, satisfy the ORDER BY clause without doing a sort by choosing an index scan.

When NLS_SORT is set to a linguistic sort, a sort is needed to satisfy the ORDER BY clause if there is no linguistic index for the linguistic sort specified by NLS_SORT.

If a linguistic index exists for the linguistic sort specified by NLS_SORT, then the optimizer can, in some cases, satisfy the ORDER BY clause without doing a sort by choosing an index scan.


You can alter the default value of NLS_SORT by doing one of the following:

NLS_COMP

Property Description

Parameter type

String

Parameter scope

Initialization parameter, environment variable, and ALTER SESSION

Default value

BINARY

Range of values

BINARY or ANSI

The value of NLS_COMP affects the following SQL operations: WHERE, START WITH, IN/OUT, BETWEEN, CASE WHEN, HAVING, ORDER BY. All other SQL operators make comparisons in binary mode only.

You can use NLS_COMP to avoid the cumbersome process of using the NLSSORT function in SQL statements when you want to perform a linguistic comparison instead of a binary comparison. When NLS_COMP is set to ANSI, SQL operations perform a linguistic comparison based on the value of NLS_SORT.

Set NLS_COMP to ANSI as follows:

ALTER SESSION SET NLS_COMP = ANSI;

When NLS_COMP is set to ANSI, a linguistic index improves the performance of the linguistic comparison.

To enable a linguistic index, use the following syntax:

CREATE INDEX i ON t(NLSSORT(col, 'NLS_SORT=FRENCH'));
See Also:

Character Set Conversion Parameter

This section includes the following topic:

NLS_NCHAR_CONV_EXCP

Property Description

Parameter type

String

Parameter scope

Initialization parameter, ALTER SESSION, ALTER SYSTEM

Default value

FALSE

Range of values

TRUE or FALSE

NLS_NCHAR_CONV_EXCP determines whether an error is reported when there is data loss during an implicit or explicit character type conversion between NCHAR/NVARCHAR data and CHAR/VARCHAR2 data. The default value results in no error being reported.

See Also:

Chapter 11, "Character Set Migration" for more information about data loss during character set conversion

Length Semantics

This section includes the following topic:

NLS_LENGTH_SEMANTICS

Property Description

Parameter type

String

Parameter scope

Environment variable, initialization parameter, ALTER SESSION, and ALTER SYSTEM

Default value

BYTE

Range of values

BYTE or CHAR

By default, the character datatypes CHAR and VARCHAR2 are specified in bytes, not characters. Hence, the specification CHAR(20) in a table definition allows 20 bytes for storing character data.

This works well if the database character set uses a single-byte character encoding scheme because the number of characters is the same as the number of bytes. If the database character set uses a multibyte character encoding scheme, then the number of bytes no longer equals the number of characters because a character can consist of one or more bytes. Thus, column widths must be chosen with care to allow for the maximum possible number of bytes for a given number of characters. You can overcome this problem by switching to character semantics when defining the column size.

NLS_LENGTH_SEMANTICS enables you to create CHAR, VARCHAR2, and LONG columns using either byte or character length semantics. NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. Existing columns are not affected.

You may be required to use byte semantics in order to maintain compatibility with existing applications.

NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary always uses byte semantics.

Note that if the NLS_LENGTH_SEMANTICS environment variable is not set on the client, then the client session defaults to the value for NLS_LENGTH_SEMANTICS on the database server. This enables all client sessions on the network to have the same NLS_LENGTH_SEMANTICS behavior. Setting the environment variable on an individual client enables the server initialization parameter to be overridden for that client.

See Also: