Oracle NLS Parameters

The globalization support in Oracle Database enables you to store, process, and retrieve data in native languages. It ensures that database utilities, error messages, sort order, and date, time, monetary, numeric, and calendar conventions automatically adapt to any native language and locale.

In the past, Oracle referred to globalization support capabilities as National Language Support (NLS) features. NLS is actually a subset of globalization support. NLS is the ability to choose a national language and store data in a specific character set. Globalization support enables you to develop multilingual applications and software products that can be accessed and run from anywhere in the world simultaneously. An application can render content of the user interface and process data in the native users' languages and locale preferences.

NLS即National Language Support，即国家语言支持，相关参数均以NLS开头，主要用于配置以何种方式展示数据

数据库字符集也属于NLS参数(NLS_CHARACTERSET与NLS_NCHAR_CHARACTERSET)，它不能更改，表示数据在数据库中以何编码方式存储而不是展示

The languages that can be stored in a database are all languages written in scripts that are encoded by Oracle-supported character sets. Through the use of Unicode databases and data types, Oracle Database supports most contemporary languages.

可以通过以下方式配置NLS参数：

1. As initialization parameters on the server

Spfile/pfile中的NLS参数只对server有效，对client无效

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.

1. As environment variables on the client

操作系统的环境变量可用于指定client端设置，它也会覆盖session级别的server端设置

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

1. With the ALTER SESSION statement

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

1. In SQL functions

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

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

The following NLS parameters can be specified in SQL functions:

NLS_DATE_LANGUAGE

NLS_NUMERIC_CHARACTERS

NLS_CURRENCY

NLS_ISO_CURRENCY

NLS_DUAL_CURRENCY

NLS_CALENDAR

NLS_SORT

The following NLS parameters are not accepted in SQL functions except for NLSSORT:

NLS_LANGUAGE

NLS_TERRITORY

NLS_DATE_FORMAT

Table 9-1 SQL Functions and Their Valid NLS Parameters

SQL Function	Valid NLS Parameters
TO_DATE	NLS_DATE_LANGUAGE, NLS_CALENDAR
TO_NUMBER	NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY
TO_CHAR	NLS_DATE_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR
TO_NCHAR	NLS_DATE_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR
NLS_UPPER	NLS_SORT
NLS_LOWER	NLS_SORT
NLS_INITCAP	NLS_SORT
NLSSORT	NLS_SORT

In some languages, some lowercase characters correspond to more than one uppercase character or vice versa. As a result, the length of the output from the NLS_UPPER, NLS_LOWER, and NLS_INITCAP functions can differ from the length of the input.

The following examples show how to use NLS parameters in SQL functions:

TO_DATE ('1-JAN-99', 'DD-MON-YY', 'nls_date_language = American')

TO_CHAR (hire_date, 'DD/MON/YYYY', 'nls_date_language = French')

TO_CHAR (SYSDATE, 'DD/MON/YYYY','nls_date_language=''Traditional Chinese'' ')

TO_NUMBER ('13.000,00', '99G999D99','nls_numeric_characters = '',.''')

TO_CHAR (salary, '9G999D99L', 'nls_numeric_characters = '',.''nls_currency = ''EUR''')

TO_CHAR (salary, '9G999D99C', 'nls_numeric_characters = ''.,''nls_iso_currency = Japan')

NLS_UPPER (last_name, 'nls_sort = Swiss')

NLSSORT (last_name, 'nls_sort = German')

以下为设置生效优先级(Higher priority settings override lower priority settings)

下表列出了可用的NLS参数(不包含)

Because the SQL function NLS parameters can be specified only with specific functions,

the table does not show the SQL function scope. This table shows the following values for Scope:

   I = Initialization Parameter File
   E = Environment Variable
   A = ALTER SESSION

可以通过以下方式查看server的NLS相关设置

1. NLS的数据字典

1. 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.
2. NLS_INSTANCE_PARAMETERS shows the current NLS instance parameters that have been explicitly set and the values of the NLS instance parameters.
3. NLS_DATABASE_PARAMETERS shows the values of the NLS parameters for the database. The values are stored in the database.

1. NLS的动态视图

1. V$NLS_VALID_VALUES lists values for the following NLS parameters: 

NLS_LANGUAGE, NLS_SORT, NLS_TERRITORY, NLS_CHARACTERSET

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

1. OCINlsGetInfo() Function

（一）NLS_LANG Environment Variable

The NLS_LANG parameter has three components: language, territory(地方), and character set. 设置格式如下(注意分隔符)：

NLS_LANG = language_territory.charset

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

1. language

Specifies conventions约定 such as the language used for Oracle Database 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.

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

1. charset

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

NLS_LANG设置的是session默认属性(实际是当产生会话时自动通过alter session执行NLS_LANG设置)，可通过ALTER SESSION来更改language与territory属性(NLS_LANGUAGE与NLS_TERRITORY参数)，但不能用ALTER SESSION更改client character set

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 Database tools).

