client - 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

question from:https://stackoverflow.com/questions/65861426/oracle-client-charset

Answer 1

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 "?"