utf8 string truncated when inserted in Oracle !!

[ciarpame]

I have a very strange problem related with oracle DBD driver and utf8 perl encoding.
Consider the following environment:

Oracle 9i with AL32UTF8 charset
A table with name “TEST” and two field: id INTEGER, text LONG
Perl v5.8.6
DBD::Oracle 1.16
DBI 1.46
Linux RH Ent. 3.0

When I try to insert/update any text field of type LONG with any utf8 string, only 2k (more or less) characters of the string (plus some other strange character) are really written on the db and the rest of the string disappears without any error or warning message. I read a lot of docs about unicode/perl/utf8 and Oracle LONG data type handling in DBD/DBI and I’m quite sure that I did everything in the right way (but probably not..).

Here is a simple test script:

#!/usr/bin/perl
binmode(STDOUT,"utf8");
use DBI;
use DBD::Oracle qw(ra_types);
$ENV{"NLS_LANG"}="AMERICAN_AMERICA.AL32UTF8";

my $DBUSER=qq{meweb};
my $DBPASS=qq{meweb};
my $DBNAME=qq{bradipo};
my $DBH = DBI->connect("dbi:Oracle:$DBNAME", $DBUSER, $DBPASS,{AutoCommit => 1});
$DBH->{LongReadLen} = 1024 * 1024; # 1 Mb !!
$DBH->{LongTruncOk} = 0;

my $sqlwrite=qq{update test set text=? where id=1};
my $sthwrite = $DBH->prepare($sqlwrite);
my $sqlread=qq{select text from test where id=1};
my $sthread = $DBH->prepare($sqlread);

my $text="\x{263A}"; # to be sure that $text is marked as utf8 by perl
for($i=1;$i<=1000;$i++){ $text.=qq{$i: test test test test test test test test test \n}; }
$sthwrite->bind_param(1,$text, {ora_type => ORA_LONG}) || die $DBH->errstr;
$sthwrite->execute;
print qq{ ===================== content of variable \$text =============== \n $text };
$sthread->execute || die "Unable to execute query: $dbh->errstr\n";
my @row=$sthread->fetchrow_array;
$sthread->finish;
print qq{ ===================== content of db =============== \n $row[0] };
$DBH->disconnect;

As you can see from the following output when I try to read back the record, the ‘text’ field only contains a small part of the expected data (more or less 2170 characters) plus some bad encoded character.
This happens only with utf8 string. In fact if I remove the following line from the script
my $text="\x{263A}";
the variable $text is tagged by perl as “not utf8” and everything goes fine.

Can you please HELP ME ????


Here is the script output:

===================== content of variable $text ===============
?1: test test test test test test test test test
2: test test test test test test test test test
3: test test test test test test test test test
4: test test test test test test test test test
5: test test test test test test test test test
6: test test test test test test test test test
7: test test test test test test test test test
8: test test test test test test test test test
9: test test test test test test test test test
10: test test test test test test test test test
11: test test test test test test test test test
continue …
1000: test test test test test test test test test
===================== content of db ===============
?1: test test test test test test test test test
2: test test test test test test test test test
3: test test test test test test test test test
4: test test test test test test test test test
5: test test test test test test test test test
6: test test test test test test test test test
7: test test test test test test test test test
8: test test test test test test test test test
9: test test test test test test test test test
10: test test test test test test test test test
11: test test test test test test test test test
12: test test test test test test test test test
13: test test test test test test test test test
14: test test test test test test test test test
15: test test test test test test test test test
16: test test test test test test test test test
17: test test test test test test test test test
18: test test test test test test test test test
19: test test test test test test test test test
20: test test test test test test test test test
21: test test test test test test test test test
22: test test test test test test test test test
23: test test test test test test test test test
24: test test test test test test test test test
25: test test test test test test test test test
26: test test test test test test test test test
27: test test test test test test test test test
28: test test test test test test test test test
29: test test test test test test test test test
30: test test test test test test test test test
31: test test test test test test test test test
32: test test test test test test test test test
33: test test test test test test test test test
34: test test test test test test test test test
35: test test test test test test test test test
36: test test test test test test test test test
37: test test test test test test test test test
38: test test test test test test test test test
39: test test test test test test test test test
40: test test test?? ????? ?À????

 

[mlibeson]

Try reading the Encode docs => perldoc Encode

They may or may not help. You may need to encode your text in the proper charset before importing.


Michael Libeson