3 Setting Up a Globalization Support Environment

Specifying the Value of NLS_LANG

In a UNIX operating system C-shell session, you can specify the value of NLS_LANG by entering a statement similar to the following example:

% setenv NLS_LANG FRENCH_FRANCE.WE8ISO8859P1

Because NLS_LANG is an environment variable, it is read by the client application at startup time. The client communicates the information defined by NLS_LANG to the server when it connects to the database server.

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

% 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

LAST_NAME                 HIRE_DATE     SALARY
------------------------- ---------  ---------
...
Sciarra                   30/09/05       962,5
Urman                     07/03/06         975
Popp                      07/12/07       862,5
...

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

The ALTER SESSION statement modifies only the session environment. The local client NLS environment is not modified, unless the client explicitly retrieves the new settings and modifies its local environment.

Locale Variants

Before Oracle Database 10g, Oracle defined language and territory definitions separately. This resulted in the definition of a territory being independent of the language setting of the user. Since Oracle Database 10g, some territories can have different date, time, number, and monetary formats based on the language setting of a user. This type of language-dependent territory definition is called a locale variant.

For the variant to work properly, both NLS_TERRITORY and NLS_LANGUAGE must be specified.

Table 3-3 shows the territories that have been enhanced to support variations.

Should the NLS_LANG Setting Match the Database Character Set?

The NLS_LANG character set should reflect the setting of the operating system character set of the client. For example, if the database character set is AL32UTF8 and the client is running on a Windows operating system, then you should not set AL32UTF8 as the client character set in the NLS_LANG parameter because there are no UTF-8 WIN32 clients. Instead, the NLS_LANG setting should reflect the code page of the client. For example, on an English Windows client, the code page is 1252. An appropriate setting for NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252.

Setting NLS_LANG correctly enables proper conversion from the client operating system character set to the database character set. When these settings are the same, Oracle Database assumes that the data being sent or received is encoded in the same character set as the database character set, so character set validation or conversion may not be performed. This can lead to corrupt data if the client code page and the database character set are different and conversions are necessary.

NLS_OS_CHARSET Environment Variable

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.

NLS Data Dictionary Views

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

• NLS_SESSION_PARAMETERS shows the NLS parameters and their values for the session that is querying the view. It does not show information about the character set.

• NLS_INSTANCE_PARAMETERS shows the current NLS instance parameters that have been explicitly set and the values of the NLS instance parameters.

• NLS_DATABASE_PARAMETERS shows the values of the NLS parameters for the database. The values are stored in the database.

NLS Dynamic Performance Views

Applications can check the following NLS dynamic performance views:

• V$NLS_VALID_VALUES lists values for the following NLS parameters: NLS_LANGUAGE, NLS_SORT, NLS_TERRITORY, NLS_CHARACTERSET

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

  See Also:

  Oracle Database Reference

OCINlsGetInfo() Function

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

NLS_LANGUAGE

NLS_LANGUAGE specifies the default conventions for the following session characteristics:

• Language for server messages

• Language for day and month names and their abbreviations (specified in the SQL functions TO_CHAR and TO_DATE)

• Symbols for equivalents of AM, PM, AD, and BC. (A.M., P.M., A.D., and B.C. are valid only if NLS_LANGUAGE is set to AMERICAN.)

• Default sorting sequence for character data when ORDER BY is specified. (GROUP BY uses a binary sort unless ORDER BY is specified.)

• Writing direction

• Affirmative and negative response strings (for example, YES and NO)

The value specified for NLS_LANGUAGE in the initialization parameter file is the default for all sessions in that instance. For example, to specify the default session language as French, the parameter should be set as follows:

NLS_LANGUAGE = FRENCH

Consider the following server message:

ORA-00942: table or view does not exist

When the language is French, the server message appears as follows:

ORA-00942: table ou vue inexistante

Messages used by the server are stored in binary-format files that are placed in the $ORACLE_HOME/product_name/mesg directory, or the equivalent for your operating system. Multiple versions of these files can exist, one for each supported language, using the following file name convention:

