Oracle8i National Language Support Guide
Release 2 (8.1.6)

Part Number A76966-01

Library

Product

Contents

Index

Go to previous page Go to next page

2
Setting Up an NLS Environment

This chapter tells how to set up an NLS environment, and includes the following topics:

Setting NLS Parameters

NLS parameters determine the locale-specific behavior on both the client and the server. There are four ways to specify NLS parameters:

  1. 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"
    
    
  2. As environment variables on the client. You can use NLS parameters to specify locale-dependent behavior for the client, and also override the defaults set for the session in the initialization file. For example, on a UNIX system:

    % setenv NLS_SORT FRENCH
    
    
  3. As ALTER SESSION parameters. NLS parameters set in an ALTER SESSION statement can be used to override the defaults set for the session in the initialization file, or set by the client with environment variables.

    SQL> ALTER SESSION SET NLS_SORT = FRENCH;
    
    

    For a complete description of ALTER SESSION, see Oracle8i SQL Reference.

  4. As a SQL function parameter. NLS parameters can be used explicitly to hardcode NLS behavior within a SQL function. Doing so will override the defaults set for the session in the initialization file, the client with environment variables, or set for the session by ALTER SESSION. For example:

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

The database character set and the national character set are specified in the CREATE DATABASE statement. For a complete description of CREATE DATABASE, see Oracle8i SQL Reference.

Table 2-1 shows the precedence order when using NLS parameters. Higher priority settings will override lower priority settings. For example, a default value will have the lowest possible priority, and can be overridden by any other method. And explicitly setting an NLS parameter within a SQL function can override all other settings -- default, initialization parameter, environment variable, and ALTER SESSION parameters.

Table 2-1 Parameters and Their Priorities
  Highest Priority 

Explicitly set in SQL functions 

Set by an ALTER SESSION statement 

Set as an environment variable 

Specified in the initialization parameter file 

Default 

 

Lowest Priority 

Table 2-2 lists the NLS parameters available with the Oracle server.

Table 2-2 Parameters and their Scope
Parameter  Description  Default  Scope
(I= INIT.ORA,
E= Environment
Variable,
A= Alter Session)
 

NLS_CALENDAR 

Calendar system 

Gregorian 

I, E, A 

NLS_COMP 

SQL Operator comparison 

Binary 

I, E, A 

NLS_CREDIT 

Credit accounting symbol 

NLS_TERRITORY 

-, E, - 

NLS_CURRENCY 

Local currency symbol 

NLS_TERRITORY 

I, E, A 

NLS_DATE_FORMAT 

Date format 

NLS_TERRITORY 

I, E, A 

NLS_DATE_LANGUAGE 

Language for day and month names 

NLS_LANGUAGE 

I, E, A 

NLS_DEBIT 

Debit accounting symbol 

NLS_TERRITORY 

-, E, - 

NLS_ISO_CURRENCY 

ISO international currency symbol 

NLS_TERRITORY 

I, E, A 

NLS_LANG 

Language, territory, character set 

American_America.US7ASCII 

-, E, - 

NLS_LANGUAGE 

Language 

NLS_LANG 

I, -, A 

NLS_LIST_SEPARATOR 

Character separating items in a list 

NLS_TERRITORY 

-, E, - 

NLS_MONETARY_CHARACTERS 

Monetary symbol for dollar and cents (or their equivalents) 

NLS_TERRITORY 

-, E, - 

NLS_NCHAR 

National character set 

NLS_LANG 

-, E, - 

NLS_NUMERIC_CHARACTERS 

Decimal character and group separator 

NLS_TERRITORY 

I, E, A 

NLS_SORT 

Character Sort Sequence 

NLS_LANGUAGE 

I, E, A 

NLS_TERRITORY 

Territory 

NLS_LANG 

I, -, A 

NLS_DUAL_CURRENCY 

Dual currency symbol 

NLS_TERRITORY 

I, E, A 

Choosing a Locale with NLS_LANG

A locale is a linguistic and cultural environment in which a system or program is running. Setting the NLS_LANG parameter is the simplest way to specify locale behavior. It sets the language and territory used by the client application. It also sets the character set of the client, i.e., the character set of data entered or displayed by a client program.

