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
• Choosing a Locale with the NLS_LANG Environment Variable
• NLS Database Parameters
• Language and Territory Parameters
• Date and Time Parameters
• Calendar Definitions
• Numeric and List Parameters
• Monetary Parameters
• Linguistic Sort Parameters
• Character Set Conversion Parameter
• Length Semantics

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:

• As initialization parameters on the server

  You can include parameters in the initialization parameter file to specify a default session NLS environment. These settings have no effect on the client side; they control only the server's behavior. For example:

  NLS_TERRITORY = "CZECH REPUBLIC"
  
  

• As environment variables on the client

  You can use NLS environment variables, which may be platform-dependent, to specify locale-dependent behavior for the client and also to override the default values set for the session in the initialization parameter file. For example, on a UNIX system:

  % setenv NLS_SORT FRENCH
  
  

• With the ALTER SESSION statement

  NLS parameters that are set in an ALTER SESSION statement can be used to override the default values that are set for the session in the initialization parameter file or set by the client with environment variables.

  ALTER SESSION SET NLS_SORT = FRENCH;
  

  See Also: Oracle Database SQL Reference for more information about the ALTER SESSION statement

• In SQL functions

  NLS parameters can be used explicitly to hardcode NLS behavior within a SQL function. This practice overrides the default values that are set for the session in the initialization parameter file, set for the client with environment variables, or set for the session by the ALTER SESSION statement. For example:

  TO_CHAR(hiredate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')
  

  See Also: Oracle Database SQL Reference for more information about SQL functions, including the TO_CHAR function

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:

• language

  Specifies conventions such as the language used for Oracle messages, sorting, day names, and month names. Each supported language has a unique name; for example, AMERICAN, FRENCH, or GERMAN. The language argument specifies default values for the territory and character set arguments. If the language is not specified, then the value defaults to AMERICAN.

• territory

  Specifies conventions such as the default date, monetary, and numeric formats. Each supported territory has a unique name; for example, AMERICA, FRANCE, or CANADA. If the territory is not specified, then the value is derived from the language value.

• charset

  Specifies the character set used by the client application (normally the Oracle character set that corresponds to the user's terminal character set or the OS character set). Each supported character set has a unique acronym, for example, US7ASCII, WE8ISO8859P1, WE8DEC, WE8MSWIN1252, or JA16EUC. Each language has a default character set associated with it.

  Note: All components of the NLS_LANG definition are optional; any item that is not specified uses its default value. If you specify territory or character set, then you must include the preceding delimiter [underscore (_) for territory, period (.) for character set]. Otherwise, the value is parsed as a language name. For example, to set only the territory portion of NLS_LANG, use the following format: NLS_LANG=_JAPAN

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
• Overriding Language and Territory Specifications
• Locale Variants

  See Also: Appendix A, "Locale Data" for a complete list of supported languages, territories, and character sets Your operating system documentation for information about additional globalization settings that may be necessary for your platform

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.

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.

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.

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
• NLS Dynamic Performance Views
• OCINlsGetInfo() Function

NLS Data Dictionary Views

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

• NLS_SESSION_PARAMETERS shows the NLS parameters and their values for the session that is querying the view. It does not show information about the character set.
• NLS_INSTANCE_PARAMETERS shows the current NLS instance parameters that have been explicitly set and the values of the NLS instance parameters.
• NLS_DATABASE_PARAMETERS shows the values of the NLS parameters for the database. The values are stored in the database.

NLS Dynamic Performance Views

Applications can check the following NLS dynamic performance views:

• V$NLS_VALID_VALUES lists values for the following NLS parameters: NLS_LANGUAGE, NLS_SORT, NLS_TERRITORY, NLS_CHARACTERSET
• V$NLS_PARAMETERS shows current values of the following NLS parameters: NLS_CALENDAR, NLS_CHARACTERSET, NLS_CURRENCY, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_ISO_CURRENCY, NLS_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_SORT, NLS_TERRITORY, NLS_NCHAR_CHARACTERSET, NLS_COMP, NLS_LENGTH_SEMANTICS, NLS_NCHAR_CONV_EXP, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_TIME_FORMAT, NLS_TIME_TZ_FORMAT

  See Also: Oracle Database Reference

OCINlsGetInfo() Function

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

Language and Territory Parameters

This section contains information about the following parameters:

• NLS_LANGUAGE
• NLS_TERRITORY

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:

• Language for server messages
• Language for day and month names and their abbreviations (specified in the SQL functions TO_CHAR and TO_DATE)
• Symbols for equivalents of AM, PM, AD, and BC. (A.M., P.M., A.D., and B.C. are valid only if NLS_LANGUAGE is set to AMERICAN.)
• Default sorting sequence for character data when ORDER BY is specified. (GROUP BY uses a binary sort unless ORDER BY is specified.)
• Writing direction
• Affirmative and negative response strings (for example, YES and NO)

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.

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:

• Messages from the server
• Messages and boilerplate text generated by Oracle Forms
• Messages and boilerplate text generated by the application

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.

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:

• Date format
• Decimal character and group separator
• Local currency symbol
• ISO currency symbol
• Dual currency symbol
• First day of the week
• Credit and debit symbols
• ISO week flag
• List separator

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.

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
• Time Formats

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
• NLS_DATE_LANGUAGE

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:

• Changing its value in the initialization parameter file and then restarting the instance
• Using an ALTER SESSION SET NLS_DATE_FORMAT statement

  See Also: Oracle Database SQL Reference for more information about date format elements and the ALTER SESSION statement

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')

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:

• Month and day abbreviations returned by the TO_CHAR and TO_DATE functions
• Month and day abbreviations used by the default date format (NLS_DATE_FORMAT)
• Abbreviations for AM, PM, AD, and BC

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');

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
• NLS_TIMESTAMP_TZ_FORMAT

  See Also: Chapter 4, "Datetime Datatypes and Time Zone Support"

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:

• Changing its value in the initialization parameter file and then restarting the instance
• Using the ALTER SESSION SET NLS_TIMESTAMP_FORMAT statement

  See Also: Oracle Database SQL Reference for more information about the TO_TIMESTAMP function and the ALTER SESSION statement

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:

• Changing its value in the initialization parameter file and then restarting the instance
• Using the ALTER SESSION statement.

See Also: Oracle Database SQL Reference for more information about the TO_TIMESTAMP_TZ function and the ALTER SESSION statement "Choosing a Time Zone File" for more information about time zones

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

SELECT SYSDATE FROM dual;

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

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",.";

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

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

SQL> connect oe/oe
Connected.

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

WHERE order_id > 2450;

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

ALTER SESSION SET NLS_ISO_CURRENCY = FRANCE;

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

WHERE customer_id = 146;

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

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

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

NLS_SORT = BINARY | sort_name

NLS_SORT = German

ALTER SESSION SET NLS_COMP = ANSI;

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