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 the default value of NLS_NUMERIC_CHARACTERS by:

Changing the value of NLS_NUMERIC_CHARACTERS in the initialization parameter file and then restart the instance
Using the ALTER SESSION statement to change the parameter's value during a session

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

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

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

Parameter type:	String
Parameter scope:	Initialization Parameter, Environment Variable, and `ALTER` `SESSION`
Default value:	Default local currency symbol for a particular 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 schema 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:

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

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

NLS_ISO_CURRENCY

Parameter type:	String
Parameter scope:	Initialization Parameter, Environment Variable, and `ALTER` `SESSION`
Default value:	Derived from `NLS_TERRITORY`
Range of values:	Any valid territory name

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. For the Australian dollar, it is AUD.

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

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

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

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

NLS_DUAL_CURRENCY

Parameter type:	String
Parameter scope:	Initialization Parameter, Environment Variable, and `ALTER` `SESSION`
Default value:	Default dual currency symbol for a particular territory
Range of values:	Any valid name

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-11 lists the character sets that support the euro symbol.

Table 3-11 Character Sets that Support the Euro Symbol

Character Set Name	Description	Code Value of the Euro Symbol
D8EBCDIC1141	EBCDIC Code Page 1141 8-bit Austrian German	0x9F
DK8EBCDIC1142	EBCDIC Code Page 1142 8-bit Danish	0x5A
S8EBCDIC1143	EBCDIC Code Page 1143 8-bit Swedish	0x5A
I8EBCDIC1144	EBCDIC Code Page 1144 8-bit Italian	0x9F
F8EBCDIC1147	EBCDIC Code Page 1147 8-bit French	0x9F
WE8PC858	IBM-PC Code Page 858 8-bit West European	0xDF
WE8ISO8859P15	ISO 8859-15 West European	0xA4
EE8MSWIN1250	MS Windows Code Page 1250 8-bit East European	0x80
CL8MSWIN1251	MS Windows Code Page 1251 8-bit Latin/Cyrillic	0x88
WE8MSWIN1252	MS Windows Code Page 1252 8-bit West European	0x80
EL8MSWIN1253	MS Windows Code Page 1253 8-bit Latin/Greek	0x80
WE8EBCDIC1047E	Latin 1/Open Systems 1047	0x9F
WE8EBCDIC1140	EBCDIC Code Page 1140 8-bit West European	0x9F
WE8EBCDIC1140C	EBCDIC Code Page 1140 Client 8-bit West European	0x9F
WE8EBCDIC1145	EBCDIC Code Page 1145 8-bit West European	0x9F
WE8EBCDIC1146	EBCDIC Code Page 1146 8-bit West European	0x9F
WE8EBCDIC1148	EBCDIC Code Page 1148 8-bit West European	0x9F
WE8EBCDIC1148C	EBCDIC Code Page 1148 Client 8-bit West European	0x9F
EL8ISO8859P7	ISO 8859-7 Latin/Greek	0xA4
IW8MSWIN1255	MS Windows Code Page 1255 8-bit Latin/Hebrew	0x80
AR8MSWIN1256	MS Windows Code Page 1256 8-Bit Latin/Arabic	0x80
TR8MSWIN1254	MS Windows Code Page 1254 8-bit Turkish	0x80
BLT8MSWIN1257	MS Windows Code Page 1257 Baltic	0x80
VN8MSWIN1258	MS Windows Code Page 1258 8-bit Vietnamese	0x80
TH8TISASCII	Thai Industrial 620-2533 - ASCII 8-bit	0x80
AL32UTF8	Unicode 3.1 UTF-8 Universal character set	E282AC
UTF8	Unicode 3.0 UTF-8 Universal character set	E282AC
AL16UTF16	Unicode 3.1 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	0xA3E1
ZHS32GB18030	GB18030-2000	0xA2E3
WE8BS2000E	Siemens EBCDIC.DF.04 8-bit West European	0x9F

Oracle Support for the Euro

The members of the European Monetary Union (EMU) now use the euro as their currency as of January 1, 2002. 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:

NLS_CURRENCY was defined as the primary currency of the country
NLS_ISO_CURRENCY was defined as the ISO currency code of a given territory
NLS_DUAL_CURRENCY was defined as the secondary currency symbol (usually the euro) for a given territory

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

