My Oracle Support Banner

The Priority of NLS Parameters Explained (Where To Define NLS Parameters) (Doc ID 241047.1)

Last updated on AUGUST 09, 2021

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

Purpose

This note explains the order in which NLS parameters are taken into account in the database client/server model using a standard client connection. i.e NLS_LANG

Scope

This is relevant from Oracle 8.0 Onward and intended for Oracle DBAs and support analysts.
 

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Scope
Details
 Introduction
 A) The Session Parameters - NLS_SESSION_PARAMETERS.
 A1) Using alter session .
 A2) Using NLS_LANG on the client side
 A2a) NLS_LANG is specified with only the <NLS_Territory> part.
 A2b) NLS_LANG is specified with only the <NLS_Language> part
 A2c) NLS_LANG is specified with only the <clients characterset>
 A2d) Setting other NLS parameters on the client side
 A2e) Defaults values for settings in NLS_SESSION_PARAMETERS:
 A2f) If NLS_LANG is not set
 A3) Other things who are good to know:
 B) The Instance Parameters - NLS_INSTANCE_PARAMETERS.
 C) The Database Parameters - NLS_DATABASE_PARAMETERS.
 D) Other views containing NLS information:
 D1) sys.props$
 D2) v$nls_parameters
 D3) userenv language / sys_context language
 D4) userenv ('lang')
 D5) show parameter NLS%
 D6) v$parameter
 D7) v$system_parameter
 D8) database_properties
 E) Other parameters with "NLS" in the name:
 F) What are valid values to use for NLS parameters?
 G) I'm the dba and have been asked to set a NLS parameter ( NLS_DATE_FORMAT etc) "on database" level.
 H) NLS parameters when using JDBC
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.