Gen CSE "NLS_LANG characterset must match NLS_CHARACTERSET"

Products

• Gen

Issue/Introduction

AIX Gen 8.6  Client Server Encyclopedia (CSE) install/ configuration.
Created an Oracle 19c instance and database using an AL32UTF8 character set and NLS settings AMERICA/AMERICAN (NLS_LANGUAGE/NLS_TERRITORY).
After CSE confIguration, the CSE iefmd fails to start and the iefmd.log contains this message for every server start attempt:

DATABASE CONNECT ERROR: characterset inconsistency,
NLS_LANG characterset must match NLS_CHARACTERSET!
DBNAME=DB19C NLS_CHARACTERSET=AL32UTF8 NLS_LANG=AMERICAN

Environment

Gen Client Server Encyclopedia

Resolution

The Oracle NLS_LANG environment variable value (or NLS_LANG registry value on Windows) has the format:
[NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET]

The CSE is an Oracle client which handles its own character translation and so to avoid any Oracle character set translation between the CSE and Oracle database, the NLS_LANG must match the NLS settings that the database was created with which are stored in database view NLS_DATABASE_PARAMETERS.

Therefore for the user starting the CSE, the NLS_LANG environment variable must match the database NLS_DATABASE_PARAMETERS.
To resolve the problem for this scenario the user that starts the CSE iefmd should set:
NLS_LANG = AMERICAN_AMERICA.AL32UTF8

NOTE:
On a Unix CSE machine, it is required to set NLS_LANG as an environment variable.
On a Windows CSE machine, an Oracle client should normally read the NLS_LANG value from the registry. During the Oracle 32-bit client install the NLS_LANG value is set in the registry key for the Oracle client Home e.g.
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Oracle\KEY_OraClient19Home1_32bit
That NLS_LANG value needs to match the Database NLS_DATABASE_PARAMETERS.
Alternatively set NLS_LANG as an environment variable to override the registry value.

Additional Information

1. The fact that the CSE handles its own character translation is documented under Prerequisites for CSE Configuration section "Configure Databases" but was only previously there for the SQL Server DSN creation. It has now been expanded for the Oracle NLS_LANG environment variable.


2. To find all 3 values for NLS_LANGUAGE, NLS_TERRITORY, NLS_CHARACTERSET that the database was created with this SQL can be run against the NLS_DATABASE_PARAMETERS view:
SELECT DECODE(parameter, 'NLS_CHARACTERSET', 'CHARACTER SET',  'NLS_LANGUAGE', 'LANGUAGE', 'NLS_TERRITORY', 'TERRITORY') name, value from  
NLS_DATABASE_PARAMETERS WHERE parameter IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY')

Example output:
NAME                       VALUE
-------------                  ----------------------------------------------------------------
CHARACTER SET   AL32UTF8
TERRITORY             AMERICA
LANGUAGE             AMERICAN

So NLS_LANG = LANGUAGE_TERRITORY. CHARACTER_SET