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?

timnavigate
  • 741
  • 4
  • 12
  • 23
maestro
  • 671
  • 1
  • 13
  • 27
  • Could you show here a result of this query: `select * from nls_database_parameters where parameter like '%SET'` – krokodilko Dec 06 '13 at 19:54
  • The result of your query: NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_CHARACTERSET EE8ISO8859P2 – maestro Dec 07 '13 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 ? – krokodilko Dec 07 '13 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. – maestro Dec 07 '13 at 13:05

2 Answers2

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.

Community
  • 1
  • 1
Mohsen Heydari
  • 7,256
  • 4
  • 31
  • 46
  • Thanks for your reply. Does it mean that I cannot change the encoding of the sqldeveloper to have the proper encoding? – maestro Dec 08 '13 at 19:38
  • 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 – Mohsen Heydari Dec 08 '13 at 20:31
  • Thanks, tomorrow afternoon I'll try it and reply with my results. – maestro Dec 08 '13 at 20:32
  • 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? – maestro Dec 09 '13 at 12:26
  • Did you set HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1\NLS_LANG to HUNGARIAN_HUNGARY.EE8MSWIN1250 ? – Mohsen Heydari Dec 09 '13 at 13:21
  • Yes - but without any positive result. – maestro Dec 09 '13 at 13:24
  • In plsql click on supportInfo (i think the name is this) a page will opens that will have some information find oracle_home, that is the home plsql is using to connect, what is the value? – Mohsen Heydari Dec 09 '13 at 13:55
  • If I'm correct you mean sqldeveloper help/about/properties. Well, I found the oracle.home value which points to my sqldeveloper folder on the HDD. But I also found some encoding information, like file.encoding = Cp1250, sun.jnu.encoding = Cp150. – maestro Dec 09 '13 at 14:22
  • No. The value of oracle.home = c:\sqldeveloper. In the registry, the value of the ORACLE_HOME_KEY = SOFTWARE\ORACLE\KEY_OraDb11g_home1, and the value of ORACLE_HOME = C:\oracle\de\product\11.2.0\dbhome_2 (I don't have the c:\oracle folder on my machine, maybe it's some garbage), and the value of the ORACLE_HOME_NAME = OraDb11g_home1 – maestro Dec 09 '13 at 14:50
  • Search the garbage value in registery, probably you will find a garbage Oracle-home; Delete the registry entry of garbage Oracle-home, then re-install plsql. – Mohsen Heydari Dec 09 '13 at 15:03
  • You mean I should reinstall the sqldeveloper client? I do not have oracle server on my local computer. – maestro Dec 09 '13 at 15:10
  • No any oracle home represents an Oracle Client installation, first search the registry for invalid home – Mohsen Heydari Dec 09 '13 at 15:11
  • I found several invalid entries - I had a full Oracle Server install on my local computer, I guess when I removed it, some garbage was left in the registry and that's why it has no effect when I change it. – maestro Dec 09 '13 at 15:18
  • I deleted the registry garbage, but sqldeveloper "reinstall" was just to delete the existing sqldeveloper and I unpacked it again. But the problem still exists. Fortunately we could manage to change our database encoding to UTF-8 which I guess solves the problem. The other thing is that I successfully installed Toad to another machine and using that I didn't have any encoding problem. So I guess that's the way. Anyway, thanks for your assistance, I accept your answer because that's how encoding things can be set. Thanks again. – maestro Dec 10 '13 at 12:58
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

timnavigate
  • 741
  • 4
  • 12
  • 23