Re: truncation error on fetch of small magnitude numbers

[Chris Sarnowski]

On Sep 23, 2004, at 4:42 AM, Tim Bunce wrote:
On Wed, Sep 22, 2004 at 12:06:27PM -0400, Chris Sarnowski wrote:
On Sep 21, 2004, at 5:05 PM, Tim Bunce wrote:
DBD::Oracle simply asks for the columns as strings and it's Oracle
itself
that performs the number to string conversion. DBD::Oracle gives
Oracle a buffer of 133 characters for NUMBER fields, so there's
pleanty of room to store the number.
Ahh. I've been meaning to ask about this. There is one case
where the 133 char buffer is a problem. We are storing very
small p-values from BLAST, and they can be down in the range
of 1E-128 to 1E-130. Now for numbers of this magnitude it's not
very meaningful to store more than one (or maybe 2) significant
digits anyway, but BLAST returns 6 significant digits, and we store
them in Oracle without trouble, but when we attempt to retrieve them
we get a 'truncation or null fetch error' (In particular, using
DBD::Oracle 1.14 or 1.15 with a couple different versions of Oracle  
9i).
The DBD::Oracle test suite does
        select 1 * power(10,-130) ...
and doesn't get an error. [Though looking at it the tests that follow
aren't useful, but at least there's no 'truncation or null fetch'  
error.]

Can you give me an SQL statement like that above that'll trigger the  
problem?

I don't know if it constitutes a DBD::Oracle bug as such.
I think a 'truncation or null fetch error' for a NUMBER does.
Tim.
Here is my test program. There seems to be some short-circuiting that
causes selects from dual to behave differently. Note that Oracle stores
1E-130 correctly; it rounds anything smaller to 0 (I think this is
mentioned in the Oracle docs).
--- begin testTrunc.pl ---
#!/usr/bin/perl -w
use strict;
use DBI;
# create sequence testnum_seq start with 1 increment by 1;
# create table testnum (
#  id  number,
#  num number);
my @nums =(1E-130, 1.1E-130, 0.9E-130,1.2345E-128, 1.23456E-128);
my ($user, $password, $host, $sid);
my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid", $user,  
$password,
		       {RaiseError => 1})
  or die "cannot connect";

my $dualselect = $dbh->prepare("select ?  from dual");
for my $num (@nums) {
  $dualselect->execute($num);
  while (my ($result) = $dualselect->fetchrow_array()) {
    print "$result\n";
  }
}
my $tinsert = $dbh->prepare("insert into testnum (id, num) values  
(testnum_seq.nextval,?)");
my $tselect = $dbh->prepare("select id, num from testnum");
for my $num (@nums) {
  $tinsert->execute($num);
}

$tselect->execute();
while (my($id, $num) = $tselect->fetchrow_array()) {
  print "$id: $num\n";
}
$dbh->disconnect();
exit 0;
--- end testTrunc.pl ---
[colbek:~/bin] csarnows% ./testTrunc.pl
1e-130
1.1e-130
9e-131
1.2345e-128
1.23456e-128
1:  
.00000000000000000000000000000000000000000000000000000000000000000000000 
00000000000000000000000000000000000000000000000000000000001
2:  
.00000000000000000000000000000000000000000000000000000000000000000000000 
000000000000000000000000000000000000000000000000000000000011
3: 0
4:  
.00000000000000000000000000000000000000000000000000000000000000000000000 
0000000000000000000000000000000000000000000000000000000012345
DBD::Oracle::st fetchrow_array failed: ORA-24345: A Truncation or null  
fetch error occurred (DBD ERROR: ORA-01406 error on field 2 of 2,  
ora_type 2) [for Statement "select id, num from testnum"] at  
./testTrunc.pl line 42.
DBD::Oracle::st fetchrow_array failed: ORA-24345: A Truncation or null  
fetch error occurred (DBD ERROR: ORA-01406 error on field 2 of 2,  
ora_type 2) [for Statement "select id, num from testnum"] at  
./testTrunc.pl line 42.

[note that I removed some white space from the test program above, so  
the line number won't be corrrect]

sqlplus:
SQL> select * from testnum;
        ID        NUM
---------- ----------
         1 1.000E-130
         2 1.100E-130
         3          0
         4 1.235E-128
         5 1.235E-128