<product_id><language_abbrev>.MSB

For example, the file containing the server messages in French is called oraf.msb, because ORA is the product ID (<product_id>) and F is the language abbreviation (<language_abbrev>) for French. The product_name is rdbms, so it is in the $ORACLE_HOME/rdbms/mesg directory.

If NLS_LANG is specified in the client environment, then the value of NLS_LANGUAGE in the initialization parameter file is overridden at connection time.

Messages are stored in these files in one specific character set, depending on the language and the operating system. If this character set is different from the database character set, then message text is automatically converted to the database character set. If necessary, it is then converted to the client character set if the client character set is different from the database character set. Hence, messages are displayed correctly at the user's terminal, subject to the limitations of character set conversion.

The language-specific binary message files that are actually installed depend on the languages that the user specifies during product installation. Only the English binary message file and the language-specific binary message files specified by the user are installed.

The default value of NLS_LANGUAGE may be specific to the operating system. You can alter the NLS_LANGUAGE parameter by changing its value in the initialization parameter file and then restarting the instance.

All messages and text should be in the same language. For example, when you run an Oracle Developer application, the messages and boilerplate text that you see originate from three sources:

• Messages from the server

• Messages and boilerplate text generated by Oracle Forms

• Messages and boilerplate text generated by the application

NLS_LANGUAGE determines the language used for the first two kinds of text. The application is responsible for the language used in its messages and boilerplate text.

The following examples show behavior that results from setting NLS_LANGUAGE to different values.

ALTER SESSION SET NLS_LANGUAGE=Italian;

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

LAST_NAME                 HIRE_DATE     SALARY
------------------------- --------- ----------
...
Sciarra                   30-SET-05      962.5
Urman                     07-MAR-06        975
Popp                      07-DIC-07      862.5
...

SQL> ALTER SESSION SET NLS_LANGUAGE=German;

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-MRZ-06        975
Popp                      07-DEZ-07      862.5
...

NLS_TERRITORY

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.

SQL> SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees;

SALARY
--------------------
$24,000.00
$17,000.00
$17,000.00

ALTER SESSION SET NLS_TERRITORY = Germany;
Session altered.

SQL> SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees;

SALARY
--------------------
€24.000,00
€17.000,00
€17.000,00

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

SQL> SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees;

SALARY
--------------------
€24.000,00
€17.000,00
€17.000,00

ALTER SESSION SET NLS_TERRITORY = America;
Sitzung wurde geändert.

SQL> SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees;

SALARY
--------------------
$24,000.00
$17,000.00
$17,000.00

% setenv NLS_LANG Italian_Italy.WE8DEC

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

LAST_NAME                 HIRE_DATE     SALARY
------------------------- --------- ----------
...
Sciarra                   30-SET-05      962,5
Urman                     07-MAR-06        975
Popp                      07-DIC-07      862,5
...

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.

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.MRZ.06        975
Popp                      07.DEZ.07      862.5
...

Date Formats

Different date formats are shown in Table 3-4.

This section includes the following parameters:

• NLS_DATE_FORMAT

• NLS_DATE_LANGUAGE

NLS_DATE_FORMAT = "MM/DD/YYYY"

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

NLS_DATE_FORMAT = "DD RM YYYY"

SELECT TO_CHAR(SYSDATE) currdate FROM DUAL;

CURRDATE
---------
12 II 1997

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

ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;

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

TO_CHAR(SYSDATE,'DAY:DDMONTHYYYY')
------------------------------------------------------------
Vendredi:07 Décembre  2001

SQL> SELECT TO_CHAR(TO_DATE('12-Oct.-2001'),'Day: ddspth Month') FROM DUAL;

TO_CHAR(TO_DATE('12-OCT.-2001'),'DAY:DDSPTHMONTH')
--------------------------------------------------------------------
Vendredi: twelfth Octobre

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

