4

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?

4
  • Could you show here a result of this query: select * from nls_database_parameters where parameter like '%SET' Commented Dec 6, 2013 at 19:54
  • The result of your query: NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_CHARACTERSET EE8ISO8859P2 Commented Dec 7, 2013 at 11:30
  • Can you try to close Oracle SQL Developer, then set NLS_LANG=HUNGARIAN_HUNGARY.UTF8 in the windows environment, and open SQL Developer and try again ? Commented Dec 7, 2013 at 12:07
  • I tried - i guess you mean that i should add an environment variable to the system. Well, i tried it,but it wasn"t help. Commented Dec 7, 2013 at 13:05

2 Answers 2

7

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.

Sign up to request clarification or add additional context in comments.

14 Comments

Thanks for your reply. Does it mean that I cannot change the encoding of the sqldeveloper to have the proper encoding?
Definitely you can. Just set the registry entry equal to your operating system local.For example if it is english-us set the value to AMERICAN_AMERICA.WE8MSWIN1252 and then restart. Let me know what happens
Thanks, tomorrow afternoon I'll try it and reply with my results.
Hi Mohsen, thanks for your help. I tried what you suggested. I checked my systeminfo and my system language local is hu. So I tried to set the NLS_LANG (HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1) to HUNGARIAN_HUNGARY.EE8MSWIN1250, but nothing happened. Then I set that to AMERICAN_AMERICA.WE8MSWIN1252 - also nothing happened. I restarted my computer after each regedit changes. Any other ideas?
Did you set HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1\NLS_LANG to HUNGARIAN_HUNGARY.EE8MSWIN1250 ?
|
0

You can try to change NLS_LANG value on your Win PC with regedit tool.

Path is: \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, when NLS_LANG contain value of your Oracle client's current encoding.

There is Oracle's list of available encodings: Commonly Used Values for NLS_LANG

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.