Oracle SQL Developer client encoding
character-encoding, oracle, oracle-sqldeveloper, plsql, windows
Solution
On the PC that PLSQL is installed, set the value of `NLS_LANG` registery entry equal to the PC's `operation system locale (code page)`, equivalent value. How to detect operating system language locale? How to map OS locale to NLS_LANG value?
When using PLSQL the initial parameter of client-language that is required to create an Oracle session is read from NLS_LANG registry entry. Due to Oracle documents, invalid data usually occurs in a database because the NLS_LANG parameter is not set properly on the client. The NLS_LANG value should reflect the client operating system code page. For example, in an English Windows environment, the code page is WE8MSWIN1252. When the NLS_LANG parameter is set properly, the database can automatically convert incoming data from the client operating system to its encoding. When using JAVA method, the client-language parameter is set by the value from the Control Panel, under Regional and Language Options, so the things will be OK.
Problem
I read many of the related Stack Overflow's topics and I spent a whole day with googleing the following problem but I haven't found anything that would help, however the problem not seems to be complicated. I have an Oracle database. Let's see the following PL/SQL script: ``` CREATE TABLE Dummy( id number(19,0), tclob clob, tnclob nclob, PRIMARY KEY (id)); INSERT INTO dummy (id, tclob, tnclob) VALUES (1, 'ñ$ߤ*>;''<’', 'ñ$ߤ*>;''<’'); SELECT tclob, tnclob FROM dummy; ``` My problem is that 'ñ' and '’' characters are stored as a question mark. I also tried to load the previously inserted values through JAVA, but I get the question marks instead of the special characters. I created a small Java method which uses OraclePreparedStatement to save test data, and I use setNString() method to attach the nclob data to the query. In this case all characters are displayed fine in Java and also in SqlDeveloper. So a possible solution is to use JAVA to save my data into the db. I have a thousands of lines SQL script which inserts data and I don't necessarily want to write the whole thing again in java. So the question is: why the SqlDeveloper breaks the special characters? My settings: ``` SELECT DECODE(parameter, 'NLS_CHARACTERSET', 'CHARACTER SET', 'NLS_LANGUAGE', 'LANGUAGE', 'NLS_TERRITORY', 'TERRITORY') name, value from v$nls_parameters WHERE parameter IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY') ``` Result: ``` +---------------+--------------+ | NAME | VALUE | +---------------+--------------+ | LANGUAGE | HUNGARIAN | | TERRITORY | HUNGARY | | CHARACTER SET | EE8ISO8859P2 | +---------------+--------------+ ``` I changed `SqlDeveloper/Preferences/Environment/Encoding` to `UTF-8`. I also changed `HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1` value to `HUNGARIAN_HUNGARY.UTF8` Update: I tried to insert the data with the following syntaxes: ``` INSERT INTO dummy (id, tclob, tnclob) VALUES (1, N'ñ$ߤ*>;''<’', N'ñ$ߤ*>;''<’'); INSERT INTO dummy (id, tclob, tnclob) VALUES (1, 'ñ$ߤ*>;''<’', to_nclob('ñ$ߤ*>;''<’')); ``` Nothing helped. So what can I do?