Hello,
The trace using Oracle 8i shows (first and subsequent tries)
fbh 1: ':VAR10' NULLable, otype 1-> 5, dbsize 2000/2001, p2000.s0
However, 9i seems to 'learn' the size starting with 0! I use the
following script (xx.pl):
#! /usr/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect('dbi:Oracle:XXXX','XXXX','XXXX');
my $sth = $dbh->prepare(<<END);
SELECT :var15 FROM DUAL
UNION ALL
SELECT :var15 FROM DUAL
END
$sth->bind_param(":var15", "text");
$sth->execute;
$sth->dump_results;
$sth->finish;
$dbh->disconnect;
Here are the results of subsequent runs:
$ DBI_TRACE=3=trc1 ./xx.pl
0 rows (24345: Error while trying to retrieve text for error ORA-24345
(DBD ERROR: ORA-01406 error on field 1 of 1, ora_type 1))
DBD::Oracle::st dump_results failed: Error while trying to retrieve text
for error ORA-24345 (DBD ERROR: ORA-01406 error on field 1 of 1,
ora_type 1) at ./xx.pl line 23.
$ grep otype trc1
fbh 1: ':VAR15' NULLable, otype 1-> 5, dbsize 0/1, p0.s0
bind :var15 <== 'text' (size 4/5/0, ptype 4, otype 1)
bind :var15 <== 'text' (size 4/4, otype 1, indp 0, at_exec 1)
Oracle described the column having size 0. The next run is fine:
$ DBI_TRACE=3=trc2 ./xx.pl
'text'
'text'
2 rows
$ grep otype trc2
fbh 1: ':VAR15' NULLable, otype 1-> 5, dbsize 32/33, p32.s0
bind :var15 <== 'text' (size 4/5/0, ptype 4, otype 1)
bind :var15 <== 'text' (size 4/4, otype 1, indp 0, at_exec 1)
Size is now 32. We try another one - no change:
$ DBI_TRACE=3=trc3 ./xx.pl
'text'
'text'
2 rows
$ grep otype trc3
fbh 1: ':VAR15' NULLable, otype 1-> 5, dbsize 32/33, p32.s0
bind :var15 <== 'text' (size 4/5/0, ptype 4, otype 1)
bind :var15 <== 'text' (size 4/4, otype 1, indp 0, at_exec 1)
Now we make the data longer than 32 characters
('text'->'text012345678901234567890123456789'.
$ DBI_TRACE=3=trc4 ./xx.pl
0 rows (24345: Error while trying to retrieve text for error ORA-24345
(DBD ERROR: ORA-01406 error on field 1 of 1, ora_type 1))
DBD::Oracle::st dump_results failed: Error while trying to retrieve text
for error ORA-24345 (DBD ERROR: ORA-01406 error on field 1 of 1,
ora_type 1) at ./xx.pl line 23.
$ grep otype trc4
fbh 1: ':VAR15' NULLable, otype 1-> 5, dbsize 32/33, p32.s0
bind :var15 <== 'text012345678901234567890123456789' (size
34/35/0, ptype 4, otype 1)
bind :var15 <== 'text012345678901234567890123456789' (size 34/34,
otype 1, indp 0, at_exec 1)
Expected result :-) Once again:
$ DBI_TRACE=3=trc5 ./xx.pl
'text01234567890123456789012345...'
'text01234567890123456789012345...'
2 rows
$ grep otype trc5
fbh 1: ':VAR15' NULLable, otype 1-> 5, dbsize 128/129,
p128.s0
bind :var15 <== 'text012345678901234567890123456789' (size
34/35/0, ptype 4, otype 1)
bind :var15 <== 'text012345678901234567890123456789' (size 34/34,
otype 1, indp 0, at_exec 1)
Oracle has 'learned' the new size... Again:
$ DBI_TRACE=3=trc6 ./xx.pl
'text01234567890123456789012345...'
'text01234567890123456789012345...'
2 rows
$ grep otype trc6
fbh 1: ':VAR15' NULLable, otype 1-> 5, dbsize 128/129,
p128.s0
bind :var15 <== 'text012345678901234567890123456789' (size
34/35/0, ptype 4, otype 1)
bind :var15 <== 'text012345678901234567890123456789' (size 34/34,
otype 1, indp 0, at_exec 1)
No change. Now we change back to the short data:
$ DBI_TRACE=3=trc7 ./xx.pl
'text'
'text'
2 rows
$ grep otype trc7
fbh 1: ':VAR15' NULLable, otype 1-> 5, dbsize 128/129,
p128.s0
bind :var15 <== 'text' (size 4/5/0, ptype 4, otype 1)
bind :var15 <== 'text' (size 4/4, otype 1, indp 0, at_exec 1)
Size stays the same! Once again:
$ DBI_TRACE=3=trc8 ./xx.pl
'text'
'text'
2 rows
$ grep otype trc8
fbh 1: ':VAR15' NULLable, otype 1-> 5, dbsize 128/129,
p128.s0
bind :var15 <== 'text' (size 4/5/0, ptype 4, otype 1)
bind :var15 <== 'text' (size 4/4, otype 1, indp 0, at_exec 1)
fbh 1: ':VAR10' NULLable, otype 1-> 5, dbsize 32/33, p32.s0
No change.
With the to_char workaround, the start ist different:
$ DBI_TRACE=3=trc1 ./xx.pl
'text'
'text'
2 rows
$ grep otype trc1
fbh 1: 'TO_CHAR(:VAR15)' NULLable, otype 1-> 5, dbsize
2000/2001, p2000.s0
bind :var15 <== 'text' (size 4/5/0, ptype 4, otype 1)
bind :var15 <== 'text' (size 4/4, otype 1, indp 0, at_exec 1)
Size 2000, fine! But:
$ DBI_TRACE=3=trc2 ./xx.pl
'text'
'text'
2 rows
$ grep otype trc2
fbh 1: 'TO_CHAR(:VAR15)' NULLable, otype 1-> 5, dbsize 32/33,
p32.s0
bind :var15 <== 'text' (size 4/5/0, ptype 4, otype 1)
bind :var15 <== 'text' (size 4/4, otype 1, indp 0, at_exec 1)
Again 32. We icrease the size
$ DBI_TRACE=3=trc3 ./xx.pl
0 rows (24345: Error while trying to retrieve text for error ORA-24345
(DBD ERROR: ORA-01406 error on field 1 of 1, ora_type 1))
DBD::Oracle::st dump_results failed: Error while trying to retrieve text
for error ORA-24345 (DBD ERROR: ORA-01406 error on field 1 of 1,
ora_type 1) at ./xx.pl line 23.
$ grep otype trc3
fbh 1: 'TO_CHAR(:VAR15)' NULLable, otype 1-> 5, dbsize 32/33,
p32.s0
bind :var15 <== 'text012345678901234567890123456789' (size
34/35/0, ptype 4, otype 1)
bind :var15 <== 'text012345678901234567890123456789' (size 34/34,
otype 1, indp 0, at_exec 1)
Expected failure. Next call is OK:
$ DBI_TRACE=3=trc4 ./xx.pl
'text01234567890123456789012345...'
'text01234567890123456789012345...'
2 rows
$ grep otype trc4
fbh 1: 'TO_CHAR(:VAR15)' NULLable, otype 1-> 5, dbsize
128/129, p128.s0
bind :var15 <== 'text012345678901234567890123456789' (size
34/35/0, ptype 4, otype 1)
bind :var15 <== 'text012345678901234567890123456789' (size 34/34,
otype 1, indp 0, at_exec 1)
After I did all that I realized that UNION modifies the behaviour, but
not fundamentally.
Even with the simple query SELECT :VAR FROM DUAL we get similar results:
$ DBI_TRACE=3=trc1 ./xx.pl
'text'
1 rows
$ grep otype trc1
fbh 1: ':VAR15' NULLable, otype 1-> 5, dbsize 2000/2001,
p2000.s0
bind :var15 <== 'text' (size 4/5/0, ptype 4, otype 1)
bind :var15 <== 'text' (size 4/4, otype 1, indp 0, at_exec 1)
Fine. But on the next try:
$ DBI_TRACE=3=trc2 ./xx.pl
'text'
1 rows
$ grep otype trc2
fbh 1: ':VAR15' NULLable, otype 1-> 5, dbsize 32/33, p32.s0
bind :var15 <== 'text' (size 4/5/0, ptype 4, otype 1)
bind :var15 <== 'text' (size 4/4, otype 1, indp 0, at_exec 1)
We are back at size 32. Now we increase the data length:
$ DBI_TRACE=3=trc3 ./xx.pl
0 rows (24345: Error while trying to retrieve text for error ORA-24345
(DBD ERROR: ORA-01406 error on field 1 of 1, ora_type 1))
DBD::Oracle::st dump_results failed: Error while trying to retrieve text
for error ORA-24345 (DBD ERROR: ORA-01406 error on field 1 of 1,
ora_type 1) at ./xx.pl line 23.
$ grep otype trc3
fbh 1: ':VAR15' NULLable, otype 1-> 5, dbsize 32/33, p32.s0
bind :var15 <== 'text012345678901234567890123456789' (size
34/35/0, ptype 4, otype 1)
bind :var15 <== 'text012345678901234567890123456789' (size 34/34,
otype 1, indp 0, at_exec 1)
As expected.
Very funny! We will file a TAR as soon as we can present it to Oracle in
a manner they can digest :-)
Thank you very much!
Regards
-gerhard
-----Original Message-----
From: Graf, Robert
Sent: Thursday, August 26, 2004 3:31 PM
To: Kircher, Gerhard
Subject: FW: DBD::Oracle 1.15 crash
-----Original Message-----
From: Tim Bunce [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 26, 2004 3:33 PM
To: Graf, Robert
Cc: [EMAIL PROTECTED]
Subject: Re: DBD::Oracle 1.15 crash
On Thu, Aug 26, 2004 at 11:10:00AM +0200, Graf, Robert wrote:
> Hi,
>
> the following statement
>
> SELECT :HeadCrncy FROM DUAL
> UNION ALL
> SELECT :HeadCrncy FROM DUAL
>
> $sth->bind_param(":HeadCrncy","EUR");
>
> crashes with Perl 5.8.0 and DBI (1.32-1.43) and DBD::Oracle 1.15 under
Oracle 9i during the first fetchrow_hashref() with the error:
(Umm "crash" isn't the right word here. It simply fails with
an error. A crash implies something more serious, like a core dump.)
> DBD::Oracle::st fetchrow_hashref failed: ORA-24345: A Truncation or
null fetch error occurred (DBD ERROR: ORA-01406 error on field 1 of 1,
ora_type 1) .. (environment + trace log level 2 list below)
I've reproduced it. The problem is that Oracle "describes" that column
as
having a max length of zero, so that's how much DBD::Oracle allocates
for it.
The trace shows:
col 1: dbtype 1, scale 0, prec 0, nullok 1, name :HEADCRNCY
: dbsize 0, char_used 0, char_size 0, csid 1, csform 1, disize
0
fbh 1: ':HEADCRNCY' NULLable, otype 1-> 5, dbsize 0/1, p0.s0
> It has worked with Oracle 8i - but since we have switched to Oracle 9i
it crashes.
Can you trace the prepare() call using Oracle 8 and show me the
corresponding lines?
> The problem is the UNION ALL. When I don't use the UNION ALL it works.
Without the UNION ALL Oracle describes the result column thus:
col 1: dbtype 1, scale 0, prec 2000, nullok 1, name :HEADCRNCY
: dbsize 2000, char_used 0, char_size 2000, csid 1, csform 1,
disize 2000
fbh 1: ':HEADCRNCY' NULLable, otype 1-> 5, dbsize 2000/2001,
p2000.s0
So there's plenty of room for a varchar.
Looks like this is an Oracle bug. Please file a TAR with Oracle.
Meanwhile, this'll work for you:
SELECT to_char(:HeadCrncy) FROM DUAL
UNION ALL
SELECT to_char(:HeadCrncy) FROM DUAL
Tim.
Die Information in dieser Nachricht ist vertraulich und ausschlie�lich f�r den
Adressaten bestimmt. Der Empf�nger dieser Nachricht, der nicht der Adressat, einer
seiner Mitarbeiter oder sein Empfangsbevollm�chtigter ist, wird hiermit davon in
Kenntnis gesetzt, dass er deren Inhalt nicht verwenden, weitergeben oder reproduzieren
darf. Sollten Sie diese Nachricht irrt�mlich erhalten haben, benachrichtigen Sie uns
bitte unverz�glich per Telefon und retournieren Sie uns die Nachricht per E-Mail/Fax.
The information contained in this e-mail is privileged and confidential and is for the
exclusive use of the addressee. The person who receives this e-mail and who is not the
addressee, one of his employees or an agent entitled to hand it over to the addressee,
is informed that he may not use, disclose or reproduce the contents thereof. If you
have received this communication by mistake, please let us know by telephone without
delay and send it back to us by e-mail/fax.
