learn-oracle
free Oracle DBA tutorial Oracle Jobs
Ask A Question
SQL Statement Tuning
Backup and Recovery Concepts
Oracle 11g New Features
Oracle E Suite & Others
Oracle Data Guard
Oracle DBA FAQ





Checking NLS Parameters


Previous Chapter | Next Chapter

You can find the values for NLS parameters in some views or by using an OCI function call.

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

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 and NLS_TERRITORY parameters are general NLS parameters that describe 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:

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

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

NLS_LENGTH_SEMANTICS

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

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

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

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

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:

  • Messages from the server
  • Messages and boilerplate text generated by Oracle Forms
  • Messages and boilerplate text defined as part of the application
The application is responsible for meeting the last requirement. NLS takes care of the other two.

Previous Chapter | Next Chapter

More Tutorials on Oracle dba ...

Source :Oracle Documentation

Liked it ? Want to share it ? Social Bookmarking

Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information




Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info