Search code examples
windowsoracleplsqlcharacter-encodingoracle-sqldeveloper

Oracle SQL Developer client encoding

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?

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.