NLS Parameters Display (nls_parms.sql)

What This Script Does

Displays National Language Support (NLS) parameters at three different levels: session, instance, and database. This comprehensive view helps understand how NLS settings are configured and inherited across different scopes.

The Script

rem  nls_parms.sql
rem
ttitle 'NLS Parameters - All Levels'
rem
col parameter format a30
col value format a30
rem
SELECT '-- SESSION --' PARAMETER, '' VALUE FROM DUAL
UNION
SELECT Parameter
     , Value
  FROM NLS_SESSION_PARAMETERS
 ORDER BY 1
/
rem
SELECT '-- INSTANCE --' PARAMETER, '' VALUE FROM DUAL
UNION
SELECT Parameter
     , Value
  FROM NLS_INSTANCE_PARAMETERS
 ORDER BY 1
/
rem
SELECT '-- DATABASE --' PARAMETER, '' VALUE FROM DUAL
UNION
SELECT Parameter
     , Value
  FROM NLS_DATABASE_PARAMETERS
 ORDER BY 1
/

Usage

-- Display all NLS parameters at all levels
@nls_parms.sql

Parameters

The script requires no input parameters and displays all NLS settings.

Required Privileges

-- Usually available to all users
SELECT on NLS_SESSION_PARAMETERS
SELECT on NLS_INSTANCE_PARAMETERS
SELECT on NLS_DATABASE_PARAMETERS

Sample Output

                                NLS Parameters - All Levels

PARAMETER                      VALUE
------------------------------ ------------------------------
-- SESSION --
NLS_CALENDAR                   GREGORIAN
NLS_COMP                       BINARY
NLS_CURRENCY                   $
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_DUAL_CURRENCY              $
NLS_ISO_CURRENCY               AMERICA
NLS_LANGUAGE                   AMERICAN
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NUMERIC_CHARACTERS         .,
NLS_SORT                       BINARY
NLS_TERRITORY                  AMERICA
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR

-- INSTANCE --
[Instance-level parameters...]

-- DATABASE --
[Database-level parameters...]

Key NLS Parameters

Date and Time Formatting

NLS_DATE_FORMAT: Default date display format
NLS_TIMESTAMP_FORMAT: Timestamp display format
NLS_TIME_FORMAT: Time display format
NLS_CALENDAR: Calendar system (Gregorian, Japanese, etc.)

Language and Territory

NLS_LANGUAGE: Default language for messages and sorting
NLS_TERRITORY: Territory for number and date conventions
NLS_DATE_LANGUAGE: Language for day/month names

Character and Numeric

NLS_LENGTH_SEMANTICS: BYTE or CHAR length semantics
NLS_NUMERIC_CHARACTERS: Decimal and group separators
NLS_CURRENCY: Currency symbol
NLS_ISO_CURRENCY: ISO currency code

Sorting and Comparison

NLS_SORT: Sorting method for character data
NLS_COMP: String comparison method

Parameter Hierarchy

Precedence Order (highest to lowest)

Session Level: ALTER SESSION SET commands
Instance Level: Initialization parameters (spfile/pfile)
Database Level: Database creation defaults

Understanding Inheritance

Session parameters override instance parameters
Instance parameters override database parameters
Explicit session settings take highest precedence

Common Use Cases

Globalization Troubleshooting

-- Check NLS settings when data appears incorrectly
@nls_parms.sql
-- Compare session vs. instance settings

Application Development

-- Verify NLS settings for international applications
@nls_parms.sql
-- Ensure consistent formatting across environments

Date Format Issues

-- Investigate date display problems
@nls_parms.sql
-- Check NLS_DATE_FORMAT and NLS_DATE_LANGUAGE

Multi-Language Support

-- Verify language and territory settings
@nls_parms.sql
-- Ensure proper character set configuration

Migration and Deployment

-- Compare NLS settings between environments
@nls_parms.sql
-- Ensure consistent configuration across systems

Level-Specific Analysis

Session Level

Current session’s effective settings
May be modified by ALTER SESSION commands
Reflects user-specific or application-specific changes

Instance Level

Settings from initialization parameters
Apply to all new sessions by default
Set via spfile/pfile or ALTER SYSTEM

Database Level

Default settings established at database creation
Baseline configuration for the database
Cannot be changed after database creation

Common Issues and Solutions

Date Format Inconsistencies

-- Check if session format differs from instance
-- Look for NLS_DATE_FORMAT differences between levels

Character Length Issues

-- Verify NLS_LENGTH_SEMANTICS setting
-- BYTE vs. CHAR can affect VARCHAR2 column behavior

Sorting Problems

-- Check NLS_SORT parameter
-- Different sorting methods affect ORDER BY results

Currency Display Issues

-- Verify NLS_CURRENCY and NLS_ISO_CURRENCY
-- Ensure proper currency symbol display

Performance Impact

Minimal: Simple queries on system views
Read-Only: No modifications to settings
Fast Execution: Instantaneous results

nls_session_parameters - Session-only NLS parameters
Database configuration and setup scripts
Application deployment verification scripts

Best Practices

Environment Consistency

Maintain consistent NLS settings across environments
Document any intentional differences
Test applications with various NLS configurations