Oracle Client_charset

Question

I am using 19c client and my database's NLS parameters like these:

Also my clients specs are:

While (Windows 10 x64) I am using sqplus, I get this(you can see my NLS_LANG environment variable on the top of command line):

My 19c client home regedit NLS_LANG variable also is set to AMERICAN_AMERICA.W8ISO8859P9.

Yet, when I using TOAD for Oracle:

and using SQL Developer:

I got confused. On the internet they say NLS_LANG environment variable should be enough for setting client character set, but clearly it isn' t.

Due to this configuration difference, I am seeing "fıtıkçışahap"(on sqlplus) as "fıtıkçışahap"(on SQL Developer and TOAD for Oracle)

How can I overcome this situation?

Thanks in advance!

Edit:

V$NLS_PARAMETERS

Answer 1

My PostgreSQL database' s encoding was tr_TR.UTF8, so still UTF8. It should have been LATIN that support Turkish character set. Changing it to tr_TR.iso8859 solved my problem.

Answer 2

Forget about SELECT CLIENT_CHARSET FROM V$SESSION_CONNECT_INFO, it does not mean anything.

SQL Developer is Java/JDBC based. Starting from Oracle Database 10g, the NLS_LANG variable is no longer part of the JDBC globalization mechanism. The JDBC driver does not check NLS environment. So, setting it has no effect.

Your NLS_LANG variable is set to AMERICAN_AMERICA.W8ISO8859P9 - what does it mean in terms of character set?

You tell the Oracle database: "my client uses character set W8ISO8859P9" (i.e. ISO-8859-9) - no more, no less!

When you run SQL*Plus then it inherits the character set from the command line codepage. You can interrogate and modify the codepage with command chcp. I assume it is either CP857 (if you run a Turkish Windows) or CP850 or CP437 (see National Language Support (NLS) API Reference). Non of these three codepages matches ISO-8859-9, so your output is gibberish.

Before you run SQL*Plus set the codepage accordingly, i.e. chcp 28599 (see Code Page Identifiers)

Alternatively set the NLS_LANG according to your codepage, e.g. AMERICAN_AMERICA.TR8PC857 (check with SELECT VALUE AS ORACLE_CHARSET, UTL_I18N.MAP_CHARSET(VALUE) AS IANA_NAME FROM V$NLS_VALID_VALUES WHERE PARAMETER = 'CHARACTERSET')

Usually TOAD handles the character set very smart, so I fear the gibberish you see in TOAD is the real data in your database, i.e. it is junk because you had the wrong NLS_LANG value when the data was inserted.

See also OdbcConnection returning Chinese Characters as "?"