Different ways to set up NLS parameters

The word NLS means National Language Support. The NLS_* parameters determine the
locale-specific behavior on both the client and the server; where * of NLS_* is for various strings which make various NLS parameters.

There are many NLS_* parameters like NLS_SORT, NLS_LANGUAGE, NLS_CHARACTERSET, NLS_DATE_LANGUAGE etc. In this post I will show how the NLS parameters can be set based on their setting of priority.

1)In SQL functions:
If you set NLS_* parameters inside SQL functions then that setting has the highest priority.

You can set in SQL functions like,
TO_CHAR(sysdate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')

Below is an example. Note that in my client machine FRENCH language is not installed so it might not display properly.

SQL> select sysdate from dual;

SYSDATE
---------
07-FEB-09

SQL> select TO_CHAR(sysdate, 'DD/MON/YYYY', 'nls_date_language = FRENCH') from dual;

TO_CHAR(SYSDA
-------------
07/F╔VR./2009

Setting in this way (inside sql functions) 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.

2)With the ALTER SESSION statement:
Setting through ALTER SESSION parameter has the second highest priority. Setting by an ALTER SESSION statement override the default values that are set for the session in the initialization parameter file or set by the client with environment variables.

Below is an example. As in my client machine Japanese language is not installed so displaying in Japanese character might not work properly.

SQL> select sysdate from dual;

SYSDATE
---------
07-FEB-09

SQL> alter session set NLS_DATE_LANGUAGE=JAPANESE;

Session altered.

SQL> select sysdate from dual;

SYSDATE
----------
07-2┐ -09

3)Through Environmental variable on the client machine:
This setting has the third highest priority. Through OS environmental variable you can set NLS_* parameters. Setting of environmental variable is platform specific. On windows machine you can set by,
C:>set NLS_*=value;
On unix machine
$export NLS_*=value; (bash shell)
$setenv NLS_*=value; (c shell)

Below is an example on my windows client machine.
C:\>set NLS_SORT=FRENCH

4)As initialization parameters on the server:
You can set the NLS_* parameters in the server machine inside the initialization parameter file. Setting in the initialization parameter specify a default session NLS environment. Setting in this way has no effect on the client side, they control only the server's behavior.
For example, if you use spfile then you can set NLS_TERRITORY parameter by below,

SQL> ALTER SYSTEM SET NLS_TERRITORY = "CZECH REPUBLIC" scope=spfile;

System altered.
Then in order to effect bounce database.

If I draw a table based on priority and ways to do then it will be like,

Priority Ways to do the task.
----------- -----------------------------------------
1 (highest) Set in SQL functions
2 Set by an ALTER SESSION statement
3 Set as an environment variable
4 Specified in the initialization parameter file
5 (lowest) Default