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: <sybrandb_at_yahoo.com>
Date: 3 Jul 2006 02:41:08 -0700
Message-ID: <1151919667.988510.102230@j8g2000cwa.googlegroups.com>

Martin T. wrote:
> 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

Invalid setting of NLS_LANG on the client, if a client is involved.

Check out NLS_LANG in your NLS_SESSION_PARAMETERS view.

-- 
Sybrand Bakker
Senior Oracle DBA
Received on Mon Jul 03 2006 - 04:41:08 CDT

Original text of this message

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