DBD::Oracle and utf8 issue
- by goe
Hi All,
I have a problem where my perl code using the latest DBD::Oracle on perl v5.8.8 throws an exception on me when I try to insert characters like 'ñ'.
Exception:
DBD::Oracle::db do failed: ORA-01756: quoted string not properly terminated (DBD ERROR: OCIStmtPrepare)
My $ENV{NLS_LANG} is set to 'AMERICAN_AMERICA.AL32UTF8'
These are the DB params based on "SELECT * from NLS_DATABASE_PARAMETERS"
1 NLS_LANGUAGE AMERICAN
2 NLS_TERRITORY AMERICA
3 NLS_CURRENCY $
4 NLS_ISO_CURRENCY AMERICA
5 NLS_NUMERIC_CHARACTERS .,
6 NLS_CHARACTERSET AL32UTF8
7 NLS_CALENDAR GREGORIAN
8 NLS_DATE_FORMAT DD-MON-RR
9 NLS_DATE_LANGUAGE AMERICAN
10 NLS_SORT BINARY
11 NLS_TIME_FORMAT HH.MI.SSXFF AM
12 NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
13 NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
14 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
15 NLS_DUAL_CURRENCY $
16 NLS_COMP BINARY
17 NLS_LENGTH_SEMANTICS BYTE
These are perl params based on "$db-ora_nls_parameters()"
$VAR1 = {
'NLS_LANGUAGE' => 'AMERICAN',
'NLS_TIME_TZ_FORMAT' => 'HH.MI.SSXFF AM TZR',
'NLS_SORT' => 'BINARY',
'NLS_NUMERIC_CHARACTERS' => '.,',
'NLS_TIME_FORMAT' => 'HH.MI.SSXFF AM',
'NLS_ISO_CURRENCY' => 'AMERICA',
'NLS_COMP' => 'BINARY',
'NLS_CALENDAR' => 'GREGORIAN',
'NLS_DATE_FORMAT' => 'DD-MON-RR',
'NLS_DATE_LANGUAGE' => 'AMERICAN',
'NLS_TIMESTAMP_FORMAT' => 'DD-MON-RR HH.MI.SSXFF AM',
'NLS_TERRITORY' => 'AMERICA',
'NLS_LENGTH_SEMANTICS' => 'BYTE',
'NLS_NCHAR_CHARACTERSET' => 'AL16UTF16',
'NLS_DUAL_CURRENCY' => '$',
'NLS_TIMESTAMP_TZ_FORMAT' => 'DD-MON-RR HH.MI.SSXFF AM TZR',
'NLS_NCHAR_CONV_EXCP' => 'FALSE',
'NLS_CHARACTERSET' => 'AL32UTF8',
'NLS_CURRENCY' => '$'
};
Here are some other strange facts:
If I set NLS_LANG to ‘'AMERICAN_AMERICA.UTF8’ the insert executes fine with ‘ñ’ character.
If I leave NLS_LANG as ‘'AMERICAN_AMERICA.AL32UTF8' but use ‘Ñ’ the insert will run fine as well.
