Home » SQL & PL/SQL » Client Tools » Hebrew displayed as junk (SQL Developer, 4.0.2)
Hebrew displayed as junk [message #616002] Wed, 11 June 2014 09:01 Go to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
Hello,

I'm trying to retrieve data from remote Oracle database which contains data in Hebrew. I'm using SQL Developer 4.0.1 for this purpose and getting the Hebrew letters displayed as junk.

I must note, that I am not a DBA person and my knowledge of databases is very limited.
After searching the internet for this issue, I found out that this may be due to NLS settings incompatibilities between database, client and the application I am using (SQL Developer).

I ran the command "Select * From nls_database_parameters" and got the following:

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16

The command "Select * From nls_session_parameters" doesn't give no row with NLS_CHARACTERSET (If I'm understanding it right - it should let me know the client charset).

Really appreciate your help with further steps for resolving this issue.
I'll be glad to give you more information needed.
Re: Hebrew displayed as junk [message #616004 is a reply to message #616002] Wed, 11 June 2014 09:06 Go to previous messageGo to next message
BlackSwan
Messages: 22783
Registered: January 2009
Senior Member
Welcome to this forum.


Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/


what is OS name & version for the DB Server system?
what is OS name & version of the client system running SQL Developer?

Are you 100% certain that the data is properly stored inside the DB now?
Re: Hebrew displayed as junk [message #616006 is a reply to message #616002] Wed, 11 June 2014 09:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59100
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
NLS_CHARACTERSET WE8MSWIN1252


WE8MSWIN1252 does not support Hebrew characters so you cant' have them in the database.

Re: Hebrew displayed as junk [message #616009 is a reply to message #616002] Wed, 11 June 2014 09:37 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2314
Registered: May 2013
Location: World Wide on the Web
Senior Member
Analyst wrote on Wed, 11 June 2014 19:31
The command "Select * From nls_session_parameters" doesn't give no row with NLS_CHARACTERSET (If I'm understanding it right - it should let me know the client charset).


Perhaps you wanted :
SELECT * FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET%';
Re: Hebrew displayed as junk [message #616011 is a reply to message #616009] Wed, 11 June 2014 09:50 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
Thanks for your answer.

1) Oracle DB version (SELECT * FROM V$VERSION):

Oracle Database 10g Release 10.2.0.1.0 - 64bit Production
PL/SQL Release 10.2.0.1.0 - Production
"CORE	10.2.0.1.0	Production"
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


2) DB server system OS - Windows Server 2003, 64 bit.

3) OS, that I work on (running SQL Developer) - Windows 7 Professional, 64 bit.

4) Quote:
WE8MSWIN1252 does not support Hebrew characters
-

but that is what I get with the query:
Select * 
From nls_database_parameters


5) I'm certain that the data is stored in Hebrew inside the DB, because we see it properly in BI tools, which retrieve the data from the same DB.

6) The following command:
SELECT * FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET%';


gives me this:

