1

I have the following problem on WINDOWS (Italian):

ORACLE DB NLS_PARAMETERS

my NLS_LANG parameter is: ITALIAN_ITALY.UTF8

i want to execute the following query:

INSERT INTO SCHEMA.MY_TABLE("NAME") VALUES('ò');

Doing it by using command line (pure sqlplus) stores invalid data inside DB.

Doing it by using SQLDEVELOPER stores correct data.

I cannot find any way to set this stuff correctly, what should I do? Using SQLPLUS from command line is required.

Any help is appreciated.

Lazarus
  • 147
  • 13

1 Answers1

2

When you use sqlplus then it inherits the character set from command line window. You can interrogate and modify character set (aka encoding) with chcp, I assume it is CP850 - which is not UTF8.

Run chcp 65001 before you start sqlplus, then it should work. See also Converting German special characters to English equivalent one in Oracle SQL / PL-SQL or to read more details OdbcConnection returning Chinese Characters as "?"

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Thank you very much for you answer... i will check it out asap. Is there a way to do the same on a ssh terminal which connects to a unix system? – Lazarus Jan 10 '20 at 19:15
  • I don't know whether you can change the encoding of the Unix terminal. Check it with `locale charmap` or `echo $LANG` and set `NLS_LANG` accordingly. – Wernfried Domscheit Jan 10 '20 at 19:25
  • Thank you... by the way, on Windows cmd the 'ò' character is displayed well when it is typed on sqlplus... it just ends with wrong characters on db. Could this be related with the wrong code page? – Lazarus Jan 10 '20 at 19:37
  • Yes, you use encoding CP850 but towards Oracle you declare UTF-8. This does not match. Btw, SQL Developer is Java JDBC based which does not use `NLS_LANG` settings at all. – Wernfried Domscheit Jan 10 '20 at 19:45
  • So the solution can be to set cmd with utf-8 OR oracle with CP850... is there any public mapping between the code page cose and the NLS_LANG? – Lazarus Jan 10 '20 at 19:48
  • Correct, you can use either way. Windows Code Page Identifiers you can find here: [Code Page Identifiers](https://learn.microsoft.com/de-de/windows/win32/intl/code-page-identifiers) Corresponding Oracle Character sets you can get with this query: `SELECT VALUE AS ORACLE_CHARSET, UTL_I18N.MAP_CHARSET(VALUE) AS IANA_NAME FROM V$NLS_VALID_VALUES WHERE PARAMETER = 'CHARACTERSET';` – Wernfried Domscheit Jan 11 '20 at 19:05
  • Everything worked specially for your suggestion about the mappings!!! Thanks – Lazarus Jan 14 '20 at 13:46