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)

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.