Parameter type:	String
Parameter scope:	Environment Variable
Default value:	Derived from `NLS_TERRITORY`
Range of values:	Any valid name

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

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

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_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
NLS_COMP
NLS_LIST_SEPARATOR

See Also:
Chapter 4, "Linguistic Sorting"

NLS_SORT

Parameter type:	String
Parameter scope:	Initialization Parameter, Environment Variable, and `ALTER` `SESSION`
Default value:	Default character sort sequence for a particular language
Range of values:	`BINARY` or any valid linguistic definition name

NLS_SORT specifies the type of sort for character data. It overrides the value that is defined implicitly by 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.

Example 3-21 Setting NLS_SORT

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

NLS_SORT = German

The name given to a linguistic sort sequence has no direct connection to language names. Usually, however, each supported language has an appropriate linguistic sort sequence that uses the same name. Oracle offers two kinds of linguistic sorts: monolingual and multilingual. In addition monolingual sorts can be extended to handle special cases. Extended monolingual sorts usually sort characters differently than the ASCII values of the characters. For example, ch and ll are treated as only one character in XSPANISH, the extended Spanish sort. In other words, the SPANISH sort uses modern Spanish collation rules, while XSPANISH uses traditional Spanish sorting rules.

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

Changing its value in the initialization parameter file and then restarting the instance

Using an ALTER SESSION statement

See Also:

"Multilingual Linguistic Sorts"
Oracle9i SQL Reference for more information about the ALTER SESSION statement
Appendix A, "Locale Data" for a complete list of linguistic sort definitions

NLS_COMP

Parameter type:	String
Parameter scope:	Initialization Parameter, Environment Variable and `ALTER` `SESSION`
Default value:	Binary
Range of values:	`BINARY` or `ANSI`

You can use NLS_COMP to avoid the cumbersome process of using NLS_SORT in SQL statements. Normally, comparison in the WHERE clause and in PL/SQL blocks is binary. To use linguistic comparison, you must use the NLSSORT SQL function. Sometimes this can be tedious, especially when the linguistic sort has already been specified in the NLS_SORT session parameter. You can use NLS_COMP to indicate that the comparisons must be linguistic according to the NLS_SORT session parameter. Do this by altering the session:

ALTER SESSION SET NLS_COMP = ANSI;

To specify that comparison in the WHERE clause is always binary, issue the following statement:

ALTER SESSION SET NLS_COMP = BINARY;

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:

"Using Linguistic Indexes"

NLS_LIST_SEPARATOR

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. Its default value is derived from the value of NLS_TERRITORY.

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 (.).

Character Set Conversion Parameter

This section includes the following topic:

NLS_NCHAR_CONV_EXCP

NLS_NCHAR_CONV_EXCP

Parameter type:	String
Parameter scope:	Initialization Parameter, `ALTER` `SESSION`, `ALTER` `SYSTEM`
Default value:	`FALSE`
Range of values:	`TRUE,` `FALSE`

NLS_NCHAR_CONV_EXCP determines whether an error is reported when there is data loss during an implicit or explicit character type conversion. The default value results in no error being reported.

See Also:

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

Length Semantics

This section includes the following topic:

NLS_LENGTH_SEMANTICS

NLS_LENGTH_SEMANTICS

Parameter type:	String
Parameter scope:	Dynamic, Initialization Parameter, `ALTER` `SESSION`, and `ALTER` `SYSTEM`
Default value:	`BYTE`
Range of values:	`BYTE` \| `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 will be 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.

See Also:

"Length Semantics"
Oracle9i Database Concepts for more information about length semantics

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

Monetary Parameters

Currency Formats

Table 3-9 Currency Format Examples

NLS_CURRENCY

Example 3-19 Displaying the Local Currency Symbol

NLS_ISO_CURRENCY

Table 3-10 ISO Currency Examples

Example 3-20 Setting NLS_ISO_CURRENCY

NLS_DUAL_CURRENCY

Table 3-11 Character Sets that Support the Euro Symbol

Oracle Support for the Euro

NLS_MONETARY_CHARACTERS

NLS_CREDIT

NLS_DEBIT

Linguistic Sort Parameters

NLS_SORT

Example 3-21 Setting NLS_SORT

NLS_COMP

NLS_LIST_SEPARATOR

Character Set Conversion Parameter

NLS_NCHAR_CONV_EXCP

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