Using this parameter ensures that the language environments of both the database and the client application are automatically the same.

Immediately after the connection has been established, if the NLS_LANG environment setting is defined on the client side, then an implicit ALTER SESSION statement synchronizes the client and session NLS environments.

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

1. The values in NLS_LANG and other NLS parameters are case-insensitive.
2. All components of the NLS_LANG definition are optional; any item that is not specified uses its default value. For example, to set only the territory portion of NLS_LANG, use the following format: NLS_LANG=_JAPAN
3. You cannot modify the setting for the client character set with the ALTER SESSION statement.

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

$ setenv NLS_LANG American_America.WE8ISO8859P1

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

LAST_NAME                 HIRE_DATE     SALARY

------------------------- --------- ----------

...

Sciarra                     30-SEP-05      962.5

Urman                     07-MAR-06      975

Popp                      07-DEC-07      862.5

...

$ setenv NLS_LANG French_France.WE8ISO8859P1

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

LAST_NAME      HIRE_DATE     SALARY

-------------- ---------  ---------

...

Sciarra         30/09/05          962,5

Urman          07/03/06         975

Popp           07/12/07         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.

（二）NLS_OS_CHARSET Environment Variable

如果NLS_LANG的字符集设置与OS不同还要设置NLS_OS_CHARSET

The NLS_OS_CHARSET environment variable should be set on Oracle client installations if the client OS character set is different from the Oracle NLS client character set specified by the NLS_LANG environment variable.

The client OS character set is the character set used to represent characters in the OS fields like machine name, program executable name and logged on user name. On UNIX platforms, this is usually the character set specified in the LANG environment variable or the LC_ALL environment variable.

An example of setting NLS_OS_CHARSET would be if the locale charset specified in LANG or LC_ALL in a Linux client could be zh_CN (simplified Chinese) and the Oracle client application charset specified in NLS_LANG could be UTF8. In this case, the NLS_OS_CHARSET variable must be set to the equivalent Oracle charset ZHT16GBK.

The NLS_OS_CHARSET environment variable must be set to the Oracle character set name corresponding to the client OS character set.

If NLS_LANG corresponds to the OS character set, NLS_OS_CHARSET does not need to be set. NLS_OS_CHARSET does not need to be set and is ignored on Windows platforms.

（三）Language and Territory Parameters

1. NLS_LANGUAGE

NLS_LANGUAGE specifies the default conventions for the following session characteristics:

1. Language for server messages
2. Language for day and month names and their abbreviations (specified in the SQL functions TO_CHAR and TO_DATE)
3. 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.)
4. Default sorting sequence for character data when ORDER BY is specified. (GROUP BY uses a binary sort unless ORDER BY is specified.)
5. Writing direction
6. 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. If NLS_LANG is specified in the client environment, then the value of NLS_LANGUAGE in the initialization parameter file is overridden at connection time.

1. NLS_TERRITORY

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

1. Date format
2. Decimal character and group separator
3. Local currency symbol
4. ISO currency symbol
5. Dual currency symbol
6. First day of the week
7. Credit and debit symbols
8. ISO week flag
9. List separator

The value specified for NLS_TERRITORY in the initialization parameter file is the default for the instance. 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.

（四）Date and Time Parameters

1. Date Formats

1) NLS_DATE_FORMAT

The NLS_DATE_FORMAT parameter defines the default date format to use with the TO_CHAR and TO_DATE functions. 

The NLS_TERRITORY parameter determines the default value of NLS_DATE_FORMAT. 

The value of NLS_DATE_FORMAT can be any valid date format mask. For example:

NLS_DATE_FORMAT = "MM/DD/YYYY"

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

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

The value of NLS_DATE_FORMAT is stored in the internal date format. Each format element occupies two bytes, and each string occupies the number of bytes in the string plus a terminator byte. Also, the entire format mask has a two-byte terminator.

For example, "MM/DD/YY" occupies 14 bytes internally because there are three format elements (month, day, and year), two 3-byte strings (the two slashes), and the two-byte terminator for the format mask. 3x2+2x3+2

The format for the value of NLS_DATE_FORMAT cannot exceed 24 bytes.

2) NLS_DATE_LANGUAGE

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 also determines the language used for:

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

2. Time Formats

1) NLS_TIMESTAMP_FORMAT

NLS_TIMESTAMP_FORMAT defines the default date format for the TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE data types.

The following example shows a value for NLS_TIMESTAMP_FORMAT:

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

2) NLS_TIMESTAMP_TZ_FORMAT

NLS_TIMESTAMP_TZ_FORMAT defines the default date format for the TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE data types.

It is used with the TO_CHAR and TO_TIMESTAMP_TZ functions.

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

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

（五）Calendar Definitions

1. Calendar Formats

1) 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. The first day of the week is determined by the NLS_TERRITORY parameter.

2) First Calendar Week of the Year

