DBD::Oracle and utf8

I have some troubles inserting an UTF8 string into an oracle 10 database on Solaris, using the latest DBD::Oracle on perl v5.8.4.

This are my DB settings

> --------SELECT * from NLS_DATABASE_PARAMETERS-------------------------------
> NLS_NCHAR_CHARACTERSET AL16UTF16
> NLS_LANGUAGE AMERICAN
> NLS_TERRITORY AMERICA NLS_CURRENCY $
> NLS_ISO_CURRENCY AMERICA
> NLS_NUMERIC_CHARACTERS .,
> NLS_CHARACTERSET UTF8
> NLS_CALENDAR GREGORIAN
> NLS_DATE_FORMAT DD-MON-RR
> NLS_DATE_LANGUAGE AMERICAN
> NLS_SORT BINARY
> NLS_TIME_FORMAT HH.MI.SSXFF AM
> NLS_TIMESTAMP_FORMAT DD-MON-RR
> HH.MI.SSXFF AM
> NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
> NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR
> HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $
> NLS_COMP BINARY
> NLS_LENGTH_SEMANTICS CHAR
> NLS_NCHAR_CONV_EXCP FALSE
> NLS_RDBMS_VERSION 10.2.0.4.0
> --------------------------------------------------------------------------

This are my perl $dbh->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' => 'CHAR',
          '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' => 'UTF8',
          'NLS_CURRENCY' => '$'
        };

In my script I have:

use utf-8;
$ENV{NLS_LANG}='AMERICAN_AMERICA.UTF8';
..
$sth->bind_param(5, $myclobfield, {ora_type => ORA_CLOB, ora_csform => SQLCS_NCHAR});
..

The string prints out 1 on

print Encode::is_utf8($m开发者_如何学编程yclobfield);

But characters like òàè are not correctly inserted into the DB. (I tested with a utf8 compliant client that can correctly insert and read them)

Can anyone suggest the best way to do it? Thanks

Ok after couple of hours of hammering, and tampering all the DBAs I could, I Solved it:

I was missing this:

 $ENV{NLS_NCHAR}='AL32UTF16';

also be careful to

utf8::encode($myclobfield);

if you're not sure it's UTF8

Cheers G.

Unicode literals needs to be prefixed with an 'n', like this:

select n'Language - Språk - Język' from dual;

Also, check this: Inserting national characters into an oracle NCHAR or NVARCHAR column does not work