Re: Insert Statement put invalid character data into UTF8 database/column

joel garry wrote:
> Martin T. wrote:
> > Hi all. [Oracle 9i - 9.2.0.1.0]
> > Can someone explain to me why a simple insert statements with no
> > encoding convertions manages to put an invalid string into a database
> > column?
>
> Because a simple insert statement has no encoding conversions. This
> would be a feature. You can even insert 8 bit characters into a 7 bit
> character set this way. exp/imp will convert for you if you tell the
> NLS environment you are changing character sets. This would also be a
> feature. The results may sometimes be considered a misfeature.
>

Oh. Didn't know that. That's why I was confused obviously.

> So what do you want to wind up with? If you want to update the bad
> data, use sqlplus. If you don't want to insert bad data with sqlplus,
>

I already managed to correct the wrong data. I was just confused how it got in there in the first place.

> scrub your data. sqlplus doesn't care. If java can't handle your
> data, don't use java. Is 'ß' considered bad data by UTF8?
>

Just to make sure: 'ß' in it's UTF8-encoded form is of course no bad data. But the 'ß' was there in the UTF8 column encoded as ISO8859-1, so yes, that's bad data because these bytes in the DB do not represent a valid UTF8 string.

> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c13datyp.htm#14968
>
> You might consider patching your database, too.
>

Yes, if I could do that I'd already done it. :) But that's another story.

Anyway thanks a bunch for clarifying this!

best,
Martin Received on Tue Jul 04 2006 - 04:08:44 CDT