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:

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 parameters 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:
Oracle9i 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. Doing so will override 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:
Oracle9i 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. Another example is that setting an NLS parameter within a SQL function overrides all other methods of setting NLS parameters.

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 NLS parameters available with the Oracle server.

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. It sets the language and territory used by the client application. It also sets the client's character set, which is the character set for data entered or displayed by a client program.

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 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 that of the user's terminal). 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 left out will default. If you specify territory or character set, you must include the preceding delimiter [underscore (_) for territory, period (.) for character set]. Otherwise, the value will be parsed as a language name.

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

NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252

NLS_LANG = FRENCH_CANADA.WE8DEC

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

Because the WE8DEC character set does not support any Japanese characters, you cannot store 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

See Also:
Appendix A, "Locale Data" for a complete list of supported languages, territories, and character sets

Specifying the Value of NLS_LANG

Set NLS_LANG as an environment variable at the command line. For example, in the UNIX operating system, specify the value of NLS_LANG by entering a statement similar to the following:

% setenv NLS_LANG FRENCH_FRANCE.WE8DEC

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:

Should the NLS_LANG Setting Match the Database Character Set?

The NLS_LANG character set should reflect the setting of the operating system client. For example, if the database character set is UTF8 and the client has a Windows operating system, you should not set UTF8 as the client character set because there are no UTF8 WIN32 clients. Instead the NLS_LANG setting should reflect the code page of the client.

NLS_LANG is set as a local environment variable on UNIX platforms.

NLS_LANG is set in the registry on Windows platforms. For example, on an English Windows client, the code page is WE8MSWIN1252. An appropriate setting for NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252.

Setting NLS_LANG correctly allows proper conversion from the client operating system code page 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:

Oracle9i 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 database environment 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_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 that were used when the database was created.

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:
Oracle9i Database Reference

OCINlsGetInfo() Function

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

See Also:

Chapter 8, "OCI Programming in a Global Environment" for the description of OCINlsGetInfo()

Language and Territory Parameters

This section contains information about the following parameters:

NLS_LANGUAGE

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

When the language is French, the server message

ORA-00942:  table or view does not exist

appears as

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 F is the language abbreviation for French.

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 will then be converted to the client character set if the client character set is different from the database character set. Hence, messages will be displayed correctly at the user's terminal, subject to the limitations of character set conversion.

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:

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

NLS 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

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.

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 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 the language of the month abbreviations, the date format, and the decimal character.

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, 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-3.

Table 3-3 Date Formats

Country	Description	Example
Estonia	dd.mm.yyyy	28.02.1998
Germany	dd-mm-rr	28-02-98
Japan	rr-mm-dd	98-02-28
UK	dd-mon-rr	28-Feb-98
US	dd-mon-rr	28-Feb-98

This section includes the following parameters:

NLS_DATE_FORMAT

Parameter type:	String
Parameter scope:	Initialization Parameter, Environment Variable, and `ALTER` `SESSION`
Default value:	Default format for a particular 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. The value must be surrounded by quotation marks. 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 every special character (such as the double quote) must be preceded with an escape character. The entire expression must be surrounded with single quotes. For example:

NLS_DATE_FORMAT = '\"Today\'s 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 12 bytes internally because there are three format elements (month, day, and year), two one-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.

Note:

The applications you design may need to allow for a variable-length default date format. Also, the parameter value must be surrounded by double quotes. Single quotes are interpreted as part of the format mask.

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:
Oracle9i SQL ReferenceOracle9i 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')

See Also:

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

NLS_DATE_LANGUAGE

Parameter type:	String
Parameter scope:	Initialization Parameter, Environment Variable, and `ALTER` `SESSION`
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 SESSIONS 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:

Oracle9i SQL Reference

Time Formats

Different time formats are shown in Table 3-4.

Table 3-4 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

