UTF-8 - Oracle issue

Question

Possible Duplicate:
DBD::Oracle and utf8 issue

I set my NLS_LANG variable as 'AMERICAN_AMERICA.AL32UTF8' in the perl file that connects to oracle and tries to insert the data.

However when I insert a record with one value having this 'ñ' character the sql fails. But if I use 'Ñ' it inserts just fine.

What am I doing wrong here?

Additional info: If I change my NLS_LANG to 'AMERICAN_AMERICA.UTF8' I can insert 'ñ' just fine...

Answer 1

What does it fail with ?

Generally if there is a problem in character conversion it fails quietly (eg recording a character with an inappropriate translation). Sometimes you get an error which indicates that the column isn't large enough. This is typically when trying to store, for example, a character that takes up two or three bytes in a column that only allows one byte.

First step is to confirm the database settings

select * from V$NLS_PARAMETERS where parameter like ‘%CHARACTERSET%’;

Then check the byte composition of the strings with a:

select dump('ñ',16), dump('Ñ',16) from dual;

Answer 2

The first query gives me:

1   NLS_CHARACTERSET    AL32UTF8
2   NLS_NCHAR_CHARACTERSET  AL16UTF16

The second query gives me:

1   Typ=96 Len=2: c3,b1 Typ=96 Len=2: c3,91

My exact db and perl settings are listed in this question: http://stackoverflow.com/questions/3016128/dbdoracle-and-utf8-issue