Oh yeah a few more quick questions
1) Which version of 11g are you running?
2) Has it been Patched?
3) can you to a make test and send me the results
Just need the above to have the proper talks with my Oracle chum
cheers
John Scoles
> 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.
>
>
