Hi,
We have found some of our code that worked perfectly well with 9i
started failing when we moved the database to 11g. We are using
DBD::Oracle 1.22.
I have managed to create a very simple test case ...
#!/usr/bin/perl -w
use strict;
use warnings;
use DBI qw();
my $uidpwd = 'usr/p...@db';
my $dbh = DBI->connect('dbi:Oracle:', $uidpwd, '', {RaiseError => 1,
PrintError => 0});
$dbh->trace(3);
my $sth = $dbh->prepare(q(
select :dummy
from dual
union all
select :dummy
from dual
));
$sth->bind_param(':dummy', 100);
$sth->execute;
my $data = $sth->fetchall_arrayref;
END {
if (defined $dbh) {
$dbh->disconnect;
}
}
If I run this connecting to a 9i instance, everything is fine. However
if I run it connecting to an 11g instance, it crashes with an ORA-24345.
Here is the output of a level 3 trace for the *9i* run ...
DBI::db=HASH(0x82df56c) trace level set to 0x0/3 (DBI @ 0x0/0) in
DBI 1.605-ithread (pid 29269)
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x82df5d8)~0x82df56c '
select :dummy
from dual
union all
select :dummy
from dual
') thr#8153008
dbd_preparse scanned 1 distinct placeholders
dbd_st_prepare'd sql SELECT (pl1, auto_lob1, check_sql1)
dbd_describe SELECT (EXPLICIT, lb 80)...
Described col 1: dbtype 1(VARCHAR), scale 0, prec 2000, nullok 1,
name :DUMMY
: dbsize 2000, char_used 0, char_size 2000, csid 873, csform
1, disize 2000
fbh 1: ':DUMMY' NULLable, otype 1-> 5, dbsize 2000/2001, p2000.s0
row cache OCI_ATTR_PREFETCH_ROWS 14, OCI_ATTR_PREFETCH_MEMORY 0
rs_array_init: rs_array_on=0, rs_array_size=1
calling OCIAttrSet OCI_ATTR_CHARSET_FORM with csform=1
dbd_describe'd 1 columns (row bytes: 2000 max, 1000 est avg, cache:
14)
<- prepare= DBI::st=HASH(0x82e905c) at 11g.plx line 14
-> bind_param for DBD::Oracle::st (DBI::st=HASH(0x82e905c)~0x82e9038
':dummy' 100) thr#8153008
dbd_bind_ph(): bind :dummy <== 100 (type 0 (DEFAULT (varchar)))
dbd_rebind_ph_char() (1): bind :dummy <== 100 (size 3/4/0, ptype
5(VARCHAR), otype 1 )
dbd_rebind_ph_char() (2): bind :dummy <== '100' (size 3/4, otype
1(VARCHAR), indp 0, at_exec 1)
bind :dummy as ftype 1 (VARCHAR)
dbd_rebind_ph(): bind :dummy <== 100 (in, not-utf8, csid 1->0->1, ftype
1 (VARCHAR), csform 0->0, maxlen 4, maxdata_size 0)
<- bind_param= 1 at 11g.plx line 21
-> execute for DBD::Oracle::st (DBI::st=HASH(0x82e905c)~0x82e9038)
thr#8153008
dbd_st_execute SELECT (out0, lob0)...
Statement Execute Mode is 0 (DEFAULT)
in ':dummy' [0,0]: len 3, ind 0, value=100
dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
<- execute= '0E0' at 11g.plx line 22
-> fetchall_arrayref for DBD::Oracle::st
(DBI::st=HASH(0x82e905c)~0x82e9038) thr#8153008
dbd_st_fetch 1 fields...
dbd_st_fetched 1 fields with status of 0(SUCCESS)
dbd_st_fetch 1 fields...
dbd_st_fetched 1 fields with status of 0(SUCCESS)
dbd_st_fetch 1 fields...
dbd_st_fetch no-more-data
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x82e9038)~INNER)
thr#8153008
<- DESTROY= undef
-> trace for DBD::Oracle::db (DBI::db=HASH(0x82df5d8)~0x82df56c 0)
thr#8153008
<- trace= 3 at 11g.plx line 27 via at 11g.plx line 0
Here is the *11g* level 3 trace output ...
DBI::db=HASH(0x82df524) trace level set to 0x0/3 (DBI @ 0x0/0) in
DBI 1.605-ithread (pid 29253)
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x82df590)~0x82df524 '
select :dummy
from dual
union all
select :dummy
from dual
') thr#8153008
dbd_preparse scanned 1 distinct placeholders
dbd_st_prepare'd sql SELECT (pl1, auto_lob1, check_sql1)
dbd_describe SELECT (EXPLICIT, lb 80)...
Described col 1: dbtype 1(VARCHAR), scale 0, prec 0, nullok 1,
name :DUMMY
: dbsize 0, char_used 0, char_size 0, csid 873, csform 1,
disize 0
fbh 1: ':DUMMY' NULLable, otype 1-> 5, dbsize 0/1, p0.s0
row cache OCI_ATTR_PREFETCH_ROWS 1123, OCI_ATTR_PREFETCH_MEMORY 0
rs_array_init: rs_array_on=0, rs_array_size=1
calling OCIAttrSet OCI_ATTR_CHARSET_FORM with csform=1
dbd_describe'd 1 columns (row bytes: 0 max, 0 est avg, cache: 1123)
<- prepare= DBI::st=HASH(0x82e9014) at 11g.plx line 14
-> bind_param for DBD::Oracle::st (DBI::st=HASH(0x82e9014)~0x82e8ff0
':dummy' 100) thr#8153008
dbd_bind_ph(): bind :dummy <== 100 (type 0 (DEFAULT (varchar)))
dbd_rebind_ph_char() (1): bind :dummy <== 100 (size 3/4/0, ptype
5(VARCHAR), otype 1 )
dbd_rebind_ph_char() (2): bind :dummy <== '100' (size 3/4, otype
1(VARCHAR), indp 0, at_exec 1)
bind :dummy as ftype 1 (VARCHAR)
dbd_rebind_ph(): bind :dummy <== 100 (in, not-utf8, csid 1->0->1, ftype
1 (VARCHAR), csform 0->0, maxlen 4, maxdata_size 0)
<- bind_param= 1 at 11g.plx line 21
-> execute for DBD::Oracle::st (DBI::st=HASH(0x82e9014)~0x82e8ff0)
thr#8153008
dbd_st_execute SELECT (out0, lob0)...
Statement Execute Mode is 0 (DEFAULT)
in ':dummy' [0,0]: len 3, ind 0, value=100
dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
<- execute= '0E0' at 11g.plx line 22
-> fetchall_arrayref for DBD::Oracle::st
(DBI::st=HASH(0x82e9014)~0x82e8ff0) thr#8153008
dbd_st_fetch 1 fields...
dbd_st_fetched 1 fields with status of 1(SUCCESS_WITH_INFO)
!! ERROR: '24345' 'ORA-24345: A Truncation or null fetch error
occurred (DBD ERROR: ORA-01406 error on field 1 of 1, ora_type
1)' (err#1)
<- fetchall_arrayref= [ ] row1 at 11g.plx line 23
ERROR: '24345' 'ORA-24345: A Truncation or null fetch error
occurred (DBD ERROR: ORA-01406 error on field 1 of 1, ora_type
1)' (err#1)
1 <- FETCH= HASH(0x82e91c4)1keys at 11g.plx line 23
DBD::Oracle::st fetchall_arrayref failed: ORA-24345: A Truncation or
null fetch error occurred (DBD ERROR: ORA-01406 error on field 1 of 1,
ora_type 1) [for Statement "
select :dummy
from dual
union all
select :dummy
from dual
" with ParamValues: :dummy=100] at ./11g.plx line 23.
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x82e8ff0)~INNER)
thr#8153008
ERROR: '24345' 'ORA-24345: A Truncation or null fetch error
occurred (DBD ERROR: ORA-01406 error on field 1 of 1, ora_type
1)' (err#1)
<- DESTROY= undef
-> trace for DBD::Oracle::db (DBI::db=HASH(0x82df590)~0x82df524 0)
thr#8153008
ERROR: '24345' 'ORA-24345: A Truncation or null fetch error
occurred (DBD ERROR: ORA-01406 error on field 1 of 1, ora_type
1)' (err#0)
<- trace= 3 at 11g.plx line 27 via at 11g.plx line 0
I found that if I changed the SELECT expression to either
'TO_NUMBER(:dummy)' or '0 + :dummy' it works on 11g (as well as 9i).
I don't know if this is to do with DBD::Oracle code or if it is a change
in behaviour from 9i OCI to 11g OCI, but would appreciate some feedback
from the DBD::Oracle maintainer.
Thanks,
Steve
This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.