The NLS_LANG parameter has three components (language, territory, and charset) in the form:

NLS_LANG = language_territory.charset

Each component controls the operation of a subset of NLS features.

language 

Specifies conventions such as the language used for Oracle messages, collation, 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, so either (or both) territory or charset can be omitted. If language is not specified, the value defaults to American. For a complete list of languages, see Appendix A, "Locale Data"

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 territory is not specified, the value defaults from the language. For a complete list of territories, see Appendix A, "Locale Data"

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, WE8EBCDIC500, or JA16EUC. Each language has a default character set associated with it. For a complete list of character sets, see Appendix A, "Locale Data"


Note:

All components of the NLS_LANG definition are optional; any item left out will default. If you specify territory or charset, you must include the preceding delimiter [underscore (_) for territory, period (.) for charset], 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.US7ASCII

or

NLS_LANG = FRENCH_CANADA.WE8DEC

or

NLS_LANG = JAPANESE_JAPAN.JA16EUC

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

NLS_LANG = JAPANESE_JAPAN.WE8DEC

Because WE8DEC does not support any Japanese characters, the result is that you would be unable to store Japanese data.

Specifying NLS_LANG

You can set NLS_LANG as an environment variable at the command line. For example, on UNIX, you could specify the value of NLS_LANG by entering the following line at the prompt:

% setenv NLS_LANG FRENCH_FRANCE.WE8DEC

NLS_LANG Examples

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

% setenv NLS_LANG American_America.WE8ISO8859P1
SQL> SELECT ename, hiredate, ROUND(sal/12,2)  sal FROM emp;
ENAME                         HIREDATE            SAL
-------                       ---------           -------
Clark                         09-DEC-88           4195.83
Miller                        23-MAR-92           4366.67
Strauß                        01-APR-95           3795.87

If NLS_LANG is set with the language as French, the territory as France, and the character set as Western European 8-bit ISO 8859-1, the same query returns:

% setenv NLS_LANG French_France.WE8ISO8859P1
SQL> SELECT ename, hiredate, ROUND(sal/12,2)  sal FROM emp;
ENAME                         HIREDATE           SAL
---------                     ---------          -------
Clark                         09/12/88           4195,83
Miller                        23/03/92           4366,67
Strauß                        01/04/95           3795,87

Overriding Language and Territory Specifications

NLS_LANG 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 database and client application are automatically the same.

The language and territory components of NLS_LANG set the default values for the other NLS parameters, such as date format, numeric characters, and collation. Each of these detailed parameters can be set in the client environment to fine-tune the language and territory defaults.

Note that NLS parameters in the client environment are ignored if NLS_LANG is not set.

If NLS_LANG is not set, 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 your NLS environment dynamically during the session. To do so, you can use NLS_LANGUAGE, NLS_TERRITORY and other NLS parameters in 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. SQL*Plus is an example of an application that does it; Oracle Developer is an example of an application that does not do this.

NLS Database Parameters

When a new database is created during the execution of CREATE DATABASE statement, the NLS database environment is established. The current NLS instance parameters, as defined by the initialization parameter file, are stored in the Data Dictionary along with the database and national character sets.

Checking NLS Parameters

You can find the values for NLS settings with some views and an OCI function call.

NLS Views

Applications can check the current session, instance and database NLS parameters by querying the following Data Dictionary views:

See Oracle8i Reference for further details.

OCI Functions

To allow user applications to query client NLS settings Oracle8i OCI contains the OCINlsGetInfo function. See Chapter 5, "OCI Programming", for the description of this function.

Language and Territory Parameters

NLS_LANGUAGE and NLS_TERRITORY parameters are general NLS parameters describing NLS behavior of locale-dependent operations.

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:

The value specified for NLS_LANGUAGE in the initialization 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

In this case, the server message

ORA-00942:  table or view does not exist

will appear as

ORA-00942:  table ou vue inexistante

Messages used by the server are stored in binary-format files that are placed in the ORA_RDBMS directory, or the equivalent. Multiple versions of these files can exist, one for each supported language, using the filename convention

