1

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

enter image description here

Also my clients specs are:

enter image description here

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):

enter image description here

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

Yet, when I using TOAD for Oracle:

enter image description here

and using SQL Developer:

enter image description here

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

enter image description here

Umut TEKİN
  • 856
  • 1
  • 9
  • 19
  • 3
    What is your database character set from `v$nls_parameters`? Are you querying a `varchar` or `nvarchar` column? What does `select dump(column_name)` show and what value are you expecting to be in that column? – Justin Cave Jan 23 '21 at 16:24
  • Thanks for your reply. Actually, data is coming from a PostgreSQL database. On Windows sqlplus the output is: select * from "test"@postgrelink; username fıtıkçışahap SQL> select dump("username") from "test"@postgrelink; DUMP("USERNAME") Typ=1 Len=17: 102,196,177,116,196,177,107,195,167,196,177,197,159,97,104,97,112 SQL> On SQL Developer and TOAD for Oracle output is same: Typ=1 Len=17: 102,196,177,116,196,177,107,195,167,196,177,197,159,97,104,97,112 – Umut TEKİN Jan 23 '21 at 16:35
  • Yet, presenting the type of output is different. sqlplus is giving the output, "fıtıkçışahap" and the others "fıtıkçışahap". The dump values of all of them are same. I edited the question with v$nls_parameters. Thanks :). – Umut TEKİN Jan 23 '21 at 16:42
  • 1
    How is the database link configured? What character set is the data stored in PostgreSQL? Can you use JDBC to connect directly to the PostgreSQL database in SQL Developer? What is the username supposed to be? Is the column a `varchar` or a `nvarchar`? I'm not seeing the `nls_characterset` in your output which is generally the key value. – Justin Cave Jan 23 '21 at 17:22
  • 2
    Funny, you show so much but the most important information you missed. What do you get from `SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET'`? – Wernfried Domscheit Jan 23 '21 at 18:42
  • Sorry about the missing part. I thought it was there. – Umut TEKİN Jan 23 '21 at 19:19
  • Thanks both of you. I just reviewed all of my system and error was in source database, PostgreSQL. It was configured as tr_TR.UTF8 encoding and other one en_US.UTF8 encoding. All those characters is stored as two byte in PostgreSQL(am I right?) and my WE8ISO8859P9 Turkish encoding Oracle database was trying to interpret them as one byte character(am I right?). Changing source encoding to LATIN5 encoding and tr_TR.iso8859 collate fixed my problem. Again, sorry about your time. But, you taught me dump, chcp, charmap and all other good stuff :). Thanks again! – Umut TEKİN Jan 23 '21 at 21:28
  • It would be much better if you provided your final comment above as an answer, and accept it, so others can benefit from it. Right now, that comment is not readily visible and the question looks like it's still open. – Sabuncu Jan 24 '21 at 14:51

2 Answers2

4

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

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Thanks for your reply first of all. I appreciate it. My Windows command line's code page is none of them that you guessed, it is "chcp -> Active code page: 65001(UTF-8)". However, ok SQL developer is not being effected by NLS environment variable, but what about TOAD. I am telling the TOAD that I am using my 19c client which is using NLS_LANG environment variable(picture 2). On TOAD CLIENT_CHARSET parameters is set UTF16. Thanks again for your time. – Umut TEKİN Jan 23 '21 at 19:29
  • 1
    Honestly I don't know how TOAD works in detail. Either it reads the `NLS_LANG` value before it establish the connection and sets his character set accordingly. Or it "pre-connect" to the database, reads the **database** character set, set his own character set accordingly and then makes the actual connection. I did not find out yet. – Wernfried Domscheit Jan 23 '21 at 21:21
0

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.

Umut TEKİN
  • 856
  • 1
  • 9
  • 19