(note: this is a response to a different thread, but I'm changing the subject,
so have changed the subject line).
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).
Basically, since the default format is not scientific notation, the string
that is returned must be
(significant digits of mantissa) + (digits of exponent) <= 132,
and the 133rd character is the decimal point.
There are a couple different workarounds that can be used, but the one I suggested was to round the values to something more scientifically appropriate before storing them. A more robust solution is to store mantissa and exponent separately (esp. see below), but there are various reasons this won't work well in our case.
I also found that anything smaller than 1E-130 is silently converted by Oracle to 0. This behavior is utterly independent of DBI or DBD, but I thought I'd mention it in case anyone else is dealing with very small numbers.
Anyway, I bring this up just in case anyone else has gotten bitten by this.
I don't know if it constitutes a DBD::Oracle bug as such.
