Using nvarchar2 columns in perl DBI

I am currently playing around with nvarchar2 columns.

I have Oracle 8i running on Redhat Linux 6.1. The database has NLS_CHARACTERSET=WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET=UTF8. When I use sqlplus to insert and query values in an nvarchar2 column, the values are converted correctly to and from UTF8 depending on the NLS_LANG variable.

So far everything is ok.

But when I use perl (DBI 1.13, DBD-Oracle 1.03), conversion works correctly only when I write into the database:

"insert into nvc_test(text) values(" . "N" . $dbh->quote($ARGV[0]) . ")";

correctly converts the input character set to UTF8 (BTW, is there some more elegant way to do this?).

But when I try to read the table with:

my $sth_select = $dbh->prepare("select nls_lang, text from nvc_test"); $rv = $sth_select->execute();
print "select: $rv\n";
my @ary;
while( (@ary = $sth_select->fetchrow_array)) {

    print $ary[0], "\t", $ary[1], "\n"; }

I get back UTF8, when NLS_LANG is set to WE8ISO8859P1 and something wierd (obviously the result of applying a WE8ISO8859P1->UTF8 conversion to UTF8) when I set it to UTF8.

Am I doing something incredibly stupid in this simple select statement or is there a bug in DBD-Oracle?

        hp

--

   _  | Peter J. Holzer             | Nobody should ever have to be
|_|_) | Sysadmin WSR / Obmann LUGA  | ashamed if they have a secret love
| |   | hjp_at_wsr.ac.at               | for writing computer programs that
__/   | http://wsrx.wsr.ac.at/~hjp/ | actually work.  -- Donald E. Knuth