Database Software

Hi,

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)

1. My $ENV{NLS_LANG} is set to 'AMERICAN_AMERICA.AL32UTF8'
2. 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

3. 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.