| Unable to insert Euro Symbol in database with Character set WE8MSWIN1252 [message #501450] |
Tue, 29 March 2011 04:44  |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello
I am using oracle 9.2.0.6.0 on HP-UX
I am unable to insert and even display euro symbol from server as well as windows client
Following are the details of my database server
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
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_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 9.2.0.6.0
20 rows selected.
SQL> !echo $NLS_LANG
AMERICAN_AMERICA.WE8MSWIN1252
1)
When I try to insert € from Db server (using putty) using Alt+0128 it does not print anything (nothing gets typed on the screen). Not even junk characters
Also following query does not print anything
SQL> select chr(128) from dual;
C
-
2) while I set NLS_LANG on client and try to insert €, Alt+0128 produces a question mark symbol
And following query displays junk character
SQL> select chr(128) from dual;
C
-
Ç
3) Regardless when I inserted couple of rows and tried UNISTR following was the result
SQL> select unistr(v) from t;
Error 45 initializing SQL*Plus
Internal error
$
Am I missing anything?
Regards,
OraKaran
|
|
|
|
|
|
| Re: Unable to insert Euro Symbol in database with Character set WE8MSWIN1252 [message #501469 is a reply to message #501450] |
Tue, 29 March 2011 05:57  |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello Michel
Thanks for your reply
1) Sorry. I should have been more clear.
I am using sqlplus on server as well as client
2) On server NLS_LANG is set to value I mentioned
I am setting the same value 'AMERICAN_AMERICA.WE8MSWIN1252' on client as well before inserting the value
My character set is WE8MSWIN1252 and for code page 1252 code point is 128 as described in Metalink note ID 68790.1
That's why I tried Alt+0128 reading something here and there
Regards,
OraKaran
|
|
|
|
|
|
|
|
|
|
| Re: Unable to insert Euro Symbol in database with Character set WE8MSWIN1252 [message #501495 is a reply to message #501450] |
Tue, 29 March 2011 07:53 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello Michel
Following is the output of 'locale -a' on the db server
oracle@cushty:/home/oracle $locale -a
C
POSIX
C.iso88591
C.iso885915
C.utf8
univ.utf8
es_AR.iso88591
es_AR.iso885915
es_AR.utf8
es_BO.iso88591
es_BO.iso885915
es_CL.iso88591
es_CL.iso885915
es_CL.utf8
es_CO.iso88591
es_CO.iso885915
es_CO.utf8
es_CR.iso88591
es_CR.iso885915
es_EC.iso88591
es_EC.iso885915
es_ES.iso88591
es_ES.iso885915@euro
es_ES.roman8
es_ES.utf8
es_GT.iso88591
es_GT.iso885915
es_MX.iso88591
es_MX.iso885915
es_MX.utf8
es_NI.iso88591
es_NI.iso885915
es_PA.iso88591
es_PA.iso885915
es_PE.iso88591
es_PE.iso885915
es_PE.utf8
es_PR.iso88591
es_PR.iso885915
es_PR.utf8
es_PY.iso88591
es_PY.iso885915
es_SV.iso88591
es_SV.iso885915
es_UY.iso88591
es_UY.iso885915
es_UY.utf8
es_VE.iso88591
es_VE.iso885915
es_VE.utf8
ko_KR.eucKR
ko_KR.utf8
ja_JP.SJIS
ja_JP.eucJP
ja_JP.kana8
ja_JP.utf8
it_IT.iso88591
it_IT.iso885915@euro
it_IT.roman8
it_IT.utf8
de_DE.iso88591
de_DE.iso885915@euro
de_DE.roman8
de_DE.utf8
fr_CA.iso88591
fr_CA.iso885915
fr_CA.roman8
fr_CA.utf8
fr_FR.iso88591
fr_FR.iso885915@euro
fr_FR.roman8
fr_FR.utf8
zh_HK.hkbig5
zh_HK.utf8
zh_TW.big5
zh_TW.ccdc
zh_TW.eucTW
zh_TW.utf8
zh_CN.gb18030
zh_CN.hp15CN
zh_CN.utf8
ar_DZ.arabic8
ar_SA.arabic8
ar_SA.iso88596
bg_BG.iso88595
cs_CZ.iso88592
da_DK.iso88591
da_DK.iso885915@euro
da_DK.roman8
nl_NL.iso88591
nl_NL.iso885915@euro
nl_NL.roman8
en_GB.iso88591
en_GB.iso885915@euro
en_GB.roman8
en_US.iso88591
en_US.roman8
fi_FI.iso88591
fi_FI.iso885915@euro
fi_FI.roman8
el_GR.greek8
el_GR.iso88597
el_GR.utf8
iw_IL.hebrew8
iw_IL.iso88598
hu_HU.iso88592
is_IS.iso88591
is_IS.iso885915@euro
is_IS.roman8
no_NO.iso88591
no_NO.iso885915@euro
no_NO.roman8
pl_PL.iso88592
pt_BR.iso88591
pt_BR.iso885915
pt_BR.utf8
pt_PT.iso88591
pt_PT.iso885915@euro
pt_PT.roman8
ro_RO.iso88592
ru_RU.iso88595
hr_HR.iso88592
sk_SK.iso88592
sl_SI.iso88592
sv_SE.iso88591
sv_SE.iso885915@euro
sv_SE.roman8
sv_SE.utf8
th_TH.tis620
tr_TR.iso88599
tr_TR.turkish8
ar_DZ.utf8
ar_SA.utf8
bg_BG.utf8
cs_CZ.utf8
da_DK.utf8
nl_NL.utf8
en_GB.utf8
en_US.utf8
fi_FI.utf8
iw_IL.utf8
hu_HU.utf8
is_IS.utf8
no_NO.utf8
pl_PL.utf8
pt_PT.utf8
ro_RO.utf8
ru_RU.utf8
hr_HR.utf8
sk_SK.utf8
sl_SI.utf8
es_BO.utf8
es_CR.utf8
es_EC.utf8
es_GT.utf8
es_NI.utf8
es_PA.utf8
es_PY.utf8
es_SV.utf8
tr_TR.utf8
oracle@cushty:/home/oracle $
Also the NLS_LANG is set based on following values
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8MSWIN1252
SQL> !echo $NLS_LANG
AMERICAN_AMERICA.WE8MSWIN1252
My client tool is putty
Also I am trying from windows client as well using sqlplus
Regards,
OraKaran
|
|
|
|
|
|
| Re: Unable to insert Euro Symbol in database with Character set WE8MSWIN1252 [message #502469 is a reply to message #501450] |
Wed, 06 April 2011 09:34 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello Michel,
I checked 'Character classes' in putty and it ranges from 0 till 127
Is that the reason I am not getting anything displayed after typing Alt + 0128?
There is no luck on for sqlplus or sqlplusw as well, which displays junk characters.
However TOAD is inserting and displaying the Euro symol correctly
using 'insert into t chr(128);' or 'select chr(128) from dual'
Please let me when I how I can do it from cmd->sqlplus or from putty.
Regards,
OraKaran
|
|
|
|
|
|
|
|
| Re: Unable to insert Euro Symbol in database with Character set WE8MSWIN1252 [message #502547 is a reply to message #501450] |
Thu, 07 April 2011 03:01 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello Michel and ThomasG
Thanks for your replies and patience
Michel I tried changing codepage but have no luck
Following are the results
C:\Documents and Settings\orakaran>chcp
Active code page: 850
C:\Documents and Settings\orakaran>chcp 850
Active code page: 850
-- Typed Alt+0128
C:\Documents and Settings\orakaran>?
'?' is not recognized as an internal or external command,
operable program or batch file.
C:\Documents and Settings\orakaran>chcp 1252
Active code page: 1252
-- Typed Alt+0128
C:\Documents and Settings\orakaran>Ç
'Ç' is not recognized as an internal or external command,
operable program or batch file.
C:\Documents and Settings\orakaran>set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
C:\Documents and Settings\orakaran>chcp 1252
Active code page: 1252
-- Typed Alt+0128
C:\Documents and Settings\orakaran>Ç
'Ç' is not recognized as an internal or external command,
operable program or batch file.
C:\Documents and Settings\orakaran>chcp 850
Active code page: 850
-- Typed Alt+0128
C:\Documents and Settings\orakaran>?
'?' is not recognized as an internal or external command,
operable program or batch file.
C:\Documents and Settings\orakaran>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Apr 7 08:58:31 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: system@testdb
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> delete from e;
3 rows deleted.
SQL> insert into e values(chr(128));
1 row created.
SQL> select * from e;
V
----------
Ç
SQL>
ThomasG -> I have Translataion seetings in putty exactly same as the snap attached by you. However there is no font like 'Euro'. My default font is 'Courier New'. I tried all the available fonts r_*, WST_* but no luck.
Regards,
OraKaran
|
|
|
|
|
|
| Re: Unable to insert Euro Symbol in database with Character set WE8MSWIN1252 [message #502550 is a reply to message #501450] |
Thu, 07 April 2011 03:18 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Surprise!!
I open new cmd session
Did not change code page in this session (default is 850)
However I changed the font from 'Raster Fonts' to ''Lucida Console' and it worked.
Please look at session below
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
- Typed Alt + 0128
C:\Documents and Settings\orakaran>€
'€' is not recognized as an internal or external command,
operable program or batch file.
C:\Documents and Settings\orakaran>chcp
Active code page: 850
C:\Documents and Settings\orakaran>
Michel, how to justify this
It did not need NLS_LANG or code page settings to display Euro symbol (€) now.
I am more confused now.
What we need actually? Font, Code page, NLS_LANg setting
One thing I know that database character set must support it (WE8MSWIN1252-> OK, WE8ISO8859P1-> Wont work) but what about above 3 settings?
Regards,
OraKaran
[Updated on: Thu, 07 April 2011 03:19] Report message to a moderator |
|
|
|
| Re: Unable to insert Euro Symbol in database with Character set WE8MSWIN1252 [message #502552 is a reply to message #501450] |
Thu, 07 April 2011 03:33 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello
One more thing
After cmd console started displaying the euro symbol correctly I inserted the values in database and tried to display it to see it is not being displayed correctly
C:\Documents and Settings\orakaran>€
'€' is not recognized as an internal or external command,
operable program or batch file.
C:\Documents and Settings\orakaran>chcp
Active code page: 850
C:\Documents and Settings\orakaran>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Apr 7 09:20:07 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: system@testdb
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> insert into e values('€');
1 row created.
SQL> insert into e values('€');
1 row created.
SQL> insert into e values('€');
1 row created.
SQL> insert into e values('€');
1 row created.
SQL> insert into e values('€');
1 row created.
SQL> insert into e values('€');
1 row created.
SQL> insert into e values('€');
1 row created.
SQL> insert into e values('€');
1 row created.
SQL> insert into e values('€');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into e values(chr(128));
1 row created.
SQL> commit
2 ;
Commit complete.
SQL> select * from e;
V
----------
?
?
?
?
?
?
?
?
?
Ç
10 rows selected.
SQL>
SQL>
Also in TOAD which was dealing with Euro symbol correctly when used chr(128)
following were the results
select * from e;
?
?
?
?
?
?
?
?
?
€
Then I tried setting NLS_LANG too but results were same...junk character
C:\Documents and Settings\orakaran>set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
C:\Documents and Settings\orakaran>SQLPLUS
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Apr 7 09:27:25 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: system@testdb
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> delete from e;
10 rows deleted
SQL> insert into e values('€');
1 row created.
SQL> commit;
SQL> select * from e;
V
----------
?
SQL> insert into e values(chr(128));
1 row created.
SQL> commit;
Commit complete.
SQL> select * from e;
V
----------
Ç
?
SQL>
Regards,
OraKaran
|
|
|
|
|
|
| Re: Unable to insert Euro Symbol in database with Character set WE8MSWIN1252 [message #502554 is a reply to message #502550] |
Thu, 07 April 2011 03:38 |
 |
Michel Cadot
Messages: 68499
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Good to know but take care that € is not really supported in code page 850.
See the following example.
On Windows (MSWIN1252)
SQL> desc t
Name Null? Type
-------------------------------- -------- ----------------------
COL1 NUMBER(38)
COL2 VARCHAR2(10 CHAR)
SQL> insert into t values (1,'€');
1 row created.
SQL> commit;
Commit complete.
In DOS:
C:\>chcp 850
Page de codes active : 850
C:\>sqlplus michel/michel
SQL*Plus: Release 10.2.0.4.0 - Production on Jeu. Avr. 7 10:33:07 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> select * from t;
COL1 COL2
---------- ----------
1 Ç
1 row selected.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
C:\>chcp 1252
Page de codes active : 1252
C:\>sqlplus michel/michel
SQL*Plus: Release 10.2.0.4.0 - Production on Jeu. Avr. 7 10:30:47 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> select * from t;
COL1 COL2
---------- ----------
1 €
1 row selected.
The fact you can type € sign is misleading, see:
C:\>chcp 850
Page de codes active : 850
C:\>sqlplus michel/michel
SQL*Plus: Release 10.2.0.4.0 - Production on Jeu. Avr. 7 10:33:07 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> insert into t values(2,'€');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
COL1 COL2
---------- ----------
1 Ç
2 ?
2 rows selected.
Back to Windows:
SQL> col dmp format a20
SQL> select col1, col2, dump(col2) dmp from t;
COL1 COL2 DMP
---------- ---------- --------------------
1 € Typ=1 Len=1: 128
2 ? Typ=1 Len=1: 63
2 rows selected.
As you can see, with code page 850 you didn't actually enter a € sign but a replacement one.
Regards
Michel
[Updated on: Thu, 07 April 2011 03:40] Report message to a moderator |
|
|
|
| Re: Unable to insert Euro Symbol in database with Character set WE8MSWIN1252 [message #502555 is a reply to message #501450] |
Thu, 07 April 2011 03:58 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello Michel and Cookiemonster
Just checked it with chcp 1252 and read your reply
Yes it works with chcp 1252
With chcp 850 it displays the symbol which really has different value
Many Thanks for your patience
To summarize
The editor (windows console) here needed code page + font setting
I did not even set NLS_LANG in my last test
So can we say, to correctly display and feed in the special characters we need code page + font settings of the editor/console and to accomodate the characters correctly in database we need specific database character set?
I am unable to co-relate NLS_LANG here.
If possible could you give a brief answer how it is (NLS_LANG) related to above?
Many thanks for your help on this
Regards,
OraKaran
|
|
|
|
|
|
| Re: Unable to insert Euro Symbol in database with Character set WE8MSWIN1252 [message #502585 is a reply to message #501450] |
Thu, 07 April 2011 08:34 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Thanks Michel
Yes I checked In registry and things are as you have said
I have 2 Oracle homes
[1] Oracle Client (only)
[2] Oracle Forms/ Developer D2k
[1] has NLS_LANG = ENGLISH_UNITED KINGDOM.WE8MSWIN1252 in registry key, HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\HOME1
[2] has NLS_LANG = AMERICAN_AMERICA.WE8ISO8859P1 in registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\HOME0
Database has character set AMERICAN_AMERICA.WE8MSWIN1252
So though LANGUAGE and Territory does not matches (not good) characteset is same as registry entry of [1]
Obviuously data inserted and displayed from [1] correctly but not from [2]
Many Thanks for your help
Regards,
OraKaran
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