NLS_CHARACTERSET  WE8MSWIN1252
NLS_NCHAR_CHARACTERSET  AL16UTF16
Re: Hebrew displayed as junk [message #616012 is a reply to message #616011] Wed, 11 June 2014 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 22783
Registered: January 2009
Senior Member
since you have not actually shown us the SELECT you issue, I can only generalize what you need to do

SELECT ASCIISTR(HEBREW_COL), HEBREW_COL FROM HEBREW_TBL WHERE PK = <KNOWN_VALUE>;

COPY & PASTE your actual query & post results back here.

[Updated on: Wed, 11 June 2014 09:57]

Report message to a moderator

Re: Hebrew displayed as junk [message #616013 is a reply to message #616012] Wed, 11 June 2014 10:08 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
Select ASCIISTR(LOCAL_NAME) 
From ITEMSECT 
Where ITEMSECT.CODE_NO in (40001,40002)


gave me:

\00F9\00E9\00E5\00E5\00F7
\00EE\00E5\00F1\00E3\00E9



Actually it must be (In such a way we see it in ERP):

שיווק
מוסדי

Re: Hebrew displayed as junk [message #616018 is a reply to message #616013] Wed, 11 June 2014 10:37 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2314
Registered: May 2013
Location: World Wide on the Web
Senior Member
Browser might be capable of displaying Hebrew, but your client tool might not. Where are you able to view hebrew properly?

Anorher thing, since when are you facing this issue? Were any changes done recently? Consult your DBA and probe these questions.

[Updated on: Wed, 11 June 2014 10:51]

Report message to a moderator

Re: Hebrew displayed as junk [message #616021 is a reply to message #616013] Wed, 11 June 2014 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59100
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Actually it must be


Not possible with WE8MSWIN1252.
For instance, character F9 in WE8MSWIN1252 is:
SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
VALUE
----------------------------------------
WE8MSWIN1252

SQL> select chr(to_number('F9','XX')) from dual;
C
-
ù


Quote:
In such a way we see it in ERP


This is because your ERP is wrongly parametrized. It says to Oracle it works in WE8MSWIN1252 but actually works and displays in another character set.

[Updated on: Wed, 11 June 2014 10:49]

Report message to a moderator

Re: Hebrew displayed as junk [message #616024 is a reply to message #616018] Wed, 11 June 2014 11:24 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
Quote:
Where are you able to view hebrew properly?


The data inputted in ERP (our operational enterprise management system) in Hebrew and stored in the DB, from which I am trying to retrieve the data. In this ERP the data is stored properly.

The BI tools (like Qlikview), which use the data from the DB also display it in a proper way.

Quote:
Anorher thing, since when are you facing this issue? Were any changes done recently? Consult your DBA and probe these questions.


I recently joined the company and the issue faced since they started to use SQL Developer (more than a year). There is no real DBA to consult with.


Quote:
his is because your ERP is wrongly parametrized. It says to Oracle it works in WE8MSWIN1252 but actually works and displays in another character set.


Do I have a way to resolve it on my client tool without changing the parameters of ERP?
Note that in BI tools I see it properly as well.

[Updated on: Wed, 11 June 2014 11:24]

Report message to a moderator

Re: Hebrew displayed as junk [message #616026 is a reply to message #616024] Wed, 11 June 2014 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59100
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Do I have a way to resolve it on my client tool without changing the parameters of ERP?


This tool is wrongly configured, sooner or later you WILL screw up your data with a tool which is correctly configured.

Quote:
Note that in BI tools I see it properly as well.


So it is also wrongly wonfigured.

I repeat, and you can do nothing with it, WE8MSWIN1252 cannot store hebrew characters.

Re: Hebrew displayed as junk [message #616071 is a reply to message #616026] Thu, 12 June 2014 02:55 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
Thank you for the explanation. I would like to ascertain, that I understand it right.

1) The data, which inputted to ERP in Hebrew letters is wrongly stored to Oracle as WE8MSWIN1252 (which is not familiar with Hebrew) and consequently displayed as junk symbols in SQL Developer.

2) The same Hebrew data (stored as junk because of WE8MSWIN1252) is taken from Oracle DB to BI tools and presented there properly because of manipulations with wrong configurations.

I suppose, that you are right. With that, the database and the BI tools work in such a manner for years and it's not going to be changed in the nearest time.

I would like to configure my SQL Developer in similar manner, in order to see the data in Hebrew.
I've consulted the DBA of one of our BI tools. As I've answered, they solve it using collation:

Collate=Hebrew_CI_AS


Do you think, I could use this in order to get the right result?
If yes, could anybody recommend me how should I do it?
Re: Hebrew displayed as junk [message #616099 is a reply to message #616071] Thu, 12 June 2014 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59100
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ensure that in Tools/Preference/Environment/Encoding you have the value "Cp1252".
I think if you do this you should be able to see your characters.
Waiting for your feedback...

Re: Hebrew displayed as junk [message #616111 is a reply to message #616099] Thu, 12 June 2014 06:02 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
I have the value (Cp1252) in this configuration now. It doesn't help.
Re: Hebrew displayed as junk [message #616129 is a reply to message #616111] Thu, 12 June 2014 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59100
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you launch SQL Developer from a .cmd file like the following:
set AMERICAN_AMERICA.WE8MSWIN1252
<your sqldeveloper path>\sqldeveloper.exe

(You can change the language and country as you want, it does not matter for this problem, but do not change the character set.)

Re: Hebrew displayed as junk [message #616284 is a reply to message #616129] Sun, 15 June 2014 01:43 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
I launched cmd.exe and ran the command, you mentioned:

set AMERICAN_AMERICA.WE8MSWIN1252
<My sqldeveloper path>\sqldeveloper.exe


Getting the following message:

Environmental variable AMERICAN_AMERICA.WE8MSWIN1252
<My sqldeveloper path>\sqldeveloper.exe not defined



[Updated on: Sun, 15 June 2014 01:52]

Report message to a moderator

Re: Hebrew displayed as junk [message #616286 is a reply to message #616284] Sun, 15 June 2014 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59100
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to replace "<My sqldeveloper path>" by the real path of sqldeveloper.exe.

Re: Hebrew displayed as junk [message #616287 is a reply to message #616284] Sun, 15 June 2014 02:33 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
Of course, I did it.
It was actually:

Environmental variable AMERICAN_AMERICA.WE8MSWIN1252
<C:\Users\Analyst\Desktop\DB\Programs\Oracle SQL Developer>\sqldeveloper.exe not defined

Re: Hebrew displayed as junk [message #616290 is a reply to message #616287] Sun, 15 June 2014 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59100
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"<" and ">" must not be in the command.
Just
C:\Users\Analyst\Desktop\DB\Programs\Oracle SQL Developer\sqldeveloper.exe

Re: Hebrew displayed as junk [message #616293 is a reply to message #616290] Sun, 15 June 2014 05:43 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
Sorry, It's my mistake, there was no "<" and ">" in the command.

Environmental variable AMERICAN_AMERICA.WE8MSWIN1252
C:\Users\Analyst\Desktop\DB\Programs\Oracle SQL Developer>\sqldeveloper.exe not defined
Re: Hebrew displayed as junk [message #616295 is a reply to message #616293] Sun, 15 June 2014 06:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59100
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
C:\Users\Analyst\Desktop\DB\Programs\Oracle SQL Developer>\sqldeveloper.exe not defined


There is still ">"

Re: Hebrew displayed as junk [message #616298 is a reply to message #616295] Sun, 15 June 2014 07:22 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
In the actual command there are no "<" and ">". I cannot copy from CMD, so I wrote it in a manual way.

The message is:

Environmental variable AMERICAN_AMERICA.WE8MSWIN1252
C:\Users\Analyst\Desktop\DB\Programs\Oracle SQL Developer\sqldeveloper.exe not defined


Excuse me for the confusion.
Re: Hebrew displayed as junk [message #616301 is a reply to message #616298] Sun, 15 June 2014 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59100
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Instead of wrongly telling what you do (and get), copy and paste it in text mode (no image).

Re: Hebrew displayed as junk [message #616302 is a reply to message #616301] Sun, 15 June 2014 08:44 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
Do I have a way to switch cmd.exe to the text mode? "Alt + Enter" doesn't work at Windows 7 64-bit, as I found from the Web.

This is the message, that I get

Environmental variable AMERICAN_AMERICA.WE8MSWIN1252
C:\Users\Analyst\Desktop\DB\Programs\Oracle SQL Developer\sqldeveloper.exe not defined


After running this:

set AMERICAN_AMERICA.WE8MSWIN1252
C:\Users\Analyst\Desktop\DB\Programs\Oracle SQL Developer\sqldeveloper.exe
Re: Hebrew displayed as junk [message #616303 is a reply to message #616302] Sun, 15 June 2014 08:52 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2314
Registered: May 2013
Location: World Wide on the Web
Senior Member
Is the path for sql developer correct? Are you giving that of the shortcut on desktop or the original path where it resides in installed files.
Re: Hebrew displayed as junk [message #616304 is a reply to message #616303] Sun, 15 June 2014 08:58 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
It is the original path.
I've installed the SQL Developer into the folder, that resides on the desktop.
Re: Hebrew displayed as junk [message #616305 is a reply to message #616304] Sun, 15 June 2014 09:06 Go to previous messageGo to next message
BlackSwan
Messages: 22783
Registered: January 2009
Senior Member
post results from following OS command

DIR C:\Users\Analyst\Desktop\DB\Programs\Oracle SQL Developer\
Re: Hebrew displayed as junk [message #616306 is a reply to message #616305] Sun, 15 June 2014 09:17 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
I am getting the following:

The system cannot find the file specified.


How could it be? I copied the path from the properties of the original .exe file.
Re: Hebrew displayed as junk [message #616307 is a reply to message #616306] Sun, 15 June 2014 09:24 Go to previous messageGo to next message
BlackSwan
Messages: 22783
Registered: January 2009
Senior Member
We only know about your system what you post here.
If/when you post wrongly, we can't distinguish fact from fiction.
do you know & understand Windows difference between "shortcut" & actual physical file?

What does Windows "File Find" report for where sqldeveloper.exe file actually resides on your system?
Re: Hebrew displayed as junk [message #616308 is a reply to message #616306] Sun, 15 June 2014 09:30 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2314
Registered: May 2013
Location: World Wide on the Web
Senior Member
Analyst wrote on Sun, 15 June 2014 19:47
I am getting the following:

The system cannot find the file specified.


How could it be? I copied the path from the properties of the original .exe file.


1. Go to All Programs from Start.
2. Click Oracle home folder.
3. Click application development folder.
4. Right click on sql developer, go to properties, copy target file path.
Re: Hebrew displayed as junk [message #616309 is a reply to message #616306] Sun, 15 June 2014 09:41 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
Maybe it is time to get acquainted with your operating system as well... or stop using spaces in directory names.
http://www.computerhope.com/issues/ch001477.htm
Quote:
In the Windows command line any file or directory that contains a space must be surrounded with quotation marks in order for it to be recognized.
...
The reason the quotation marks are needed on files with spaces is that the command line doesn't know where the file name begins or ends without them, due to the spaces.
...

C:\Users\Flyboy>cd c:/work

c:\Work>mkdir "Test AAA"

c:\Work>dir C:\work\Test AAA\
The system cannot find the file specified.

c:\Work>dir "C:\work\Test AAA\"
 Volume in drive C is Windows
 Volume Serial Number is D8B7-68E2

 Directory of C:\work\Test AAA

15.06.2014  16:35    <DIR>          .
15.06.2014  16:35    <DIR>          ..
               0 File(s)              0 bytes
               2 Dir(s)  337 059 303 424 bytes free

c:\Work>
Re: Hebrew displayed as junk [message #616310 is a reply to message #616308] Sun, 15 June 2014 10:12 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
1. Go to All Programs from Start.
2. Click Oracle home folder.
3. Click application development folder.
4. Right click on sql developer, go to properties, copy target file path.


I said, that my technical knowledge in computing is limitted.Smile
The path, that I get following your instructions:

C:\app\Analyst\product\12.1.0\dbhome_1\sqldeveloper\sqldeveloper\bin\sqldeveloperW.exe


Now that is what I wrote in cmd.exe:

set AMERICAN_AMERICA.WE8MSWIN1252
C:\app\Analyst\product\12.1.0\dbhome_1\sqldeveloper\sqldeveloper\bin\sqldeveloperW.exe


There are no spaces in the directory name now, but I still get the same message:

Environmental variable AMERICAN_AMERICA.WE8MSWIN1252
C:\app\Analyst\product\12.1.0\dbhome_1\sqldeveloper\sqldeveloper\bin\sqldeveloperW.exe not defined



[Updated on: Sun, 15 June 2014 10:21]

Report message to a moderator

Re: Hebrew displayed as junk [message #616311 is a reply to message #616310] Sun, 15 June 2014 11:02 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2314
Registered: May 2013
Location: World Wide on the Web
Senior Member
Analyst wrote on Sun, 15 June 2014 20:42

C:\app\Analyst\product\12.1.0\dbhome_1\sqldeveloper\sqldeveloper\bin\sqldeveloperW.exe


Can you launch Sql developer from cmd, and tell us whether it opens up or not.

1. Open cmd
2. Copy this C:\app\Analyst\product\12.1.0\dbhome_1\sqldeveloper\sqldeveloper\bin\sqldeveloperW.exe
3. Paste it in cmd using right click on top and click paste.
4. Press enter and see whether sql developer is launched.

Re: Hebrew displayed as junk [message #616312 is a reply to message #616311] Sun, 15 June 2014 11:08 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
I'm getting pop-up window with Oracle SQL Developer in the title and the following message in it:

Enter the full pathname for java:exe


Re: Hebrew displayed as junk [message #616313 is a reply to message #616312] Sun, 15 June 2014 11:11 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
cmd.exe is opened already with the text (I cannot delete it):

C:\Users\Analyst>


After the text I copied what you told me:

C:\app\Analyst\product\12.1.0\dbhome_1\sqldeveloper\sqldeveloper\bin\sqldeveloperW.exe
Re: Hebrew displayed as junk [message #616314 is a reply to message #616312] Sun, 15 June 2014 11:20 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2314
Registered: May 2013
Location: World Wide on the Web
Senior Member
Analyst wrote on Sun, 15 June 2014 21:38
I'm getting pop-up window with Oracle SQL Developer in the title and the following message in it:

Enter the full pathname for java:exe




Seems to be an issue with JDK. You said that you saw junk characters in sql developer, means you were already using the tool. But now you are unable to open it via cmd. Is the same error thrown while you open it directly?
Re: Hebrew displayed as junk [message #616315 is a reply to message #616314] Sun, 15 June 2014 11:25 Go to previous messageGo to next message
Analyst
Messages: 22
Registered: June 2014
Junior Member
No, directly it's opened correctly.
And I am able to open it using cmd.exe when I copy there the following path (the one I posted here before):

"C:\Users\Analyst\Desktop\DB\Programs\Oracle SQL Developer\sqldeveloper.exe"


I think that I have SQL Developer installed in 2 places.
I installed Oracle db on my client in this path:

C:\app\Analyst\product\12.1.0\dbhome_1\


And I also unpacked SQL Developer to this path:

C:\Users\Analyst\Desktop\DB\Programs\Oracle SQL Developer\


Should I try to delete one of them?

[Updated on: Sun, 15 June 2014 11:28]

Report message to a moderator

Re: Hebrew displayed as junk [message #616316 is a reply to message #616315] Sun, 15 June 2014 11:43 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2314
Registered: May 2013
Location: World Wide on the Web
Senior Member
No, It is not an issue to install sql developer into a folder of your choice in windows. However, since it is prompting to enter the fullpath for java.exe, you need to provide its location for example : <your directory>\Java\jdk1.x.x_xx\bin\java.exe.

For more information, check out http://docs.oracle.com/cd/E12151_01/doc.150/e12153/install.htm
Re: Hebrew displayed as junk [message #616317 is a reply to message #616316] Sun, 15 June 2014 11:53 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2314
Registered: May 2013
Location: World Wide on the Web
Senior Member
Coming back to your problem with environment variable not defined while following Michel's steps, can you do this check :

In WIndows 7,
1. Right click on My computer.
2. Go to properties.
3. Go to Advanced system settings on left pane.
4. In system properties, click Environment variables.
5. In system variables below, select the PATH. 
6. Click on edit, copy paste it back here.
Re: Hebrew displayed as junk [message #616348 is a reply to message #616317] Mon, 16 June 2014 01:45 Go to previous messageGo to previous message
Analyst
Messages: 22
Registered: June 2014
Junior Member
Pasting the text in the "PATH":

C:\app\Analyst\product\12.1.0\dbhome_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\;c:\Program Files\Microsoft SQL Server\110\Tools\Binn\;c:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;c:\Program Files\Microsoft SQL Server\110\DTS\Binn\;c:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\;c:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\;c:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\
Previous Topic: Exporting data with Scheduled job
Next Topic: Unable to see database list in dropdown of pl/sql developer
Goto Forum:
  


Current Time: Wed Sep 17 05:18:41 CDT 2014

Total time taken to generate the page: 0.14868 seconds