Oracle's National Language Support (NLS) is a comprehensive globalization framework that enables Oracle Database to store, process, and retrieve data in native languages and formats. The NLS architecture provides automatic adaptation of database behavior according to preconfigured language and locale parameters, ensuring applications can operate seamlessly across different cultural and linguistic environments.

NLS affects multiple aspects of database operations including data storage character sets, display formats for dates and numbers, sorting algorithms, error message languages, and calendar systems. The framework operates at three distinct levels: database, instance, and session, with session-level settings taking precedence over instance settings, which in turn override database-level defaults.

Key NLS parameters include:

• NLS_LANGUAGE: Determines the language for error messages, day and month names, and default sorting rules.
  
• NLS_TERRITORY: Specifies default date format, decimal character, group separator, and currency symbol based on geographic location.
  
  
• NLS_CURRENCY: Defines the local currency symbol used in number formatting.
  
• NLS_DUAL_CURRENCY: Specifies the dual currency symbol for euro currency support.
  
• NLS_ISO_CURRENCY: Sets the ISO currency code used in number formatting.
  
  
• NLS_NUMERIC_CHARACTERS: Defines decimal and group separator characters for numeric display.
  
  
• NLS_LENGTH_SEMANTICS { 'BYTE' | 'CHAR' }: Controls whether the length of CHAR, VARCHAR2, and LONG columns is defined in bytes or characters. This is particularly important for multibyte character sets where a single character may occupy multiple bytes.
  
• NLS_CHARACTERSET: Specifies the character encoding used for data stored in CHAR, VARCHAR2, CLOB, and LONG columns. This is set at database creation and cannot be changed afterward.
  
• NLS_NCHAR_CHARACTERSET: Defines the character encoding for national character datatypes NCHAR, NVARCHAR2, and NCLOB. Commonly set to AL16UTF16 or UTF8.
  
  
• NLS_NCHAR_CONV_EXCP: { FALSE | TRUE }: Determines whether data conversion between CHAR and NCHAR datatypes raises an exception when data loss might occur.
  
  
• NLS_CALENDAR: Specifies the calendar system used for date calculations and formatting: {'Arabic Hijrah' | 'English Hijrah' | 'Gregorian' | 'Japanese Imperial' | 'Persian' | 'ROC Official (Republic of China)' | 'Thai Buddha' }
  
• NLS_DATE_LANGUAGE: Controls the language used for day and month names in date formatting.
  
• NLS_DATE_FORMAT: Sets the default format mask for DATE datatype display and input.
  
• NLS_TIMESTAMP_FORMAT: Defines the default format for TIMESTAMP datatypes.
  
• NLS_TIMESTAMP_TZ_FORMAT: Specifies the format for TIMESTAMP WITH TIME ZONE datatypes.
  
  
• NLS_SORT: { BINARY | linguistic_definition }: Determines the collation sequence for character data sorting. BINARY provides fastest performance, while linguistic definitions support language-specific sorting rules.
  
• NLS_COMP: { BINARY | LINGUISTIC | ANSI }: Controls comparison semantics for character data in WHERE clauses and comparison operations.

The V$NLS_VALID_VALUES dynamic performance view provides a comprehensive list of valid values for specific NLS parameters. This view is essential to verify supported configurations before implementing NLS changes.

To see which NLS parameters have predefined valid values, query the distinct parameter names:

SELECT DISTINCT parameter FROM v$nls_valid_values;

Query result:

| PARAMETER    |
+--------------+
| CHARACTERSET |
| LANGUAGE     |
| SORT         |
| TERRITORY    |

Each parameter in this view represents an NLS setting with a finite set of supported values. The CHARACTERSET parameter shows available character encodings, LANGUAGE lists supported languages, SORT displays available linguistic sorting definitions, and TERRITORY contains valid territory specifications.

To retrieve all possible values for a specific parameter, use a filtered query. For example, to see available character sets:

SELECT * FROM v$nls_valid_values WHERE parameter = 'CHARACTERSET';

Query result:

| PARAMETER    | VALUE     |
---------------+-----------|
| CHARACTERSET | US7ASCII  |
| CHARACTERSET | UTF8      |
| CHARACTERSET | AL16UTF16 |
...

This information is particularly important when troubleshooting character set related issues.

The NLS_DATABASE_PARAMETERS view displays the NLS parameter values that were established when the database was created. These settings serve as the foundation for all NLS behavior and can only be changed through database recreation or specific administrative procedures for certain parameters.

Database-level NLS parameters are stored in the data dictionary and remain constant throughout the database lifecycle unless explicitly modified through supported Oracle procedures. These values act as defaults when instance or session-level parameters are not explicitly set.

SELECT parameter, value FROM nls_database_parameters;

Query result:

