NLS Settings

View All Scripts Login to Run Script

Script Name NLS Settings
Description using the National Language Setting (NLS) language and territory settings
Area NLS
Contributor Mike Hichwa (Oracle)
Created Monday October 05, 2015

Statement 1
Alter your current database session NLS Territory to France. This will cause a number of default setting to be set that are proper for France.
```
alter session set NLS_TERRITORY = "FRANCE"
```
```
Statement processed.
```
Statement 2
Setting your language to French will cause error messages to be displayed in French.
```
alter session set NLS_LANGUAGE = French
```
```
Statement processed.
```

Statement 3

Displays current settings from the Oracle Data Dictionary.

select * from NLS_SESSION_PARAMETERS

 
PARAMETER VALUE
NLS_LANGUAGE FRENCH
NLS_TERRITORY FRANCE
NLS_CURRENCY €
NLS_ISO_CURRENCY FRANCE
NLS_NUMERIC_CHARACTERS , 
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD/MM/RR
NLS_DATE_LANGUAGE FRENCH
NLS_SORT FRENCH
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY €
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 rows selected.

PARAMETER	VALUE
NLS_LANGUAGE	FRENCH
NLS_TERRITORY	FRANCE
NLS_CURRENCY	€
NLS_ISO_CURRENCY	FRANCE
NLS_NUMERIC_CHARACTERS	,
NLS_CALENDAR	GREGORIAN
NLS_DATE_FORMAT	DD/MM/RR
NLS_DATE_LANGUAGE	FRENCH
NLS_SORT	FRENCH
NLS_TIME_FORMAT	HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT	DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT	HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT	DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY	€
NLS_COMP	BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE

Statement 4

Dates and numbers are displayed using national language settings.

SELECT last_name, 
       hire_date, 
       to_char(ROUND(salary/8,2),'999G999G990D00') salary 
FROM hr.employees 
order by hire_date

 
LAST_NAME HIRE_DATE SALARY
OConnell 21/06/07          325,00
Grant 13/01/08          325,00
Whalen 17/09/03          550,00
Hartstein 17/02/04        1 625,00
Fay 17/08/05          750,00
Mavris 07/06/02          812,50
Baer 07/06/02        1 250,00
Higgins 07/06/02        1 501,00
Gietz 07/06/02        1 037,50
King 17/06/03        3 000,00
Kochhar 21/09/05        2 125,00
De Haan 13/01/01        2 125,00
Hunold 03/01/06        1 125,00
Ernst 21/05/07          750,00
Austin 25/06/05          600,00
Pataballa 05/02/06          600,00
Lorentz 07/02/07          525,00
Greenberg 17/08/02        1 501,00
Faviet 16/08/02        1 125,00
Chen 28/09/05        1 025,00
Sciarra 30/09/05          962,50
Urman 07/03/06          975,00
Popp 07/12/07          862,50
Raphaely 07/12/02        1 375,00
Khoo 18/05/03          387,50
Baida 24/12/05          362,50
Tobias 24/07/05          350,00
Himuro 15/11/06          325,00
Colmenares 10/08/07          312,50
Weiss 18/07/04        1 000,00
Fripp 10/04/05        1 025,00
Kaufling 01/05/03          987,50
Vollman 10/10/05          812,50
Mourgos 16/11/07          725,00
Nayer 16/07/05          400,00
Mikkilineni 28/09/06          337,50
Landry 14/01/07          300,00
Markle 08/03/08          275,00
Bissot 20/08/05          412,50
Atkinson 30/10/05          350,00
Marlow 16/02/05          312,50
Olson 10/04/07          262,50
Mallin 14/06/04          412,50
Rogers 26/08/06          362,50
Gee 12/12/07          300,00
Philtanker 06/02/08          275,00
Ladwig 14/07/03          450,00
Stiles 26/10/05          400,00
Seo 12/02/06          337,50
Patel 06/04/06          312,50
Rajs 17/10/03          437,50

Rows 1 - 50

LAST_NAME	HIRE_DATE	SALARY
OConnell	21/06/07	325,00
Grant	13/01/08	325,00
Whalen	17/09/03	550,00
Hartstein	17/02/04	1 625,00
Fay	17/08/05	750,00
Mavris	07/06/02	812,50
Baer	07/06/02	1 250,00
Higgins	07/06/02	1 501,00
Gietz	07/06/02	1 037,50
King	17/06/03	3 000,00
Kochhar	21/09/05	2 125,00
De Haan	13/01/01	2 125,00
Hunold	03/01/06	1 125,00
Ernst	21/05/07	750,00
Austin	25/06/05	600,00
Pataballa	05/02/06	600,00
Lorentz	07/02/07	525,00
Greenberg	17/08/02	1 501,00
Faviet	16/08/02	1 125,00
Chen	28/09/05	1 025,00
Sciarra	30/09/05	962,50
Urman	07/03/06	975,00
Popp	07/12/07	862,50
Raphaely	07/12/02	1 375,00
Khoo	18/05/03	387,50
Baida	24/12/05	362,50
Tobias	24/07/05	350,00
Himuro	15/11/06	325,00
Colmenares	10/08/07	312,50
Weiss	18/07/04	1 000,00
Fripp	10/04/05	1 025,00
Kaufling	01/05/03	987,50
Vollman	10/10/05	812,50
Mourgos	16/11/07	725,00
Nayer	16/07/05	400,00
Mikkilineni	28/09/06	337,50
Landry	14/01/07	300,00
Markle	08/03/08	275,00
Bissot	20/08/05	412,50
Atkinson	30/10/05	350,00
Marlow	16/02/05	312,50
Olson	10/04/07	262,50
Mallin	14/06/04	412,50
Rogers	26/08/06	362,50
Gee	12/12/07	300,00
Philtanker	06/02/08	275,00
Ladwig	14/07/03	450,00
Stiles	26/10/05	400,00
Seo	12/02/06	337,50
Patel	06/04/06	312,50
Rajs	17/10/03	437,50

Statement 5

select sysdate, systimestamp, to_char(sysdate,'Day') day, to_char(sysdate,'Month') month from dual

 
SYSDATE SYSTIMESTAMP DAY MONTH
02/06/14 02/06/14 05:18:41,684703 -07:00 Lundi   Juin

Statement 6
This statement is expected to fail to show the ability to change the language in which errors are displayed.
```
select * from does_not_exist
```
```
ORA-00942: Table ou vue inexistante
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

SYSDATE	SYSTIMESTAMP	DAY	MONTH
02/06/14	02/06/14 05:18:41,684703 -07:00	Lundi	Juin

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database