Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Insert Statement put invalid character data into UTF8 database/column

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

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 4 Jul 2006 02:08:44 -0700
Message-ID: <1152004124.022358.148580@v61g2000cwv.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US