Home » SQL & PL/SQL » Client Tools » Select query returns different characters on different desktops, same pl/sql developer versions (PL/SQL Developer - v. 8)
Select query returns different characters on different desktops, same pl/sql developer versions [message #605603] Sat, 11 January 2014 17:27 Go to next message
ivona.laiu
Messages: 3
Registered: January 2014
Location: Romania
Junior Member
Hello,

I am having trouble understanding the following problem:

On PL/SQL Developer, in an SQL window, when I run:

select uin from uin_detail where uin like '%355983031908260%';

--on my virtual machine which has Windows XP, I see this result:
¿355983031908260¿

--on my remote Desktop which has Windows Server 2003 installed, I receive this result:
‭355983031908260‬ -- actually, this should have other special characters, but I couldn't paste them, so I will attach a print screen with the correct result

I wish to see the same result on my virtual machine as the one obtained on my remote Desktop. (I have the same PL/SQL Developer version on both desktops.)

Thank you,
  • Attachment: uin.png
    (Size: 0.39KB, Downloaded 53 times)
Re: Select query returns different characters on different desktops, same pl/sql developer versions [message #605604 is a reply to message #605603] Sat, 11 January 2014 17:49 Go to previous messageGo to next message
BlackSwan
Messages: 22525
Registered: January 2009
Senior Member
what is result from both system when issuing SQL below?

select length(uin) from uin_detail where uin like '%355983031908260%';
Re: Select query returns different characters on different desktops, same pl/sql developer versions [message #605610 is a reply to message #605603] Sun, 12 January 2014 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The problem resides in character sets.

In your first case you have a NLS_LANG that does not support the character and so Oracle converts it to a replacement character which is "¿", or in the database you have this character (the replacement has taken place on the insert of the data)

In your second case you have a NLS_LANG that supports the character and your client tool does not and converts it to a square or you client tool does not support the "¿" and converts it to a square.

You have to first check the data in the database:
select dump(uin), dump(uin,16) from uin_detail where uin like '%355983031908260%';

Then check:
1/ Your Windows registries (for the tool you use)
2/ Your Windows environment variable

Re: Select query returns different characters on different desktops, same pl/sql developer versions [message #605626 is a reply to message #605604] Sun, 12 January 2014 07:58 Go to previous messageGo to next message
ivona.laiu
Messages: 3
Registered: January 2014
Location: Romania
Junior Member
Hello,

The length returned from the query you wrote is 17 - on both operating systems.
I guess that for every special character on Windows Server, on the other operating systems the query will return a "¿".
I need to see the same special character on both sides Sad and "¿" is the wrong one.

Thank you,
Re: Select query returns different characters on different desktops, same pl/sql developer versions [message #605628 is a reply to message #605626] Sun, 12 January 2014 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I told you what you have to do.
So do it and post the results.

In addition, post the result of:
select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
desc uin_detail

Re: Select query returns different characters on different desktops, same pl/sql developer versions [message #605629 is a reply to message #605610] Sun, 12 January 2014 08:41 Go to previous messageGo to next message
ivona.laiu
Messages: 3
Registered: January 2014
Location: Romania
Junior Member
Hello Michel,

For select dump(uin), dump(uin,16) from uin_detail where uin like '%355983031908260%';

On Windows Server I obtained:
dump(uin) - Typ=1 Len=21: 226,128,173,51,53,53,57,56,51,48,51,49,57,48,56,50,54,48,226,128,172
dump(uin,16) - Typ=1 Len=21: e2,80,ad,33,35,35,39,38,33,30,33,31,39,30,38,32,36,30,e2,80,ac

On Windows 7:
dump(uin) - Typ=1 Len=21: 226,128,173,51,53,53,57,56,51,48,51,49,57,48,56,50,54,48,226,128,172
dump(uin,16) - Typ=1 Len=21: e2,80,ad,33,35,35,39,38,33,30,33,31,39,30,38,32,36,30,e2,80,ac

So the internal representation is the same on both sides Sad.

I also checked NLS_LANG for:
-- HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 (Windows Server)
-- HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_DevSuiteHome1 and HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient11g_home1 (Windows 7)
It has the same data (AMERICAN_AMERICA.WE8MSWIN1252)

Windows' variables should be different and I shouldn't be changing them.

It may seem wrong, but I need to see the square in both places instead of "¿".

Thank you,




Re: Select query returns different characters on different desktops, same pl/sql developer versions [message #605630 is a reply to message #605629] Sun, 12 January 2014 09:08 Go to previous message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So the internal representation is the same on both sides


Of course, how it is stored inside the database does not depend on the client (hopefully).

Your string is actually (note the minus in 3rd position is not the standard minus)
‭­­­­­­­­­­-355983031908260‬

So both results are wrong (in the meaning of displaying the actual data).

What are the clients OS character set?
Note PL/SQL Developer may not use the standard registry key but another variable, in another place in registry or in environment (I asked you and you didn't post).
Also note that Oracle does not behave in the same way directly on the server or on a remote desktop.

What you see on your screen depends on many things among them NLS_LANG of the client, active code page (character set) of your client program, characters supported by your client program.


Previous Topic: print text
Next Topic: Select * from
Goto Forum:
  


Current Time: Tue Jul 29 15:10:16 CDT 2014

Total time taken to generate the page: 0.08497 seconds