Oracle NLS_LANG Character Encoding Configuration¶

The NLS_LANG environment variable is a critical setting for ensuring correct character encoding display in Oracle database interactions.^[600-developer-database-oracle-oracle-record.md]

Diagnosis¶

To diagnose character encoding issues, such as garbled text (mojibake) in [[SQL]] clients like PL/SQL Developer, one can query the database parameters.^[600-developer-database-oracle-oracle-record.md]

The following queries allow you to inspect the current NLS (National Language Support) configuration:

Global Parameters: Retrieve a comprehensive list of NLS settings.
```
select * from v$nls_parameters;
```
Session Language: Check the specific language and territory settings for the current session.
```
select userenv('language') from dual;
```

Configuration and Resolution¶

Fixing Character Encoding¶

Mismatched encoding between the client and the database is a common cause of corruption.^[600-developer-database-oracle-oracle-record.md] For example, if the database uses UTF-8, setting the client to AMERICAN_AMERICA.AL32UTF8 ensures proper character mapping.^[600-developer-database-oracle-oracle-record.md]

On Windows systems, this is typically resolved by setting the environment variable before launching the database client executable.^[600-developer-database-oracle-oracle-record.md]

Example (Windows Batch Script):

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
START plsqldev.exe

Fixing Date Formatting¶

Incorrect encoding can also affect how date and time values are displayed (e.g., showing unrecognizable characters instead of the date).^[600-developer-database-oracle-oracle-record.md] This can be corrected at the session level using ALTER SESSION commands.^[600-developer-database-oracle-oracle-record.md]

Example (Session Configuration):

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS:FF6';

Sources¶

600-developer-database-oracle-oracle-record.md