Parameter type:	String
Parameter scope:	Dynamic, 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 timestamp format to use with TO_CHAR and TO_TIMESTAMP functions. The value must be surrounded by quotation marks as follows

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:
Oracle9i SQL Reference for more information about the TO_TIMESTAMP function and the ALTER SESSION statement

NLS_TIMESTAMP_TZ_FORMAT

Parameter type:	String
Parameter scope:	Dynamic, 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 format for the timestamp with time zone. 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.44AMERICA/LOS_ANGELES','YYYY-MM-DDHH:M
---------------------------------------------------------------------------
20-AOU-00 05:00:00.440000000 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:
Oracle9i SQL Reference for more information about the TO_TIMESTAMP_TZ function and the ALTER SESSION statement

Time Zone Parameters for Databases

You can create a database with a specific time zone by specifying:

A displacement from UTC (Coordinated Universal Time, formerly Greenwich Mean Time). The following example sets the time zone of the database to Pacific Standard time (eight hours behind UTC):
```
CREATE DATABASE ... SET TIME_ZONE = '-08:00 ';
```
A time zone region. The following example also sets the time zone of the database to Pacific Standard time in the United States:
```
CREATE DATABASE ... SET TIME_ZONE = 'PST '; 

To see a list of valid region names, query the V$TIMEZONE_NAMES view.
```

The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns. Oracle normalizes all TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database when the data is stored on disk. If you do not specify the SET TIME_ZONE clause, then Oracle uses the time zone of the operating system of the server. If the operating system's time zone is not a valid Oracle time zone, then the rdatabase time zone defaults to UTC. Oracle's time zone information is derived from the public domain time zone data available at ftp://elsie.nci.nih.gov/pub/. Oracle's time zone information may not reflect the most recent time zone data available from this site.

After the database has been created, you can change the time zone by issuing the ALTER DATABASE SET TIME_ZONE statement and then shutting down and starting up the database. The following example sets the time zone of the database to London time:

ALTER DATABASE SET TIME_ZONE = 'Europe/London ';

To find out the time zone of a database, use the DBTIMEZONE function as shown in the following example:

SELECT dbtimezone FROM dual;

DBTIME
-------
-08:00

Time Zone Parameters for Sessions

You can change the time zone parameter of a user session by issuing an ALTER SESSION statement:

Operating system local time zone
```
ALTER SESSION SET TIME_ZONE = local;
```

Database time zone

ALTER SESSION SET TIME_ZONE = DBTIMEZONE;

An absolute time difference from UTC

ALTER SESSION SET TIME_ZONE = '-05:00';

Time zone for a named region

ALTER SESSION SET TIME_ZONE = 'America/New_York';

You can use the ORA_SDTZ environment variable to set the default client session time zone. This variable takes input like DB_TZ, OS_TZ, time zone region, or numerical time zone offset. If ORA_SDTZ is set to DB_TZ, then the session time zone will be the same as the database time zone. If it is set to OS_TZ, then the session time zone will be same as the operating system's time zone.If ORA_SDTZ is set to an invalid Oracle time zone, then Oracle uses the operating system's time zone as default session time zone. If the operating system's time zone is not a valid Oracle time zone, then the session time zone defaults to UTC. To find out the time zone of a user session, use the SESSIONTIMEZONE function as shown in the following example:

SELECT sessiontimezone FROM dual;

SESSIONTIMEZONE 
---------------
         -08:00

See Also:

"Customizing Time Zone Data"

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

Table 3-5 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.

If January 1 falls on a Friday, Saturday, or Sunday, then the ISO week that includes January 1 is the last week of the previous year, because most of the days in the week belong to the previous year.
If January 1 falls on a Monday, Tuesday, Wednesday, or Thursday, then the ISO week is the first week of the new year, because most of the days in the week belong to the new year.

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

Table 3-6 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-6 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-7 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-7 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:

