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 -> Insert Statement put invalid character data into UTF8 database/column

Insert Statement put invalid character data into UTF8 database/column

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 3 Jul 2006 00:13:25 -0700
Message-ID: <1151910805.073065.174050@h44g2000cwa.googlegroups.com>


Hi all. [Oracle 9i - 9.2.0.1.0]

A while back we had an incident where a insert statement in some script file (executed via sqlplus) managed to get us invalid data into a VARCHAR2 column in the database.

All character data in the DB is UTF8 and this script with insert statements happend to contain a character from the upper ascii range ('▀' - 0xDF in ISO-8859-1) The script file itself is encoded in this character set (8859).

No what happened is, that in some cases the bytes of the UTF8 column did not contain the correct UTF8 encoding of this character, but the byte-value 0xDF was just inserted into the column. That is after running the insert statement
INSERT into my_table (id, abbreviation, name) values (62, 'F1', '20 mm Falzboden m. Verschlu▀l.');
the UTF8-column did contain an _invalid_ UTF8 string, namely a byte-string that contained 0xDF.

Naturally queries on this column-rows failed afterwards (e.g. in JAVA you get an invalid-encoding-exception)

Can someone explain to me why a simple insert statements with no encoding convertions manages to put an invalid string into a database column?

thanks a lot!

best,
Martin Received on Mon Jul 03 2006 - 02:13:25 CDT

Original text of this message

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