Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 268
maestro Avatar asked Mar 21 '23 09:03

maestro


1 Answers

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.

like image 111
Mohsen Heydari Avatar answered Apr 01 '23 00:04

Mohsen Heydari