<product_id><language_abbrev>.MSB

For example, the file containing the server messages in French is called ORAF.MSB, with "F" being the language abbreviation for French.

Messages are stored in these files in one specific character set, depending on the language and operating system. If this is different from the database character set, message text is automatically converted to the database character set. If necessary, it will be further converted to the client character set if it 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 operating system-specific. You can alter the NLS_LANGUAGE parameter by changing the value in the initialization file and then restarting the instance.

For more information on the default value, see your operating system-specific Oracle documentation.

The following examples show behavior before and after setting NLS_LANGUAGE.

SQL> ALTER SESSION SET NLS_LANGUAGE=Italian;
SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp;
ENAME     HIREDATE    SAL
-----     --------    ---
Clark     09-Dic-88   4195.83
Miller    23-Mar-87   4366.67
Strauß    01-Apr-95   3795.87

SQL> ALTER SESSION SET NLS_LANGUAGE=German;
SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp;
ENAME     HIREDATE    SAL
-----     --------    ---
Clark     09-DEZ-88   4195.83
Miller    23-MÄR-87   4366.67
Strauß    01-APR-95   3795.87

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:

The value specified for NLS_TERRITORY in the initialization 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

In this case, numbers would be formatted using a comma as the decimal character.

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

If NLS_LANG is specified in the client environment, the initialization file value is overridden already at the connection time.

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

To change the territory dynamically to France, the following statement should be issued:

SQL> ALTER SESSION SET NLS_TERRITORY=France;

The following examples show behavior before and after setting NLS_TERRITORY.

SQL> describe SalaryTable;
Name                        Null?           TYPE
---------                   -------         ------
SALARY                                      NUMBER


SQL> column SALARY format L999,999.99;
SQL> SELECT * from SalaryTable;
               SALARY
---------------------
          $100,000.00
          $150,000.00

SQL> ALTER SESSION SET NLS_TERRITORY = Germany;
Session altered.

SQL> SELECT * from SalaryTable;
               SALARY
---------------------
         DM100,000.00
         DM150,000.00

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

SQL> SELECT * from SalaryTable;
               SALARY
---------------------
         DM100,000.00
         DM150,000.00

SQL> ALTER SESSION SET NLS_TERRITORY = France;
Sitzung wurde geändert.

SQL> SELECT * from SalaryTable;
               SALARY
---------------------
          F100,000.00
          F150,000.00

Note that the symbol for currency units changed, but no monetary conversion calculations were performed. The numeric characters did not change because they were hardcoded by the SQL*Plus statement.

ALTER SESSION

The default values for language and territory can be overridden during a session by using the ALTER SESSION statement. For example:

% setenv NLS_LANG Italian_Italy.WE8DEC

SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp;
ENAME     HIREDATE    SAL
-----     --------    ---
Clark     09-Dic-88   4195,83
Miller    23-Mar-87   4366,67
Strauß    01-Apr-95   3795,87

SQL> ALTER SESSION SET NLS_LANGUAGE = German
2     > NLS_DATE_FORMAT = 'DD.MON.YY'
3     > NLS_NUMERIC_CHARACTERS = '.,';

SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp;
ENAME     HIREDATE    SAL
-----     --------    ---
Clark     09.DEZ.88   4195.83
Miller    23.MÄR.87   4366.67
Strauß    01.APR.95   3795.87

This feature implicitly determines the language environment of the database for each session. An ALTER SESSION statement is automatically executed when a session connects to a database 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, the changes are propagated to all instances to which that user session is connected.

Messages and Text

All messages and text should be in the same language. For example, when running an Oracle Developer application, messages and boilerplate text seen by the user originate from three sources:

The application is responsible for meeting the last requirement. NLS takes care of the other two.

Time Parameters

Many different time formats are used throughout the world. Some typical ones are shown in Table 2-3.

Table 2-3 Time Parameters
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:ss am 

1.50.23 PM 

Date Parameters

Oracle allows you to control how dates appear through the use of date parameters.

Date Formats

Many different date formats are used throughout the world. Some typical ones are shown in Table 2-4.

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

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 