| PARAMETER               | VALUE                            |
--------------------------+----------------------------------+
| NLS_LANGUAGE            | AMERICAN                         |
| NLS_TERRITORY           | AMERICA                          |
|                                                            |
| NLS_CURRENCY            | $                                |
| NLS_DUAL_CURRENCY       | $                                |
| NLS_ISO_CURRENCY        | AMERICA                          |
|                                                            |
| NLS_NUMERIC_CHARACTERS  | '.,'                             |
|                                                            |
| NLS_LENGTH_SEMANTICS    | BYTE                             |
| NLS_CHARACTERSET        |                                  |
| NLS_NCHAR_CHARACTERSET  |                                  |
|                                                            |
| NLS_NCHAR_CONV_EXCP     | FALSE                            |
|                                                            |
| NLS_CALENDAR            | Gregorian                        |
| NLS_DATE_LANGUAGE       | AMERICAN                         |
| NLS_DATE_FORMAT         | 'YYYY-MM-DD'                     |
| NLS_TIMESTAMP_FORMAT    | 'YYYY-MM-DD HH:MI:SS.FF'         |
| NLS_TIMESTAMP_TZ_FORMAT | 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM' |
|                                                            |
| NLS_SORT                | BINARY                           |
| NLS_COMP                | LINGUISTIC                       |

Note that character set parameters (NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET) are fundamental database properties that cannot be easily changed after database creation.

The NLS_INSTANCE_PARAMETERS view shows NLS parameter values that are set at the database instance level. These parameters can be configured in the initialization parameter file (SPFILE or PFILE) and take effect when the instance starts. Instance-level settings override database-level defaults but can be superseded by session-level modifications.

Instance parameters provide a way to establish consistent NLS behavior across all sessions connecting to the database while still allowing individual sessions to override settings as needed. Changes to instance-level NLS parameters typically require an instance restart to take effect.

SELECT parameter, value FROM nls_instance_parameters;

Query result:

| PARAMETER               | VALUE                            |
--------------------------+----------------------------------+
| NLS_LANGUAGE            | AMERICAN                         |
| NLS_TERRITORY           | AMERICA                          |
|                                                            |
| NLS_CURRENCY            | $                                |
| NLS_DUAL_CURRENCY       | $                                |
| NLS_ISO_CURRENCY        | AMERICA                          |
|                                                            |
| NLS_NUMERIC_CHARACTERS  | '.,'                             |
|                                                            |
| NLS_LENGTH_SEMANTICS    | BYTE                             |
|                                                            |
| NLS_NCHAR_CONV_EXCP     | FALSE                            |
|                                                            |
| NLS_CALENDAR            | Gregorian                        |
| NLS_DATE_LANGUAGE       | AMERICAN                         |
| NLS_DATE_FORMAT         | 'YYYY-MM-DD'                     |
| NLS_TIMESTAMP_FORMAT    | 'YYYY-MM-DD HH:MI:SS.FF'         |
| NLS_TIMESTAMP_TZ_FORMAT | 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM' |
|                                                            |
| NLS_SORT                | BINARY                           |
| NLS_COMP                | LINGUISTIC                       |

Instance-level NLS parameters are particularly useful in environments where multiple applications with different localization requirements connect to the same database, as they provide a consistent baseline while allowing application-specific customization at the session level.

The NLS_SESSION_PARAMETERS view displays the current NLS parameter values for your active database session. These are the actual values that Oracle uses for formatting, sorting, and processing data within your session. Session-level parameters take the highest precedence in Oracle's NLS hierarchy and can be dynamically modified without affecting other database sessions.

Session parameters can be inherited from instance settings, set through client environment variables (such as NLS_LANG), or explicitly modified using the ALTER SESSION statement. This flexibility allows applications to customize their locale behavior without impacting other users or requiring database-wide changes.

SELECT parameter, value FROM nls_session_parameters;

Query result:

| PARAMETER               | VALUE                            |
--------------------------+----------------------------------+
| NLS_LANGUAGE            | AMERICAN                         |
| NLS_TERRITORY           | AMERICA                          |
|                                                            |
| NLS_CURRENCY            | $                                |
| NLS_DUAL_CURRENCY       | $                                |
| NLS_ISO_CURRENCY        | AMERICA                          |
|                                                            |
| NLS_NUMERIC_CHARACTERS  | '.,'                             |
|                                                            |
| NLS_LENGTH_SEMANTICS    | BYTE                             |
|                                                            |
| NLS_NCHAR_CONV_EXCP     | FALSE                            |
|                                                            |
| NLS_CALENDAR            | Gregorian                        |
| NLS_DATE_LANGUAGE       | AMERICAN                         |
| NLS_DATE_FORMAT         | 'YYYY-MM-DD'                     |
| NLS_TIMESTAMP_FORMAT    | 'YYYY-MM-DD HH:MI:SS.FF'         |
| NLS_TIMESTAMP_TZ_FORMAT | 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM' |
|                                                            |
| NLS_SORT                | BINARY                           |
| NLS_COMP                | LINGUISTIC                       |

Session-level NLS parameters can be modified using the ALTER SESSION command, which immediately changes the behavior for the current session without requiring reconnection. These changes remain in effect until the session ends or the parameter is explicitly changed again.

For example, to change the sorting behavior to use binary comparison for improved performance:

ALTER SESSION SET NLS_SORT = BINARY;