Received: (qmail 20906 invoked from network); 11 Jan 2010 16:17:23 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-85-25-126-90.inaddr.intergenia.de with SMTP; 11 Jan 2010 16:17:03 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2F8A4CC5FD5; Mon, 11 Jan 2010 17:16:20 -0500 (EST) X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id rgFWba1ycmlB; Mon, 11 Jan 2010 17:16:19 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5F568CC59E2; Mon, 11 Jan 2010 17:15:39 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 11 Jan 2010 17:14:56 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B5125CC6B4C for ; Mon, 11 Jan 2010 17:14:56 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id GW5kEL+vlefc for ; Mon, 11 Jan 2010 17:14:56 -0500 (EST) Received: from mail-px0-f184.google.com (mail-px0-f184.google.com [209.85.216.184]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 25E77CC6B27 for ; Mon, 11 Jan 2010 17:14:55 -0500 (EST) Received: by pxi14 with SMTP id 14so7052712pxi.20 for ; Mon, 11 Jan 2010 14:14:55 -0800 (PST) Received: by 10.141.214.24 with SMTP id r24mr9574561rvq.27.1263248094350; Mon, 11 Jan 2010 14:14:54 -0800 (PST) Received: from ?192.168.1.7? (pool-173-50-249-143.ptldor.fios.verizon.net [173.50.249.143]) by mx.google.com with ESMTPS id 23sm21618460pzk.4.2010.01.11.14.14.50 (version=TLSv1/SSLv3 cipher=RC4-MD5); Mon, 11 Jan 2010 14:14:53 -0800 (PST) Cc: oracle-l L Message-Id: <2C9EB49F-7205-4057-81C2-E9AF60ECD5C5@furfly.net> From: Janine Sisk To: "Bobak, Mark" In-Reply-To: <6AFC12B9BFCDEA45B7274C534738067F36AF4FDF@AAPQMAILBX02V.proque.st> Content-Type: multipart/alternative; boundary=Apple-Mail-59--674435273 Mime-Version: 1.0 (Apple Message framework v936) Subject: Re: Replacement of US7ASCII character set in 11g? Date: Mon, 11 Jan 2010 14:14:49 -0800 References: <4AB31293-AF0A-4B8D-8F97-914F5174D0D3@furfly.net> <6AFC12B9BFCDEA45B7274C534738067F36AF4FDF@AAPQMAILBX02V.proque.st> X-archive-position: 24458 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: janine@furfly.net Precedence: normal Reply-to: janine@furfly.net List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --Apple-Mail-59--674435273 Content-Type: text/plain; charset=WINDOWS-1252; format=flowed; delsp=yes Content-Transfer-Encoding: quoted-printable Mark, thanks for that! You gave me the idea to run CSSCAN and tell it =20= to convert the data from WE8MSWIN1252 to UTF8. Voila, no more =20 errors. At least now I know what Oracle thought it was doing when =20 this data was inserted. So (I think) what I need to do is get the data out of 8.1.7, but have =20= Oracle treat it as WE8MSWIN1252 as-is, without converting it. If it =20 tries to convert it, I'll probably lose all those 128 and above =20 characters. I am going to try experimenting with this, but if anyone has any =20 educated guesses I'd love to hear them. Also, sadly I no longer have Metalink/MOS access; I am doing this as =20= an outside contractor to one tiny piece of a huge organization, and I =20= am not allowed to use their CSI. I used to have my own, but over the =20= years my smaller clients have all converted to Postgres; our database =20= usage is fairly minimal and doesn't justify the licensing expense. =20 So, no more Metalink for me. :( janine On Jan 11, 2010, at 11:49 AM, Bobak, Mark wrote: > Hi Janine, > > Problem is that =93LOSSY=94 may mean that the data is already = corrupted =20 > in the source database, so, Oracle has no idea what to convert it =20 > to. In the case of US7ASCII, character values range from 0-127, =20 > correct? So, if you have any values 128 or greater, Oracle has no =20 > idea what character that value should map to. It would depend on =20 > what *assumption* the client code was operating under when the data =20= > was inserted. So, it doesn=92t matter if AL32UTF8 is the =93mother of = =20 > all character sets=94. Even if AL32UTF8 *does* have the character you = =20 > need, it doesn=92t matter, cause Oracle doesn=92t know what that =20 > character is in the source. If it can=92t determine that, it can=92t =20= > map it to the correct character in the destination character set. > > See Doc ID 444701.1 =93CSSCAN Output Explained=94, for more info, =20 > particularly =93B.4) LOSSY data=94. > > Hope that helps, > > -Mark > > From: oracle-l-bounce@freelists.org = [mailto:oracle-l-bounce@freelists.org=20 > ] On Behalf Of Janine Sisk > Sent: Monday, January 11, 2010 2:37 PM > To: oracle-l L > Subject: Re: Replacement of US7ASCII character set in 11g? > > Thanks to everyone who replied.... > > I ran CSSCAN on the original 8.1.7 database and, of course, ran into =20= > trouble right away. The conversion from US7ASCII to WE8MSWIN1252 is =20= > lossy in a number of places. This does not surprise me terribly; =20 > Jared mentioned that you can put "invalid" data into a database of =20 > type US7ASCII and I'm pretty sure that all of the programmers who =20 > have worked on this site over the years have just assumed that if =20 > the database didn't choke on it, then it was ok. > > What concerns me is that CSSCAN reports that converting to UTF8 will =20= > have the exact same lossy conversions. The two error files are =20 > literally identical except for the value of TOCHAR. I thought that =20= > UTF8 was the mother of all character sets, so where do I go from here? > > As an example, one of the first errors reported looks like this when =20= > I do a SELECT in sqlplus: > > Lic. en medios de comunicaciÃ?3n > > I will be digging further into this, with The Google and all, but if =20= > anyone has any light to shed, please do! > > janine > > On Jan 7, 2010, at 3:07 PM, David Mann wrote: > > > When I had clients worried about character set conversions I usually =20= > ran Character Set Scanner utility CSSCAN on a copy of the database =20 > to check for differences. Here is the reference in the 10g =20 > documentation, assuming it is still available in 11g but don't have =20= > a link handy. > > = http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch12scanner= .htm > > --=20 > Dave Mann > www.brainio.us > www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml > --- Janine Sisk President/CEO of furfly, LLC 503-693-6407 --Apple-Mail-59--674435273 Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Mark, thanks for that!  You gave me the idea to run CSSCAN = and tell it to convert the data from WE8MSWIN1252 to UTF8. =  Voila, no more errors.  At least now I know what Oracle = thought it was doing when this data was = inserted.

So (I think) what I need to do = is get the data out of 8.1.7, but have Oracle treat it as WE8MSWIN1252 = as-is, without converting it.  If it tries to convert it, I'll = probably lose all those 128 and above = characters.

I am going to try experimenting = with this, but if anyone has any educated guesses I'd love to hear = them.

Also, sadly I no longer have Metalink/MOS = access;  I am doing this as an outside contractor to one tiny piece = of a huge organization, and I am not allowed to use their CSI.  I = used to have my own, but over the years my smaller clients have all = converted to Postgres;  our database usage is fairly minimal and = doesn't justify the licensing expense.  So, no more Metalink for = me. :(

janine

On Jan = 11, 2010, at 11:49 AM, Bobak, Mark wrote:

Hi = Janine,
See = Doc ID 444701.1 =93CSSCAN Output Explained=94, for more info, = particularly =93B.4)  LOSSY data=94.
Hope = that helps,

<= div>
---
Janine = Sisk
President/CEO of furfly, = LLC
503-693-6407



 =
= --Apple-Mail-59--674435273-- -- http://www.freelists.org/webpage/oracle-l