TO_CHAR(SYSDATE,'DY:DDMO
------------------------
Ve:07 Déc. 2001

INSERT INTO tablename VALUES ('12-Févr.-1997');

Time Formats

Different time formats are shown in Table 3-5.

This section contains information about the following parameters:

• NLS_TIMESTAMP_FORMAT

• NLS_TIMESTAMP_TZ_FORMAT

  See Also:

  Chapter 4, "Datetime Data Types and Time Zone Support"

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

SQL> SELECT TO_TIMESTAMP('11-nov-2000 01:00:00.336', 'dd-mon-yyyy hh:mi:ss.ff')

FROM DUAL;

TO_TIMESTAMP('11-NOV-200001:00:00.336','DD-MON-YYYYHH:MI:SS.FF')
---------------------------------------------------------------------------
2000-11-11 01:00:00.336000000

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

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;

TO_TIMESTAMP_TZ('2000-08-20,05:00:00.55AMERICA/LOS_ANGELES','YYYY-MM-DDHH:M
---------------------------------------------------------------------------
2000-08-20 05:00:00.550000000 -07:00

Calendar Formats

The following calendar information is stored for each territory:

• First Day of the Week

• First Calendar Week of the Year

• Number of Days and Months in a Year

• First Year of Era

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

  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

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

SELECT SYSDATE FROM DUAL;

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

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

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

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. Setting NLS_NUMERIC_CHARACTERS overrides the values derived from the setting of NLS_TERRITORY.

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.

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",.";

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

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

• Changing the value of NLS_NUMERIC_CHARACTERS in the initialization parameter file and then restarting the instance

• Using the ALTER SESSION statement to change the parameter's value during a session

  See Also:

  Oracle Database SQL Language Reference for more information about the ALTER SESSION statement

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

Currency Formats

Different currency formats are used throughout the world. Some typical ones are shown in Table 3-10.

NLS_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> connect oe/oe
Connected.

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

WHERE order_id > 2450;

total
---------------------
          $078,279.60
          $006,653.40
          $014,087.50
          $010,474.60
          $012,589.00
          $000,129.00
          $003,878.40
          $021,586.20

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

• Using an ALTER SESSION statement

  See Also:

  Oracle Database SQL Language Reference for more information about the ALTER SESSION statement

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

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

ALTER SESSION SET NLS_ISO_CURRENCY = FRANCE;

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

WHERE customer_id = 146;

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

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

• Using an ALTER SESSION statement

  See Also:

  Oracle Database SQL Language Reference for more information about the ALTER SESSION statement

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, "Character Sets that Support the Euro Symbol" for the character sets that support the euro symbol.

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:

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

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

You should see results similar to the following output:

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.

It is not possible to override the ISO currency symbol that results from the value of NLS_ISO_CURRENCY.

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.

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.

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.

NLS_SORT

NLS_SORT specifies the type of sort for character data. It overrides the default value that is derived from NLS_LANGUAGE.

NLS_SORT contains either of the following values:

NLS_SORT = BINARY | sort_name

BINARY specifies a binary sort. sort_name specifies a linguistic sort sequence.

NLS_SORT = German

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:

  • Chapter 5, "Linguistic Sorting and Matching"

  • Oracle Database SQL Language Reference for more information about the ALTER SESSION statement

  • "Linguistic Sorts" for a list of linguistic sort names

NLS_COMP

The value of NLS_COMP affects the comparison behavior of SQL operations.

You can use NLS_COMP to avoid the cumbersome process of using the NLSSORT function in SQL statements when you want to perform a linguistic comparison instead of a binary comparison. When NLS_COMP is set to LINGUISTIC, SQL operations perform a linguistic comparison based on the value of NLS_SORT. A setting of ANSI is for backward compatibility; in general, you should set NLS_COMP to LINGUISTIC when you want to perform a linguistic comparison.

Set NLS_COMP to LINGUISTIC as follows:

ALTER SESSION SET NLS_COMP = LINGUISTIC;

When NLS_COMP is set to LINGUISTIC, 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'));

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.

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.