Sqlplus not using NLS_LANG env variable

Question

I am trying to login to an Oracle 10g database vía sqlplus.

In the nls_session_parameters I need NLS_DATE_LANGUAGE in American, but I always have Spanish.

In the database server my NLS_LANG is SPANISH_SPAIN.WE8ISO8859P1.

In the client my NLS_LANG is AMERICAN_AMERICA.UTF8.

I tried setting this with an after logon trigger, but it does not work. If I change this setting vía ALTER SESSION it works ok, but it is not a solution.

The database server is a Red Hat Enterprise 5.6 The client is a CentOS 7.5. I am using Oracle Instant client 10.2.0.5.

I do not know how to continue...

• Wernfried Domscheit: NLS_LANG exist only on your client. There is no NLS_LANG on database server side.
• Wernfried Domscheit: If your NLS_LANG is "AMERICAN_AMERICA.UTF8" then you also should get Spanish date names. Verify carefully if environment is properly set and exported. Is there any startup script login.sql or glogin.sql?

Answer 1

I am presuming you are setting it to AMERICAN_AMERICA.* with your ALTER SESSION.

In my experience the language setting has to match between the client, server and nls_database_parameters. It might be a big mission for your DBA to convert your character set to AMERICAN_AMERICA.UTF8 using the Database Migration tool, so would it be possible to make your change to NLS_LANG=SPANISH_SPAIN.WE8ISO8859P1 on the server?

Another problem is that WE8ISO8859P1 is a subset of UTF8 so when your RDBMS does its single-pass (or more) conversions when transporting data between the layers, you can get lossy results and see strange characters returning to your client. So you would be better off to set your client and server locale to WE8ISO8859P1 to match your nls_database_parameters on the database.