In Oracle Database, the NLS (National Language Support) parameters determine the locale-specific behaviour on both the client and the server.
These parameters can be set in various places, such as at the database level, in an initialization parameter file, in environment variables, at the session level, and even within some functions.
You can check the value of the NLS parameters with the following views.
NLS Dynamic Performance Views
Dynamic performance views are special views that are continuously updated while a database is open and in use. These views contain data about a large number of things, including memory usage and allocation, file states, progress of jobs and tasks, statistics and metrics, SQL execution, as well as system and session parameters.
The following dynamic performance views can be used to return information about NLS parameters:
V$NLS_PARAMETERSshows current values of various NLS parameters.V$NLS_VALID_VALUESlists the valid values that various NLS parameters can be set to.
Here are examples of each view.
The V$NLS_PARAMETERS View
Here’s an example of querying the V$NLS_PARAMETERS view on my system:
SELECT * FROM V$NLS_PARAMETERS;Result:
PARAMETER VALUE CON_ID __________________________ _________________________________ _________ NLS_LANGUAGE ENGLISH 3 NLS_TERRITORY AUSTRALIA 3 NLS_CURRENCY $ 3 NLS_ISO_CURRENCY AUSTRALIA 3 NLS_NUMERIC_CHARACTERS ., 3 NLS_CALENDAR GREGORIAN 3 NLS_DATE_FORMAT DD/MON/RR 3 NLS_DATE_LANGUAGE ENGLISH 3 NLS_CHARACTERSET AL32UTF8 3 NLS_SORT BINARY 3 NLS_TIME_FORMAT HH12:MI:SSXFF AM 3 NLS_TIMESTAMP_FORMAT DD/MON/RR HH12:MI:SSXFF AM 3 NLS_TIME_TZ_FORMAT HH12:MI:SSXFF AM TZR 3 NLS_TIMESTAMP_TZ_FORMAT DD/MON/RR HH12:MI:SSXFF AM TZR 3 NLS_DUAL_CURRENCY $ 3 NLS_NCHAR_CHARACTERSET AL16UTF16 3 NLS_COMP BINARY 3 NLS_LENGTH_SEMANTICS BYTE 3 NLS_NCHAR_CONV_EXCP FALSE 3
The V$NLS_VALID_VALUES View
The V$NLS_VALID_VALUES view lists the valid values that the following NLS parameters can be set to:
NLS_LANGUAGENLS_SORTNLS_TERRITORYNLS_CHARACTERSET
To give you an idea of the type of data contained in this view, here’s a query that returns the top 10 rows:
SELECT * FROM V$NLS_VALID_VALUES
FETCH FIRST 10 ROWS ONLY;Result:
PARAMETER VALUE ISDEPRECATED CON_ID ____________ __________________ _______________ _________ LANGUAGE AMERICAN FALSE 0 LANGUAGE GERMAN FALSE 0 LANGUAGE FRENCH FALSE 0 LANGUAGE CANADIAN FRENCH FALSE 0 LANGUAGE SPANISH FALSE 0 LANGUAGE ITALIAN FALSE 0 LANGUAGE DUTCH FALSE 0 LANGUAGE SWEDISH FALSE 0 LANGUAGE NORWEGIAN FALSE 0 LANGUAGE DANISH FALSE 0
So, these values can be used wherever an NLS parameter accepts a language value. For example, we could do the following:
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';Which sets the NLS_LANGUAGE parameter to AMERICAN. To set it to a different value, we can check the V$NLS_VALID_VALUES view to see which languages are accepted.
NLS Data Dictionary Views
You can check the session, instance, and database NLS parameters by querying the following data dictionary views:
NLS_SESSION_PARAMETERSshows the NLS parameters and their values for the session that is querying the view. It does not show information about the character set.NLS_INSTANCE_PARAMETERSshows the current NLS instance parameters that have been explicitly set and the values of the NLS instance parameters.NLS_DATABASE_PARAMETERSshows the values of the NLS parameters for the database. The values are stored in the database.
As mentioned, NLS parameters can be set in various places. Each scope can contain different values for a given NLS parameter than another scope. The above data dictionary views allow you to query each scope for its value. NLS session parameters override the NLS instance parameters. And the NLS instance parameters override the NLS parameters for the database.
See Methods of Setting NLS Parameters and their Priorities for a table outlining the precedence order each scope has in relation to the others.
Here’s what I get when I query each of these views on my system:
SELECT * FROM NLS_SESSION_PARAMETERS;
SELECT * FROM NLS_INSTANCE_PARAMETERS;
SELECT * FROM NLS_DATABASE_PARAMETERS;Result:
PARAMETER VALUE
__________________________ _________________________________
NLS_LANGUAGE ENGLISH
NLS_TERRITORY AUSTRALIA
NLS_CURRENCY $
NLS_ISO_CURRENCY AUSTRALIA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD/MON/RR
NLS_DATE_LANGUAGE ENGLISH
NLS_SORT BINARY
NLS_TIME_FORMAT HH12:MI:SSXFF AM
NLS_TIMESTAMP_FORMAT DD/MON/RR HH12:MI:SSXFF AM
NLS_TIME_TZ_FORMAT HH12:MI:SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD/MON/RR HH12:MI:SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
PARAMETER VALUE
__________________________ ___________
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
PARAMETER VALUE
__________________________ _______________________________
NLS_RDBMS_VERSION 19.0.0.0.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
20 rows selected.
Take the NLS_LANGUAGE parameter for example. This is set to AMERICAN at both the database and instance levels (as can be seen from the NLS_DATABASE_PARAMETERS and NLS_INSTANCWE_PARAMETERS views). But the session parameter (which can be seen in the NLS_SESSION_PARAMETERS view) uses ENGLISH, which overrides the other two settings.