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

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

[ This got warnocked on dbi-users, so I'm posting here too.
  When I originally posted, I was using DBI 1.41.  I have since
  upgraded to DBI 1.50 which did not help. ]


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 
<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.50
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