Is Your PC? |
|---|
| * Slow |
| * Crashing |
| * Blue-Screening |
| * Not Connecting |
| * Misbehaving |
| * Infected |
Products |
|---|
| * Home PCs |
| * Anti-Virus Software |
Services |
|---|
| * Computer Service |
| * Network Setup |
| * Web Hosting |
|
|
| * Price List |
| * Home |
| * Contact Us |
Groby Computers Ltd - Leicester
Oracle Database Administration
| Useful Stuff | Oracle Scripts | Unix Scripts |
Trouble displaying the Euro (&euro) symbol with WE8MSWIN1252 / WE8ISO8859p15 character sets
Symptoms:
=========
Your production database is WE8ISO8859p15 and your development database is WE8MSWIN1252 ( or the other way around ) and the euro symbol displays incorrectly.
Oracle version – Generic
This is a common problem in cross platform dev environments:
The obvious cost advantage to develop on a relatively inexpensive Windows platform and then deploy to the more commonly employed Unix ( or these days Linux ) environment, is clear. However it can come with a hidden cost...
Cause:
======
Although the Euro symbol can be displayed in both character sets, there is no mapping between the two.
To understand why, let us take a quick trip through history. The ISO/IEC 8859-1 ( aka Latin-1 ) standard was published around 1987 and consisted of an 8-bit character set with the 128 7-bit ANSI standard ( ASCII ) characters as the first half. It goes on to define various symbols such as the copyright symbol, registered trademark, common fractions, angle quote brackets as well as a host of accented vowels used in european languages. Of particular interest to our discussion is the fact that this standard does not define pritable characters in the ranges 0-31 and 127-159. Finally, the 8859-1 has one last point of interest and that is that character 164 is defined as a currency symbol.
Further parts to this standard were added including, in 1999, p15 from which comes WE8ISO8859p15 ( aka Latin-9 ) in which the currency symbol ( character 164 ) is displayed as the Euro (&euro).
The WE8MSWIN1252 character set also has its origins in the ISO/IEC 8859-1 standard. The difference is that Microsoft mapped characters in the range 127-159 ( which are not printable in 8859 ) as a variety of printable characters including character 128 - the Euro (&euro) symbol.
There is no mapping between the Euro symbol ( character 128 ) in WE8MSWIN1252 and the currency symbol ( character 164 ) in the ISO 8859 character sets, as the representation of character 164 differs in the different parts. Also WE8MSWIN1252 it is not a standard ( like the ISO or ANSI character sets ) so Standards organisations may be reluctant to base descisions on it.
Solution
========
For applications developed and run in cross platform environments, the advise is to use databases with the UTF-8 character set. To check to see how your database has been created run the following ...
SQL> select * from sys.props$
Here is an example using the Euro symbol in a UTF-8 character set database...
If doing inserts using putty, be sure to set the Window\Translation to UTF-8 also to allow the characters to be displayed correctly...
SQL> create table njs ( euro varchar2(10) );
Table created.
SQL> insert into njs values ( chr(14844588)||'1000.00' );
1 row created.
SQL> select * from njs;
EURO
----------
€1000.00
Here chr(14844588) is the multi-byte UTF-8 representation of the Euro symbol. Although the symbol is just 1 character, it is stored as 3 bytes ...
SQL> select length(euro) from njs;
LENGTH(EURO)
------------
8
SQL> select vsize(euro) from njs;
VSIZE(EURO)
-----------
10
... so care must be taken to define the column width to allow for this ...
SQL> insert into njs values ( chr(14844588)||'10000.00' );
insert into njs values ( chr(14844588)||'10000.00' )
*
ERROR at line 1:
ORA-12899: value too large for column "SYS"."NJS"."EURO" (actual: 11, maximum: 10)
Warning
=======
There is another scenario where things may appear to go ok. Say the development environment and end-user client are both WE8MSWIN1252 but the database is WE8ISO8859p15. There are some symbols where it is best for no attempt to translate between the character sets gives the right result ( the euro symbol being a case in point ) but others where translation is desired such as some of the letters that have a Caron (e.g. š) or the ligature (e.g. œ).
If no translation is desired, a successful insert cannot be verified in the 8859p15 environment. If translation is desired, care is needed to set NLS_LANG correctly.
Both of these situations are a step away from ideal and given human nature and enough time, mistakes are bound to happen.
