Insert Statement put invalid character data into UTF8 database/column

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