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...
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;
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: https://stackoverflow.com/questions/3016128/dbdoracle-and-utf8-issue
