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



























Setting NLS Parameters


Previous Chapter | Next Chapter

NLS parameters determine the locale-specific behavior on both the client and the server. NLS parameters can be specified in the following ways:

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

  • In addition, NLS_LENGTH_SEMANTICS and NLS_CONV_EXCP can be set using the ALTER SYSTEM statement.

    As environment variables on the client. You can use NLS parameters to specify locale-dependent behavior for the client, and also to override the default values set for the session in the initialization parameter file. For example, on a UNIX system:

    % setenv NLS_SORT FRENCH

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

    ALTER SESSION SET NLS_SORT = FRENCH;

  • As SQL function parameters. NLS parameters can be used explicitly to hardcode NLS behavior within a SQL function. Doing so will override 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 database character set and the national character set are specified in the CREATE DATABASE statement.

Following list 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. Explicitly setting an NLS parameter within a SQL function overrides all other settings -- default, initialization parameter, environment variable, and ALTER SESSION parameters.

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

Following Table lists the NLS parameters available with the Oracle server.

Parameter  Description  Default  Scope: I= INIT.ORA,
E= Environment Variable,
A= Alter Session
 

NLS_CALENDAR 

Calendar system 

Gregorian 

I, E, A 

NLS_COMP  

SQL, PL/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_LENGTH_SEMANTICS  

How strings are treated 

Byte 

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_CONV_EXCP  

Reports data loss during a character type conversion 

 

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

Timestamp 

NLS_TERRITORY  

I, E, A 

NLS_TIMESTAMP_TZ_FORMAT  

Timestamp with Timezone 

NLS_TERRITORY  

I, E, A 

NLS_DUAL_CURRENCY  

Dual currency symbol 

NLS_TERRITORY  

I, E, A

Choosing a Locale with the NLS_LANG Initialization Parameter
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, which is the character set of data entered or displayed by a client program.

The NLS_LANG parameter has three components (language, territory, and character set) 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, 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 language is not specified, the value defaults to AMERICAN.

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

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.

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 can be set but will not work properly. For example, the following specification tries to support Japanese by using a Western European character set:

NLS_LANG = JAPANESE_JAPAN.WE8DEC

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

Specifying NLS_LANG as an Environment Variable
You can set NLS_LANG as an environment variable at the command line. For example, on UNIX, you can specify the value of NLS_LANG by entering a statement similar to the following:

. % 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 the NLS_LANG parameter. % 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, then the same query returns the following information:

% 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
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 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 the NLS_LANG parameter set the default values for the other NLS parameters, such as date format, numeric characters, and linguistic sorting. Each of these detailed parameters can be set in the client environment to customize the language and territory values.

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

If the NLS_LANG parameter 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 retrieves new settings; Oracle Developer is an example of an application that does not retrieve new settings.

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.

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