Some countries use week numbers for scheduling, planning, and bookkeeping.

Oracle Database 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 Database provides the IW date format element. It returns the ISO week number.

The following table 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.

The following table 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.

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

3) Number of Days and Months in a Year

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

1. Japanese Imperial—uses the same number of months and days as Gregorian, but the year starts with the beginning of each Imperial Era.
2. ROC Official—uses the same number of months and days as Gregorian, but the year starts with the founding of the Republic of China.
3. 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).
4. Thai Buddha—uses a Buddhist calendar
5. Arabic Hijrah—has 12 months with 354 or 355 days
6. English Hijrah—has 12 months with 354 or 355 days
7. Ethiopian—has 12 months of 30 days each, then a 13th month that is either five or six days (leap year). The sixth day of the 13th month is added every four years.

The calendar system is specified by the NLS_CALENDAR parameter.

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

2. NLS_CALENDAR

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

NLS_CALENDAR can have one of the following values:

Arabic Hijrah

English Hijrah

Ethiopian

Gregorian

Japanese Imperial

Persian

ROC Official (Republic of China)

Thai Buddha

Example

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

（六）Numeric and List Parameters

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

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

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

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

Example 3-18 Setting NLS_NUMERIC_CHARACTERS

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

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

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

SQL> SELECT TO_CHAR(4000, '9G999D99') FROM DUAL;

TO_CHAR(4

---------

 4.000,00

1. NLS_LIST_SEPARATOR

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

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

（七）Monetary Parameters

1. NLS_CURRENCY(currency货币)

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.

SQL> SELECT TO_CHAR(order_total, 'L099G999D99') "total" FROM orders WHERE order_id > 2450;

total

---------------------

          $078,279.60

          $006,653.40

          $014,087.50

          $010,474.60

          $012,589.00

          $000,129.00

          $003,878.40

          $021,586.20

1. NLS_ISO_CURRENCY

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

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

More ISO currency symbols are shown in the following table.

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

SQL> ALTER SESSION SET NLS_ISO_CURRENCY = FRANCE;

1. NLS_DUAL_CURRENCY

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. See Table A-8 for the character sets that support the euro symbol.

1. Oracle Database Support for the Euro

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

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

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

Beginning with Oracle Database 9i Release 2, the value of NLS_ISO_CURRENCY results in the ISO currency symbol being set to EUR for EMU member countries who use the euro. For example, suppose NLS_ISO_CURRENCY is set to FRANCE. Enter the following SELECT statement:

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

     WHERE customer_id=116;

TOTAL

-------

EUR006,394.80

EUR011,097.40

EUR014,685.80

EUR000,129.00

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.

1. NLS_MONETARY_CHARACTERS

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.

1. NLS_CREDIT

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.

1. NLS_DEBIT

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

1. NLS_SORT

NLS_SORT specifies a set of matching and comparison rules for character data. It overrides the default value that is derived from NLS_LANGUAGE.

NLS_SORT = BINARY | collation_name

BINARY specifies the binary collation. collation_name specifies a linguistic named collation.

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 operation by choosing an index scan.

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

If a linguistic index exists for the linguistic collation specified by NLS_SORT, then the optimizer can, in some cases, satisfy the ORDER BY clause without doing a sort operation 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

1. NLS_COMP

The value of NLS_COMP affects the comparison behavior of SQL operations whose determined collation is USING_NLS_COMP.

NLS_COMP表示默认不指定NLSSORT时是否按NLS_SORT排序

ALTER SESSION SET NLS_COMP = BINARY;

ALTER SESSION SET NLS_SORT = GERMAN;

SELECT * FROM table1 ORDER BY column1;   --按binary排序

SELECT * FROM table1 ORDER BY NLSSORT(column1, 'NLS_SORT=FRENCH');  ---按FRENCH排序

ALTER SESSION SET NLS_COMP = LINGUISTIC;

ALTER SESSION SET NLS_SORT = GERMAN;

SELECT * FROM table1 ORDER BY column1;   --按GERMAN排序

SELECT * FROM table1 ORDER BY NLSSORT(column1, 'NLS_SORT=FRENCH');  ---按FRENCH排序

（九）Character Set Conversion Parameter

NLS_NCHAR_CONV_EXCP

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

（十）Length Semantics

NLS_LENGTH_SEMANTICS

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

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

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

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

NLS_LENGTH_SEMANTICS does not apply to tables created in the SYS schema. The data dictionary always uses byte semantics. Tables owned by SYS always use byte semantics if the length qualifier BYTE or CHAR is not specified in the table creation DDL.

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

Note that if the NLS_LENGTH_SEMANTICS environment variable is not set on the client or the client connects through the Oracle JDBC Thin driver, then the client session defaults to the value for the NLS_LENGTH_SEMANTICS initialization parameter of the instance to which the client connects. For compatibility reasons, Oracle recommends that this parameter be left undefined or set to BYTE.

Note:

Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in run-time errors, including buffer overflows.