Checking NLS Parameters

NLS_SESSION_PARAMETERS shows the current NLS parameters of the session querying the view.

NLS_INSTANCE_PARAMETERS shows the current NLS parameters of the instance, that is, NLS parameters read from the initialization parameter file at instance startup. The view shows only parameters that were explicitly set.

NLS_DATABASE_PARAMETERS shows the current NLS parameters of the database, including the database character set.

V$NLS_VALID_VALUES can be used to see which language, territory, linguistic and character set definitions are supported by the server.

OCI Functions

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

Language and Territory Parameters

NLS_LANGUAGE Parameter type: String
Parameter scope: Initialization Parameter and ALTER SESSION
Default value: Derived from NLS_LANG
Range of values: Any valid language name

NLS_LANGUAGE specifies the default conventions for the following session characteristics:

• Language for server messages
• Language for day and month names and their abbreviations (specified in the SQL functions TO_CHAR and TO_DATE)
• Symbols for equivalents of AM, PM, AD, and BC. (A.M., P.M., A.D., and B.C. are only valid 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

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 $ORACLE_HOME/product_name/mesg directory, or the equivalent. Multiple versions of these files can exist, one for each supported language, using the filename convention:

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

ALTER SESSION SET NLS_LANGUAGE=Italian;
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

Parameter type: String
Parameter scope: Dynamic, Initialization Parameter, ALTER SESSION, and ALTER SYSTEM
Default value: BYTE
Range of values: BYTE | CHAR

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

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

NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary will always use byte semantics.

NLS_TERRITORY

Parameter type: String
Parameter scope: Initialization Parameter and ALTER SESSION
Default value: Derived from NLS_LANG
Range of values: Any valid territory name

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

• Date format
• Decimal character and group separator
• Local currency symbol
• ISO currency symbol
• Dual currency symbol
• Week start day
• Credit and debit symbol
• ISO week flag
• List separator

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 parameter 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 value 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. Modification of NLS_TERRITORY resets all derived NLS session parameters to default values for the new territory.

To change the territory dynamically to France, issue the following statement:

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

ALTER SESSION SET NLS_TERRITORY = Germany;
Session altered.

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

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

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

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

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

ALTER SESSION

% 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

ALTER SESSION SET NLS_LANGUAGE = German
  NLS_DATE_FORMAT = 'DD.MON.YY'
  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

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

• Messages from the server
• Messages and boilerplate text generated by Oracle Forms
• Messages and boilerplate text defined as part of the application