Japanese Imperial--uses the same number of months and days as Gregorian, but the year starts with the beginning of each Imperial Era
ROC Official--uses the same number of months and days as Gregorian, but the year starts with the founding of the Republic of China
Persian--has 31 days for each of the first six months. The next five months have 30 days each. The last month has either 29 days or 30 days (leap year).
Thai Buddha--uses a Buddhist calendar
Arabic Hijrah--has 12 months with 354 or 355 days
English Hijrah--has 12 months with 354 or 355 days

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

Parameter type:	String
Parameter scope:	Initialization Parameter, Environment Variable, and `ALTER` `SESSION`
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:

Arabic Hijrah
English Hijrah
Gregorian
Japanese Imperial
Persian
ROC Official (Republic of China)
Thai Buddha

See Also:
Appendix A, "Locale Data" for a list of calendar systems, their default date formats, and the character sets in which dates are displayed

Example 3-17 NLS_CALENDAR='Japanese Imperial"

Set NLS_CALENDAR to Japanese Imperial:

SQL> ALTER SESSIONS 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 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-8.

Table 3-8 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

Parameter type:	String
Parameter scope:	Initialization Parameter, Environment Variable, and `ALTER` `SESSION`
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 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.

The characters are specified in the following format:

NLS_NUMERIC_CHARACTERS = "decimal_character group_separator"

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 = ",.";

Both characters are single-byte and are different from each other.

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

Enter a SELECT 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 t

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

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

3 Setting Up a Globalization Support Environment

Setting NLS Parameters

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

Table 3-2 NLS Parameters

Choosing a Locale with the NLS_LANG Environment Variable

Specifying the Value of NLS_LANG

Example 3-1 Setting NLS_LANG to American_America.WE8ISO8859P1

Example 3-2 Setting NLS_LANG to French_France.WE8ISO8859P1

Overriding Language and Territory Specifications

Should the NLS_LANG Setting Match the Database Character Set?

NLS Database Parameters

NLS Data Dictionary Views

NLS Dynamic Performance Views

OCINlsGetInfo() Function

Language and Territory Parameters

NLS_LANGUAGE

Example 3-3 NLS_LANGUAGE=ITALIAN

Example 3-4 NLS_LANGUAGE=GERMAN

NLS_TERRITORY

Example 3-5 NLS_LANGUAGE=AMERICAN, NLS_TERRITORY=AMERICA

Example 3-6 NLS_LANGUAGE=AMERICAN, NLS_TERRITORY=GERMANY

Example 3-7 NLS_LANGUAGE=GERMAN, NLS_TERRITORY=GERMANY

Example 3-8 NLS_LANGUAGE=GERMAN, NLS_TERRITORY=AMERICA

Overriding Default Values for NLS_LANGUAGE and NLS_TERRITORY During a Session

Example 3-9 NLS_LANG=ITALIAN_ITALY.WE8DEC

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

Date and Time Parameters

Date Formats

Table 3-3 Date Formats

NLS_DATE_FORMAT

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

NLS_DATE_LANGUAGE

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

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

Example 3-14 NLS_DATE_LANGUAGE=FRENCH, Default Date Format

Time Formats

Table 3-4 Time Formats

NLS_TIMESTAMP_FORMAT

Example 3-15 Timestamp Format

NLS_TIMESTAMP_TZ_FORMAT

Example 3-16 Setting NLS_TIMESTAMP_TZ_FORMAT

Time Zone Parameters for Databases

Time Zone Parameters for Sessions

Calendar Definitions

Calendar Formats

First Day of the Week

Table 3-5 German Calendar Example: March 1998

First Calendar Week of the Year

Table 3-6 First ISO Week of the Year: Example 1, January 1998

Table 3-7 First ISO Week of the Year: Example 2, January 1999

Number of Days and Months in a Year

First Year of Era

NLS_CALENDAR

Example 3-17 NLS_CALENDAR='Japanese Imperial"

Numeric Parameters

Numeric Formats

Table 3-8 Examples of Numeric Formats

NLS_NUMERIC_CHARACTERS

Example 3-18 Setting NLS_NUMERIC_CHARACTERS

3
Setting Up a Globalization Support Environment

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