DBD::Oracle error when switching between utf8 and non-utf8

[Garrett, Philip \(MAN-Corporate\)]

Hi list.

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 made a script for the test suite to reproduce the problem.  You can
get it from
  http://www.pgarrett.net/rebind_nchar.t

Is this the appropriate forum for this kind of thing?  Should I post
elsewhere also?

Thanks much,
Philip


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