I believe I have tripped a DBD::Oracle bug in the way it binds utf8
parameters. If I create a statement and execute it with a non-utf8
parameter, it works. If I then execute that *same sth* with a utf8
parameter (scalar with UTF8 flag on), I receive the following error:
ORA-01460: unimplemented or unreasonable conversion requested
Example:
use Encode qw(decode);
my $sth = $dbh->prepare("select ? from dual") || die $dbh->errstr;
my $non_utf8 = "X";
$sth->execute($non_utf8) || die $sth->errstr;
my $utf8 = decode('utf8', $non_utf8);
$sth->execute($utf8) || die $sth->errstr; # DIES
The problem does *NOT* occur when:
1) the order is reversed (utf8 before non-utf8) or
2) the statement is prepared again before the second execute or
3) NLS_LANG does not indicate utf8 or
4) ora_csform is specified as SQLCS_NCHAR or SQLCS_IMPLICIT.
I attached a script for the test suite to reproduce the problem.
All of my DBI and DBD::Oracle tests passed.
Perl: 5.8.3
DBI: 1.4.1
DBD::Oracle: 1.17
OS: Linux 2.6.5
Oracle server: 9.2.5
Oracle client: 9.2.4
Oracle database charset: US7ASCII
Oracle nchar charset: AL16UTF16
NLS_LANG env var: AMERICAN_AMERICA.AL32UTF8
Here are the bits from DBI trace that looked relevant:
First execution (non-utf8):
-> execute for DBD::Oracle::st (DBI::st=HASH(0x84121f8)~0x8416818
'X') thr#814f008
bind :p1 <== 'X' (type 0)
rebinding :p1 (not-utf8, ftype 1, csid 0, csform 0, inout 0)
bind :p1 <== 'X' (size 1/2/0, ptype 4, otype 1)
bind :p1 <== 'X' (size 1/1, otype 1, indp 0, at_exec 1)
bind :p1 <== 'X' (in, not-utf8, csid 1->0->1, ftype 1, csform
0->0, maxlen 1, maxdata_size 0)
Second execution (utf8):
-> execute for DBD::Oracle::st (DBI::st=HASH(0x84121f8)~0x8416818
"X") thr#814f008
bind :p1 <== "X" (type 0)
rebinding :p1 (is-utf8, ftype 1, csid 0, csform 0, inout 0)
bind :p1 <== "X" (size 1/2/0, ptype 4, otype 1)
bind :p1 <== 'X' (size 1/1, otype 1, indp 0, at_exec 1)
rebinding :p1 with UTF8 value so setting csform=SQLCS_IMPLICIT
bind :p1 <== "X" (in, is-utf8, csid 1->0->871, ftype 1, csform 0->2,
maxlen 1, maxdata_size 0)
dbd_st_execute SELECT (out0, lob0)...
in ':p1' [0,0]: len 1, ind 0
OCIErrorGet after OCIStmtExecute (er1:ok): -1, 1460: ORA-01460:
unimplemented or unreasonable conversion requested
| Subject: | rebind_nchar.t |
#!perl -w
#
# ensure we can bind utf8-flagged scalars to the same parameters that
# used to bind non-utf8s.
#
use strict;
use Test::More;
unshift(@INC,'t');
require 'nchar_test_lib.pl';
use DBD::Oracle qw(SQLCS_NCHAR SQLCS_IMPLICIT);
$| = 1;
my $dbh;
SKIP: {
plan skip_all => "Unable to run 8bit test, perl version is less than 5.6"
unless ( $] >= 5.006 );
plan skip_all => "Oracle charset tests unreliable for Oracle 8 client"
if ORA_OCI() < 9.0 and !$ENV{DBD_ALL_TESTS};
my $non_utf8 = 'X';
my $utf8;
eval {
require Encode;
Encode->import(qw(is_utf8 decode));
$utf8 = decode('utf8',$non_utf8);
die unless is_utf8($utf8);
};
plan skip_all => "Unable to encode utf8" if $@;
# force client charset to AL32UTF8
set_nls_nchar('AL32UTF8',1);
$dbh = db_handle();
plan skip_all => "Not connected to oracle" if not $dbh;
plan tests => 3;
my $sql = "select ? from dual where 0 = 1";
my $sth = $dbh->prepare($sql);
$sth->execute($non_utf8);
#
# If bug is present, this will cause:
# ORA-01460: unimplemented or unreasonable conversion requested
#
ok($sth->execute($utf8), "utf8 after non-utf8");
$sth->bind_param(1, $utf8, { ora_csform => SQLCS_NCHAR });
ok($sth->execute, "utf8 after non-utf8 using SQLCS_NCHAR");
$sth->bind_param(1, $utf8, { ora_csform => SQLCS_IMPLICIT });
ok($sth->execute, "utf8 after non-utf8 using SQLCS_IMPLICIT");
}
$dbh->disconnect;