Hi,
I'm stuck with this problem , although I'm not sure whether it's a display or a storing problem.
Oracle 9i on Win32 Platform, character set MSWIN1252
On Oracle previous version, I remember that some character (like WE8ISO 8859P15) sets were able to store the EURO symbo (looks like a C with two horizontal bars in the middle)
My current Oracle box currently uses the MSWIN1252 character set, and I have no clue how to store / display this characters
I can insert chr(128) but it displays like a black square in SQL+
Is it that SQL+ can't display non-ascii characters, or am I using the wrong character set (I'm afraid I never had the choice during the install)
Any suggestions welcome
Hilaire
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
I can insert chr(128) but it displays like a black square in SQL+
Is it that SQL+ can't display non-ascii characters, or am I using the wrong character set (I'm afraid I never had the choice during the install)
Most likely it's SQLPLUS display problem.
BTW, you can test this by displaying the data in the Web pages , IE usually can display almost all kinds of characterset including Asian ones.
Business Accounts
Answer for Membership
by: slightwvPosted on 2004-05-04 at 08:57:41ID: 10987984
Based on the couple of notes I've read I believe it's a display problem not a storage problem. Try your select statement in sqlplus command line version. It appears that chr(128) is correct for the MSWIN1252 character set. You might also look into taking the DB to WE8ISO8859P1 character set. The following is a note from metalink that should explain things:
Note: even though it talks about the upside down question mark, it's the same problem.
fact: Oracle Server - Enterprise Edition 8
fact: Database character set WE8ISO8859P1
symptom: Euro symbol turns up as upside-down question mark ('¿')
cause: The cause of the problem is that the WE8ISO8859P1 character set does
not define Euro symbol and therefore you cannot store the Euro symbol in
databases with that character set.
fix:
To understand the rest of this explanation is good to understand a few basic
things:
+ Every machine runs in a certain character set (or codepage, which we use as a
synonym for character set), a Western Windows machine normally runs in the MS
Active Code Page 1252. This has nothing to do with Oracle, it's simply defined
at OS level. This character set defines a number of characters at certain 'code
points'. For example the Euro symbol is defined at code point 128 in the MS
Active Code Page 1252 (which is why you get it by typing 128 on the numeric
keyboard).
+ Every character set defines a number of symbols. In a 8-bit character set
there is obviously only place for 255 characters so every character set makes
some choices over which characters are defined and which aren't. Most character
sets leave some blocks open for later use as well so the number of defined
characters is usualy smaller than 255.
+ Oracle supports very many different character sets and gives them an Oracle
name. The Oracle name for the MS Active Code Page 1252 is WE8MSWIN1252, the
Oracle name for the ISO-8859-P1 character set (defined by the ISO) is
WE8ISO8859P1, etc. etc.
+ The NLS_LANG on the client is used to inform the Oracle software about the
client character set. A common misconception is that it is used to set the
character set, that is incorrect. The character set is set on the OS, the
NLS_LANG is only there to tell the Oracle software which character set is used
so it can interpret the character codes correctly.
+ The database is created in a certain character set which means that that
defines the characters that you can store in that database.
+ If there is a difference in the database character set and the NLS_LANG value
that tells us the client character set, the NLS modules will convert the
character codes between the 2 character sets.
So, with that behind us lets have a look at this problem:
This issue are all ultimately caused by the fact that your database character
set is WE8ISO8859P1, the Euro symbol is NOT defined in WE8ISO8859P1 so you
cannot store the Euro symbol in a P1 database.
In some cases this might have worked in an older environment. The reason for
that is that you probably had the NLS_LANG set to WE8ISO8859P1 as well. This
incorrectly tells oracle that there is no conversion needed because the
incoming characters are already encoded as P1 characters. So what happened is
that you type the Euro symbol, the client software passes 'character 128' on to
Oracle, Oracle determines no conversion is needed because we have told it that
the client and database charset are the same. We send 128 to the database where
is gets stored. Whilst in the database that 128 doesn't mean anything, 128 is
empty location in WE8ISO8859P1 (which is lucky, it could have ended up at a
codepoint that actualy defined a completely different character). When you come
to query oracle sends 128 back out, it doesn't know what it means but simply
passes it on to the client, we again find that the NLS_LANG is set to the
database charset so we don't try any conversion and pass the 128 back to the OS.
The OS knows what to do with character 128 and displays the Euro symbol.
So although this setup is wrong you still end up with a 'working' situation.
You will get into problems though when you want to convert this database to a
different charset and/or when you add clients with a completely different
charset (Mac clients for example).
Basicaly you've switched off the NLS conversion by setting the NLS_LANG equal
to the database character set and that means that although the database is
defined as a WE8ISO8859P1 database, the character codes that are stored are
actualy from the WE8MSWIN1252 character set.
Back to the current situation now. You have now correctly set the NLS_LANG to
WE8MSWIN1252. That means that Oracle now understands the characters your typing
correctly and it knows how to convert to the database character set. So now
everything gets store.
d in the correct codes. That means that when you now try
to insert the Euro symbol Oracle knows that that is not defined in WE8ISO8859P1.
If we convert a character that is not defined in the destination character
set we use a replacement character, the default replacement character in
WE8ISO8859P1 is the upside down question mark so we replace the Euro symbol
with that. When you now query the data you indeed find that the Euro symbol has
been replaced by a upside down question mark.
There is only 1 real solution for this issue and that is to use a database
character set where the TM sign is defined. This could for exampale be
WE8ISO8859P15 or WE8MSWIN1252. WE8MSWIN1252 is the ideal character set to use
in the database for this situation since the clients run on Windows and
therefore use that character set themselves (it guartantees that you can store
all the characters without running into this problem again).
The conversion from WE8ISO8859P1 to WE8MSWIN1252 is very simple. Because
WE8MSWIN1252 is a superset of WE8ISO8859P1 (it defines exactly the same set of
characters plus some more, it basicaly fills some of the undefined spaces in
WE8ISO8859P1) you can use the ALTER DATABASE CHARACTER SET command which will
only take a few seconds. This is described fully in <note:66320.1>
The alternative would be to revert to the setup to the old situation in which
this used to 'work' and set NLS_LANG to WE8ISO8859P1. However, like described
above this is SIMPLY NOT CORRECT and the only thing that you do is fooling
yourself. The database character set might be WE8ISO8859P1 but in this setup
you still store the WE8MSWIN1252 codes in your database..