This parameter defines the default date format to use with the TO_CHAR and TO_DATE functions. The default value of this parameter is determined by NLS_TERRITORY. The value of this parameter can be any valid date format mask, and 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'

As another example, to set the default date format to display Roman numerals for months, you would include the following line in the initialization file:

NLS_DATE_FORMAT = "DD RM YYYY"

With such a default date format, the following SELECT statement would return the month using Roman numerals (assuming today's date is February 12, 1997):

SELECT TO_CHAR(SYSDATE) CURRDATE
     FROM DUAL;
CURRDATE
---------
12 II 1997

The value of this parameter 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, two one-byte strings (the two slashes), and the two-byte terminator for the format mask. The format for the value of this parameter 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 file and then restarting the instance, and you can alter the value during a session using an ALTER SESSION SET NLS_DATE_FORMAT statement.

Year 2000 Issues

Currently, the default date format for most territories specifies the year format as "RR" to indicate the last 2 digits. If your applications are Year 2000 compliant, you can safely specify the NLS_DATE_FORMAT using "YYYY" or "RRRR". If your applications are not yet Year 2000 compliant, you may wish to specify the NLS_DATE_FORMAT as "RR". The "RR" format will have the following effect: Given a year with 2 digits, RR will return a year in the next century if the year is less than 50 and the last 2 digits of the current year are greater than or equal to 50; return a year in the preceding century if the year is greater than or equal to 50 and the last 2 digits of the current year are less than 50.

See the Date Format Models section in the Oracle8i SQL Reference for full details on Date Format Elements.

Date Formats and Partition Bound Expressions

Partition bound expressions for a date column must specify a date using a format which requires that the month, day, and 4-digit year are fully specified. For example, the date format MM-DD-YYYY requires that the month, day, and 4-digit year are fully specified. In contrast, the date format DD-MON-YY (11-jan-97, for example) is invalid because it relies on the current date for the century.

Use TO_DATE() to specify a date format which requires the full specification of month, day, and 4-digit year. For example:

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

If the default date format, specified by NLS_DATE_FORMAT, of your session does not support specification of a date independent of current century (that is, if your default date format is MM-DD-YY for example), you must take one of the following actions:

For more information on using TO_DATE(), see Oracle8i SQL Reference.

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 

This parameter specifies the language for the spelling of day and month names by the functions TO_CHAR and TO_DATE, overriding that specified implicitly by NLS_LANGUAGE. NLS_DATE_LANGUAGE has the same syntax as the NLS_LANGUAGE parameter, and all supported languages are valid values. For example, to specify the date language as French, the parameter should be set as follows:

NLS_DATE_LANGUAGE = FRENCH

In this case, the query

SQL> SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy')
   > FROM DUAL;

returns

Mercredi:12 Février 1997

Month and day name abbreviations are also in the language specified, for example:

SQL> SELECT TO_CHAR(SYSDATE, 'Dy:dd Mon yyyy')
   > FROM DUAL;

Me:12 Fév 1997

The default date format also uses the language-specific month name abbreviations. For example, if the default date format is DD-MON-YYYY, the above date would be inserted using:

SQL> INSERT INTO tablename VALUES ('12-Fév-1997');

The abbreviations for AM, PM, AD, and BC are also returned in the language specified by NLS_DATE_LANGUAGE. Note that numbers spelled using the TO_CHAR function always use English spellings; for example:

SQL> SELECT TO_CHAR(TO_DATE('12-Fév'),'Day: ddspth Month')
   > FROM DUAL;

returns:

Mercredi: twelfth Février

You can alter the default value of NLS_DATE_LANGUAGE by changing its value in the initialization file and then restarting the instance, and you can alter the value during a session using an ALTER SESSION SET NLS_DATE_LANGUAGE statement.

Calendar Parameter

Oracle allows you to control calendar-related items through the use of parameters.

Calendar Formats

The type of calendar information stored for each territory is as follows:

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.

Table 2-5 First Day of the Week
      März  1998     
Mo  Di  Mi  Do  Fr  Sa  So 
           
10  11  12  13  14  15 
16  17  18  19  20  21  22 
23  24  25  26  27  28  29 
30  31           

First Calendar Week of the Year

Many countries, Germany, for example, use weeks for scheduling, planning, and bookkeeping. Oracle supports this convention.

In the ISO standard, the year relating to an ISO week number can be different from the calendar year. For example, 1st Jan 1988 is in ISO week number 53 of 1987. A week always starts on a Monday and ends on a Sunday.

To support the ISO standard, a format element IW is provided that returns the ISO week number.

A typical example with four or more days in the first week is:

Table 2-6 Day of the Week Example 1
    January  1998       
Mo  Tu  We  Th  Fr  Sa  Su   
      <= 1st week of 1998 
10  11  <= 2nd week of 1998 
12  13  14  15  16  17  18  <= 3rd week of 1998 
19  20  21  22  23  24  25  <= 4th week of 1998 
26  27  28  29  30  31    <= 5th week of 1998 

A typical example with three or fewer days in the first week is:

Table 2-7 Day of the Week Example 2
    January  1999       
Mo  Tu  We  Th  Fr  Sa  Su   
        <= 53rd week of 1998 
10  <= 1st week of 1999 
11  12  13  14  15  16  17  <= 2nd week of 1999 
18  19  20  21  22  23  24  <= 3rd week of 1999 
25  26  27  28  29  30  31  <= 4th week of 1999 

Number of Days and Months in a Year

Oracle supports six calendar systems, as well as the default Gregorian.

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:

For example, if NLS_CALENDAR is set to "Japanese Imperial", the date format is "E YY-MM-DD", and the date is May 15, 1997, then the SYSDATE is displayed as follows:

SELECT SYSDATE FROM DUAL;
SYSDATE
--------
H 09-05-15

Numeric Parameters

Oracle allows you to control how numbers appear.

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, the application needs to be able to display numeric information in the format expected at the client site.

Some typical ones are shown in Table 2-8.

Table 2-8 Numeric Formats
Country  Example 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 

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 grouping separator, overriding those defined implicitly by NLS_TERRITORY. The group separator is the character that separates integer groups (that is, the thousands, millions, billions, and so on). The decimal character separates the integer and decimal parts of a number.

Any character can be the decimal or group separator. The two characters specified must be single-byte, and both 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 (>).

The characters are specified in the following format:

NLS_NUMERIC_CHARACTERS = "<decimal_character><group_separator>"

The grouping separator is the character returned by the number format mask G. For example, to set the decimal character to a comma and the grouping separator to a period, the parameter should be set as follows:

NLS_NUMERIC_CHARACTERS = ",."

Both characters are single byte and must be different. Either can be a space.

Note: 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. For example, in the following statement:

INSERT INTO SIZES (ITEMID, WIDTH, HEIGHT, QUANTITY)
     VALUES (618, '45,5', 27.86, TO_NUMBER('1.234','9G999'));

618 and 27.86 are numeric literals. The text literal '45,5' is implicitly converted to the number 45.5 (assuming that WIDTH is a NUMBER column). The text literal '1.234' is explicitly converted to a number 1234. This statement is valid only if NLS_NUMERIC_CHARACTERS is set to ",.".

You can alter the default value of NLS_NUMERIC_CHARACTERS in either of these ways:

Monetary Parameters

Oracle allows you to control how currency and financial symbols appear.

Currency Formats

Many different currency formats are used throughout the world. Some typical ones are shown in Table 2-9.

Table 2-9 Currency Format Examples
Country  Example 

Estonia 

1 234,56 kr 

Germany 

1.234,56 DM 

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  

This parameter specifies the character string returned by the number format mask L, the local currency symbol, overriding that defined implicitly by NLS_TERRITORY. For example, to set the local currency symbol to "Dfl " (including a space), the parameter should be set as follows:

NLS_CURRENCY = "Dfl "

In this case, the query

SQL> SELECT TO_CHAR(TOTAL, 'L099G999D99') "TOTAL"
   > FROM ORDERS WHERE CUSTNO = 586;

would return

TOTAL
-------------
Dfl 12.673,49

You can alter the default value of NLS_CURRENCY by changing its value in the initialization file and then restarting the instance, and you can alter its value during a session using an ALTER SESSION SET NLS_CURRENCY 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 

This parameter specifies the character string returned by the number format mask C, the ISO currency symbol, overriding that 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 Specification 4217 1987-07-15 defines unique "international" currency symbols for the currencies of specific territories (or countries).

For example, the ISO currency symbol for the US Dollar is USD, for the Australian Dollar AUD. To specify the ISO currency symbol, the corresponding territory name is used.

NLS_ISO_CURRENCY has the same syntax as the NLS_TERRITORY parameter, and all supported territories are valid values. For example, to specify the ISO currency symbol for France, the parameter should be set as follows:

NLS_ISO_CURRENCY = FRANCE

In this case, the query

SQL> SELECT TO_CHAR(TOTAL, 'C099G999D99') "TOTAL"
> FROM ORDERS WHERE CUSTNO = 586;

returns

TOTAL
-------------
FRF12.673,49

You can alter the default value of NLS_ISO_CURRENCY by changing its value in the initialization file and then restarting the instance, and you can alter its value during a session using an ALTER SESSION SET NLS_ISO_CURRENCY statement.

Typical ISO currency symbols are shown in Table 2-10.

Table 2-10 ISO Currency Examples
Country  Example 

Estonia 

1 234 567,89 EEK 

Germany 

1.234.567,89 DEM 

Japan 

1,234,567.89 JPY 

UK 

1,234,567.89 GBP 

US 

1,234,567.89 USD 

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 

You can use this parameter to override the default dual currency symbol defined in the territory. When starting a new session without setting NLS_DUAL_CURRENCY, you will use the default dual currency symbol defined in the territory of your current language environment. When you set NLS_DUAL_CURRENCY, you will start up a session with its value as the dual currency symbol.

NLS_DUAL_CURRENCY was introduced to help support the Euro. The following Table 2-11 lists the character sets that support the Euro symbol:

Table 2-11 Character Sets that Support the Euro Symbol
Name  Description  Euro Code Value 

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 

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 520-2533 - ASCII 8-bit 

0x80 

AL24UTFFSS 

Unicode 1.1 UTF-8 Universal character set 

U+20AC 

UTF8 

Unicode 2.1 UTF-8 Universal character set 

U+20AC 

UTFE 

UTF-EBCDIC encoding of Unicode 2.1 

U+20AC 

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 characters that indicate monetary units, such as the dollar sign ($) for U.S. Dollars, and the cent symbol (¢) for cents.

The two characters specified must be single-byte and cannot be the same as each other. They also cannot be any numeric character or any of the following characters: plus (+), hyphen (-), less than sign (<), greater than sign (>).

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.

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.

This parameter can be specified only in the client environment. It can be retrieved through the OCIGetNlsInfo function.

Collation Parameters

Oracle allows you to choose how data is sorted through the use of collation parameters.

Sorting Order

Different languages have different sort orders. What's more, different cultures or countries using the same alphabets may sort words differently. For example, the German language sharp s (ß) is sorted differently in Germany and Austria. The linguistic sort sequence German sorts this sequence as the two characters (SS), while the linguistic sort sequence Austrian sorts it as (SZ). Another example is the treatment of ö, o, and oe. They are sorted differently throughout the various Germanic languages.

Oracle provides many different types of sort, but achieving a linguistically correct sort frequently harms performance. This is a trade-off the database administrator needs to make on a case-by-case basis. A typical case would be when sorting Spanish. In traditional Spanish, ch and ll are distinct characters, which means that the correct order would be: cerveza, colorado, cheremoya, lago, luna, llama. But a true linguistic sort will cause some performance degradation.

Sorting East Asian languages is difficult and complex. At present, Oracle typically relies on the binary order of the particular encoded character set for sorting East Asian Languages.

Sorting Character Data

Conventionally, when character data is sorted, the sort sequence is based on the numeric values of the characters defined by the character encoding scheme. Such a sort is called a binary sort. Such a sort produces reasonable results for the English alphabet because the ASCII and EBCDIC standards define the letters A to Z in ascending numeric value.

Note, however, that in the ASCII standard, all uppercase letters appear before any lowercase letters. In the EBCDIC standard, the opposite is true: all lowercase letters appear before any uppercase letters.

Binary Sorts

When characters used in other languages are present, a binary sort generally does not produce reasonable results. For example, an ascending ORDER BY query would return the character strings ABC, ABZ, BCD, ÄBC, in that sequence, when the Ä has a higher numeric value than B in the character encoding scheme.

Linguistic Sorts

To produce a sort sequence that matches the alphabetic sequence of characters for a particular language, another sort technique must be used that sorts characters independently of their numeric values in the character encoding scheme. This technique is called a linguistic sort. A linguistic sort operates by replacing characters with other binary values that reflect the character's proper linguistic order so that a sort returns the desired result.

The Oracle server provides both sort mechanisms. Linguistic sort sequences are defined as part of language-dependent data. Each linguistic sort sequence has a unique name. NLS parameters define the sort mechanism for ORDER BY queries. A default value can be specified, and this value can be overridden for each session with the NLS_SORT parameter. A complete list of linguistic definitions is provided in "Linguistic Definitions".

Warning: Linguistic sorting is not supported on Asian multi-byte character sets. If the database character set is multi-byte, you will get binary sorting, which makes the sort sequence dependent on the character set specification. There are two exceptions to this rule: Japanese Hiragana/Katakana and the UTF8 character set. This means that the Japanese Yomi sort is only possible by creating an extra column using the Hiragana or Katakana reading for the kanji and sorting on that column.

Linguistic Indexes

You can create a function-based index that uses languages other than English. The index does not change the linguistic sort order determined by NLS_SORT. The index just improves the performance. A simple example is:

SQL> CREATE INDEX nls_index ON my_table (NLSSORT(name, 'NLS_SORT = German'));

So

SQL> SELECT * FROM my_table WHERE NLSSORT(name) IS NOT NULL
   > ORDER BY name;

returns the result much faster than without an index.

For more information, see the description of function-based indexes in Oracle8i Concepts.

Multiple Linguistic Indexes

If you store character data of multiple languages into one database, you may want to create multiple linguistic indexes for one column. This approach improves the performance of the linguistic sort for a specific column for multiple languages and is a powerful feature for multilingual databases.

An example of creating multiple linguistic indexes is:

CREATE INDEX french_index ON emp (NLSSORT(emp_name, 'NLS_SORT=FRENCH'));
CREATE INDEX german_index ON emp (NLSSORT(emp_name, 'NLS_SORT=GERMAN'));

When session variable NLS_SORT is set to FRENCH, french_index can be used and when it is set to GERMAN, german_index can be used.

Requirements for Linguistic Indexes

If you want to use a single linguistic index or multiple linguistic indexes, there are some requirements to be met in order for the linguistic index to be used. The first requirement is that QUERY_REWRITE_ENABLED needs to be true. This is not a specific requirement for linguistic indexes, but for all function-based indexes. Here is an example of setting QUERY_REWRITE_ENABLED.

ALTER SESSION SET query_rewrite_enabled=true;

The second requirement, which is specific to linguistic indexes, is that

NLS_COMP needs to be ANSI. There are various ways to set NLS_COMP.

Here is an example.

ALTER SESSION SET NLS_COMP = ANSI;

The third requirement is that NLS_SORT needs to indicate the linguistic definition you want to use for the linguistic sort. If you want a FRENCH linguistic sort order, NLS_SORT needs to be FRENCH. If you want a GERMAN linguistic sort order, NLS_SORT needs to be GERMAN. There are various ways to set NLS_SORT. The below is an example. Although the example below uses the ALTER SESSION statement, it's probably better for you to set NLS_SORT as a client environment variable so that you can use the same SQL statements for all languages and different linguistic indexes can be picked up based on NLS_SORT being set in the client environment.

ALTER SESSION SET NLS_SORT='FRENCH';

The fourth requirement is that you need to use the cost-based optimizer. Function-based indexes do not get picked up by the rule-based optimizer. Function-based indexes are used only by the cost-based optimizer.

The last thing is that you need to specify "WHERE NLSSORT(column_name) IS NOT NULL" when you want to use "ORDER BY column_name" and the "column_name" is the column with the linguistic index. This is necessary only when you use "ORDER BY". See the example below.

The below is an example of using a FRENCH linguistic index. For NLS_SORT, you may want to set it in the client environment variable instead of the ALTER SESSION statement (as described above).

ALTER SESSION SET query_rewrite_enabled=true;
ALTER SESSION SET NLS_COMP = ANSI;
ALTER SESSION SET NLS_SORT='FRENCH';
CREATE TABLE test(col VARCHAR(20) NOT NULL);
CREATE INDEX test_idx ON test(NLSSORT(col, 'NLS_SORT=FRENCH'));
SELECT * FROM test WHERE NLSSORT(col) IS NOT NULL ORDER BY col;
SELECT * FROM test WHERE col > 'JJJ';

For more information, see the description of function-based indexes in Oracle8i Concepts.

Case-Insensitive Search

You can create a function-based index which improves the performance of case-insensitive searches. For example:

SQL> CREATE INDEX case_insensitive_ind ON my_table(NLS_UPPER(empname));
SQL> SELECT * FROM my_table WHERE NLS_UPPER(empname) = 'KARL';

For more information, see the description of function-based indexes in Oracle8i Application Developer's Guide - Fundamentals.

Linguistic Special Cases

Linguistic special cases are character sequences that need to be treated as a single character when sorting. Such special cases are handled automatically when using a linguistic sort. For example, one of the linguistic sort sequences for Spanish specifies that the double characters ch and ll are sorted as single characters appearing between c and d and between l and m respectively.

Another example is the German language sharp s (ß). The linguistic sort sequence German can sort this sequence as the two characters SS, while the linguistic sort sequence Austrian sorts it as SZ.

Special cases like these are also handled when converting uppercase characters to lowercase, and vice versa. For example, in German the uppercase of the sharp s (ß) is the two characters SS. Such case-conversion issues are handled by the NLS_UPPER, NLS_LOWER, and NLS_INITCAP functions, according to the conventions established by the linguistic sort sequence. (The standard functions UPPER, LOWER, and INITCAP do not handle these special cases.)

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 

This parameter specifies the type of sort for character data, overriding that defined implicitly by NLS_LANGUAGE.

The syntax of NLS_SORT is:

NLS_SORT = { BINARY | name }

BINARY specifies a binary sort and name specifies a particular linguistic sort sequence. For example, to specify the linguistic sort sequence called German, the parameter should be set 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 defined that uses the same name.

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). But when NLS_SORT is set to a linguistic sort, a sort is always needed to satisfy the ORDER BY clause if the linguistic index does not exist for the linguistic sort order specified by NLS_SORT. If the linguistic index exists for the linguistic sort order 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 file and then restarting the instance, and you can alter its value during a session using an ALTER SESSION SET NLS_SORT command.

A complete list of linguistic definitions is provided in Table A-8, "Linguistic 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 this parameter to avoid the cumbersome process of using NLS_SORT in SQL statements. Normally, comparison in the WHERE clause is binary. To use linguistic comparison, the NLSSORT function must be used. Sometimes this can be tedious, especially when the linguistic sort needed has already been specified in the NLS_SORT session parameter. You can use NLS_COMP in such cases to indicate that the comparisons must be linguistic according to the NLS_SORT session parameter. This is done by altering the session:

SQL> ALTER SESSION SET NLS_COMP = ANSI;

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

SQL> ALTER SESSION SET NLS_COMP = BINARY;

As a final note, when NLS_COMP is set to ANSI, a linguistic index improves the performance of the linguistic comparison.

To enable a linguistic index, use the syntax:

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

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.

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 Parameters

You can specify the character set used for the client.

NLS_NCHAR

Parameter type: 

String 

Parameter scope: 

Environment Variable 

Default value: 

Derived from NLS_LANG 

Range of values: 

Any valid character set name 

NLS_NCHAR specifies the character set used by the client application for national character set data (NCHAR, NVARCHAR2, NCLOB). If it is not specified, the client application uses the same character set that it uses for the database character set data.


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index