Home » SQL & PL/SQL » SQL & PL/SQL » differences in showing German umlaut (Windows Server 2008 R2 , Oracle on OEL 5.7)
| differences in showing German umlaut [message #569657] |
Tue, 30 October 2012 15:26  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi all,
I'm trying to understand the way umaults are displayed and stored in Oracle.
I have 2 application servers(A & B), and one Database Server.
SERVER A
OS Version: Windows Server 2008 R2 Version 6.1
NLS_LANG environment variable: AMERICAN_AMERICA.WE8MSWIN1252
Location, Keyboard & Locale: English(United States)
SQLPLUS client Version: 11.2.0.2.0
C:\Users\Administrator>sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 30 21:51:56 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL>
SERVER B
OS Version: Windows Server 2008 R2 Version 6.1
NLS_LANG environment variable: AMERICAN_AMERICA.WE8MSWIN1252
Location, Keyboard & Locale: English(United States)
SQLPLUS client Version: 11.2.0.1.0
C:\Users\Administrator>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 30 19:42:55 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL>
------------------------------------------------------------
So, I do my testcase on the same database,
Fisrt, from Server A:
SQL> --Server A
SQL>
SQL> conn andrey/**********@test_db
Connected.
SQL>
SQL>
SQL> DEFINE _SQLPLUS_RELEASE
DEFINE _SQLPLUS_RELEASE = "1102000200" (CHAR)
SQL> select distinct CLIENT_CHARSET from v$session_connect_info where CLIENT_CHARSET is not null;
CLIENT_CHARSET
----------------------------------------
WE8MSWIN1252
Unknown
US7ASCII
SQL>
SQL>
SQL> select NETWORK_SERVICE_BANNER from v$session_connect_info
2 where lower(NETWORK_SERVICE_BANNER) not like '%unknown%';
NETWORK_SERVICE_BANNER
--------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.1.0 - Pro
duction
Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.
1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.1.0 - Pro
duction
Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.
1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.1.0 - Pro
duction
Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.
1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.1.0 - Pro
duction
Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.
1.0 - Production
12 rows selected.
SQL>
SQL>
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL>
SQL> set pages 200
SQL> col parameter for a25
SQL> col value for a40
SQL>
SQL> select * from v$nls_parameters;
PARAMETER VALUE
------------------------- ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET AL32UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected.
SQL>
SQL> drop table dtable;
Table dropped.
SQL>
SQL> create table dtable
2 (
3 dname varchar2(20)
4 );
Table created.
SQL>
SQL> insert into dtable select 1 from dual;
1 row created.
SQL> insert into dtable select 'regular' from dual;
1 row created.
SQL> insert into dtable select 'Ö umlaut ' from dual;
1 row created.
SQL> insert into dtable select 'Ö' from dual;
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> col dname for a15
SQL> col dump_dname for a60
SQL> select dname, dump(dname) dump_dname from dtable;
DNAME DUMP_DNAME
--------------- ------------------------------------------------------------
1 Typ=1 Len=1: 49
regular Typ=1 Len=7: 114,101,103,117,108,97,114
? umlaut Typ=1 Len=11: 239,191,189,32,117,109,108,97,117,116,32
? Typ=1 Len=3: 239,191,189
SQL>
SQL> select ASCIISTR(dname) from dtable;
ASCIISTR(DNAME)
--------------------------------------------------------------------------------
1
regular
\FFFD umlaut
\FFFD
SQL>
SQL>
Then on Server B:
SQL>
SQL> --Server B
SQL>
SQL> conn andrey/**********@test_db
Connected.
SQL>
SQL>
SQL> DEFINE _SQLPLUS_RELEASE
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
SQL> select distinct CLIENT_CHARSET from v$session_connect_info where CLIENT_CHA
RSET is not null;
CLIENT_CHARSET
----------------------------------------
WE8MSWIN1252
Unknown
US7ASCII
SQL>
SQL>
SQL> select NETWORK_SERVICE_BANNER from v$session_connect_info
2 where lower(NETWORK_SERVICE_BANNER) not like '%unknown%';
NETWORK_SERVICE_BANNER
--------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.1.0 - Pro
duction
Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.
1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.1.0 - Pro
duction
Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.
1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.1.0 - Pro
duction
Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.
1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.1.0 - Pro
duction
Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.
1.0 - Production
12 rows selected.
SQL>
SQL>
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL>
SQL> set pages 200
SQL> col parameter for a25
SQL> col value for a40
SQL>
SQL> select * from v$nls_parameters;
PARAMETER VALUE
------------------------- ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET AL32UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected.
SQL>
SQL> drop table dtable;
Table dropped.
SQL>
SQL> create table dtable
2 (
3 dname varchar2(20)
4 );
Table created.
SQL>
SQL> insert into dtable select 1 from dual;
1 row created.
SQL> insert into dtable select 'regular' from dual;
1 row created.
SQL> insert into dtable select 'Ö umlaut ' from dual;
1 row created.
SQL> insert into dtable select 'Ö' from dual;
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> col dname for a15
SQL> col dump_dname for a60
SQL> select dname, dump(dname) dump_dname from dtable;
DNAME DUMP_DNAME
--------------- ------------------------------------------------------------
1 Typ=1 Len=1: 49
regular Typ=1 Len=7: 114,101,103,117,108,97,114
Ö umlaut Typ=1 Len=11: 226,132,162,32,117,109,108,97,117,116,32
Ö Typ=1 Len=3: 226,132,162
SQL>
SQL> select ASCIISTR(dname) from dtable;
ASCIISTR(DNAME)
--------------------------------------------------------------------------------
1
regular
\2122 umlaut
\2122
SQL>
SQL>
The last one gave out the correct output...
Where does the storage and the display of the umlauts come from?
Why do I have those differences?
Thanks in Advance,
Andrey
|
|
|
|
|
|
| Re: differences in showing German umlaut [message #569661 is a reply to message #569659] |
Tue, 30 October 2012 15:36  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 30 October 2012 22:31Difference of NLS_LANG of process that started the databases?
Michel,
Differences in the way the umlaut is both stored and presented,
between Windows Server A and Windows Server B.
I did not change anything in the database, nor restart it...
And my configuration seems identical,
except a slight difference in SQL*Plus version, which should not matter..
I don't understand why each Windows Server acts different on the same database.
What am I missing out on?
Regards,
Andrey
|
|
|
|
|
|
| Re: differences in showing German umlaut [message #569663 is a reply to message #569662] |
Tue, 30 October 2012 16:04 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
BlackSwan wrote on Tue, 30 October 2012 22:41
? Typ=1 Len=3: 239,191,189
above is from A
below is from B
Ö Typ=1 Len=3: 226,132,162
content is DIFFERENT, so I doubt same USER, TABLE or DB
You always doubt everything I say:)
It is the same database.
each time I dropped the table and started over with the same syntax,
Of creating the table and inserting the record. I posted it in my code.
So, as I said, it *stored* it differently.
code was the same, user was the same,
table was the same(dropped and re-created by each session, as you can see in the code),
database was the same.
Application Server hosting the SQL*Plus is different.
result is different. Why?
I can only assume it is something in the OS, but I don't know what it is,
If i have all settings I mentioned configured the same for both servers.
Regards,
Andrey
[Updated on: Tue, 30 October 2012 16:15] Report message to a moderator |
|
|
|
|
|
| Re: differences in showing German umlaut [message #569682 is a reply to message #569670] |
Wed, 31 October 2012 05:05 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Yes and nothing seems to be related to this.
I checked with PL/SQL developer and it seems to store and present the right umlaut.
(I checked with Cyrillic this time, but same scenario and testbed, only a Cyrillic umlaut).
What is "right" to me? "right" is whatever gives me the correct value I see in a ASCII table,
Such as this one:
http://jrgraphix.net/r/Unicode/0400-04FF
so, PL/SQL developer gave me the right umlaut of the russian letter "я", which is "044F".
I did select ASCIISTR(dname) from dtable;
In SQL*Plus it didn't. it gave out something else.
so I suspect that SQL*Plus takes something from the Linux Server instead of the operating system.
I will check some parameters of the Linux server(NLS_LANG) and update what i see and if i change to what, and how does it react.
If you can suggest some of these parameters to check I will be happy to examine those too.
Will update soon.
Regards,
Andrey
|
|
|
|
| Re: differences in showing German umlaut [message #569687 is a reply to message #569682] |
Wed, 31 October 2012 05:28 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
I did
[oracle@dbserver1 ~]$ export LANG='RUSSIAN_RUSSIA.AL32UTF8'
[oracle@dbserver1 ~]$ env | grep LANG
LANG=RUSSIAN_RUSSIA.AL32UTF8
[oracle@dbserver1 ~]$
It was set to en_US.UTF-8
and now SQL*Plus doesn't even let me insert the russian umlaut at all...only concatenated to a string
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\Administrator>sqlplus andrey@test_db
SQL*Plus: Release 11.2.0.1.0 Production on ╨б╤А ╨Ю╨║╤В 31 10:17:19 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL>
SQL> drop table dtable;
Table dropped.
SQL>
SQL> create table dtable
2 (
3 dname varchar2(20)
4 );
Table created.
SQL>
SQL> insert into dtable select 1 from dual;
1 row created.
SQL> insert into dtable select 'regular' from dual;
1 row created.
SQL> insert into dtable select 'я umlaut ' from dual;
1 row created.
SQL> insert into dtable select 'я' from dual;
ERROR:
ORA-01756: ╨╜╨╡╤В ╨╖╨░╨▓╨╡╤А╤И╨░╤О╤Й╨╡╨╣ ╨║╨░╨▓╤Л╤З╨║╨╕
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> col dname for a15
SQL> col dump_dname for a60
SQL> select dname, dump(dname) dump_dname from dtable;
DNAME DUMP_DNAME
--------------- ------------------------------------------------------------
1 Typ=1 Len=1: 49
regular Typ=1 Len=7: 114,101,103,117,108,97,114
я umlaut Typ=1 Len=9: 239,32,117,109,108,97,117,116,32
SQL>
SQL> select ASCIISTR(dname) from dtable;
ASCIISTR(DNAME)
-------------------------------------------------------------------------------
1
regular
\FFFDmlaut
SQL>
SQL>
It does seem to show that in SQL*Plus it is related to what is set in the database server..
I still don't understand what should be set so I can see *the same* on PL/SQL developer, and on SQL*Plus...
The question now, is maybe, what should be set on the database/SQL*Plus , so that it will store read russian umlauts correctly,
according to the ASCII table...?
I'm pretty confused, as I don't know why SQL*Plus seems to not be taking my NLS_LANG parameter to its use,
at least not as far as my understanding of the documentation says...
Will appreciate any help/hint/advice....
Thanks,
Andrey
[Updated on: Wed, 31 October 2012 05:28] Report message to a moderator |
|
|
|
| Re: differences in showing German umlaut [message #569703 is a reply to message #569687] |
Wed, 31 October 2012 07:32 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
When you INSERT with SQL*Plus the NLS_LANG code page SQL*Plus is started with is used.
Be aware, there is an additional input/output translation going on in the "Command Prompt Window", because that can use different code pages, too.
See here for example
Basically, if you want to display and insert the characters the way they are displayed in the cmd.exe prompt then the cmd.exe codepage has to match the NLS_LANG character set *and* the encoding of all input files has to match as well.
If you don't care how it "looks in the black window", you just want the input/output files to match correctly, you can ignore the code page and just set NLS_LANG to the encoding of the input/output files.
|
|
|
|
| Re: differences in showing German umlaut [message #570189 is a reply to message #569703] |
Tue, 06 November 2012 10:28 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi Thomas,
I've read the thread you gave a link to, and tried the stuff that was in there - it DOESN'T WORK.
It doesn't show russian umlauts, nor let me save them in a database in any of the code pages the poster stated it should.
Code pages he stated to be unicode and display any language simply displayed either Gibberish, either question marks.
I still have no clue what to set in SQL*Plus to store & display those umlauts.
I do see that PL/SQL developer knows how to store and display them, but I don't know why.
I even have Russian set as my LOCALE language, and it still doesn't work. what a headache:(
Does anyone know how this works? principals by which I should go? examples of what to check, what to set in my checks?
Regards,
Andrey
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Sep 03 08:54:00 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
