Re: Problems going from US7ASCII to WE8ISO8859P1

From: Anatoly Moskovsky <avm_at_trais.com.ua>
Date: Tue, 28 Aug 2001 11:10:57 +0300
Message-ID: <TRS11555E8CB@trais.com.ua>

Hi!

• On august 28 Allen wrote:
  >> I changed the character set of our production database from US7ASCII
  >> to
  >> WE8ISO8859P1. Now the "cent" symbol looks like a quotation mark. I
  >> thought WE8ISO8859P1 was a superset of US7ASCII and this wouldn't
  >> cause any problems. Now I'm worried that I can't go back to subset
  >> after using the superset character set. Anyone know anything about
  >> this? The data is stored in CLOB columns. Is there an easy way to
  >> update my CLOB data to replace the old cent character with the new
  >> one? Any other conversions I need to be aware of?
  

 A> Me again. Heres the latest.

 A> Even though we were using US7ASCII (a 7 bit char set) before,
 A> apparently some character stored in LONG columns were > 128 ascii..
 A> ie, 162 = the cent symbol.  When I imported this data into a new
 A> WE8ISO8859P1 database, oracle decided to "trunc" the characters > 128
 A> by subtracing 128 from them.  The end result was the cent symbol
 A> (162) ended up being the "
 A> symbol (34).

Maybe the cent symbol's code is beyond the WE8ISO8859P1 charset. Such symbols are replaced by ? during translation

 A> Why did this happen and how should I have prevented it? 162 with the

If you had chosen the correct charset before creating the DB you would not have any problem.
Never store data in charset different to the DB charset.

 A> WE8ISO8859P1 char set worked as a cent symbol, I just wish the  A> original 162's had stayed 162. This has been a MAJOR pain in the ass.

Try this(with the original DB, I have not tried this but it should work). - set the charset part of NLS_LANG to US7ASCII on both the server and the client
  ( to disable charset translation)

- make export of your tables, not schemas
- change database charset
- change NLS_LANG accordingly
- make a dummy export file (for instance, export any table with rows=n)
- (this part is most likely not supported by Oracle and may cause problems

with Oracle support service)
the bytes #01,02 (starting from 0) contain the charset of the dump file(your new charset). Replace the old charset in the first dump by these bytes. - delete your data and make import with ignore=y

Bye

---

Anatoly Moskovsky, Oracle Developer avm_at_sqlbatch.com [SQL Batch/Oracle, DBA tools, reverse engineering, scripting extensions]: http://sqlbatch.com/sb/ Received on Tue Aug 28 2001 - 03:10:57 CDT