Erwan Lemonnier wrote:
Hi again! Thanks Tim for this thorough explanation :)
For the record, DBD::Oracle binds parameters and fetches values as
strings.
Great, now we are getting closer to the heart :)
As I wrote in my first post, the problem can be triggered with the
following perl test:
-----------------<snip>---------------------------
use strict;
use warnings;
use Data::Dumper;
use Test::More tests => 1;
use Carp qw(confess);
use DBI;
# database credentials: EDIT HERE
my $ORASID = $ENV{ORACLE_SID};
my $ORAUSR = 'username';
my $ORAPWD = 'password';
my $DBC;
sub sql_execute {
my ($sql,@arg) = @_;
my $sth = $DBC->prepare($sql);
if(!$sth || $sth->err) {
confess "prepare failed for [$sql]\nbecause: [".$DBC->errstr."]";
}
$sth->execute(@arg) ||
confess "exec failed: [".$sth->errstr."]\nin query [$sql]";
return $sth;
}
# connect to oracle
($DBC = DBI->connect("dbi:Oracle:$ORASID",$ORAUSR,$ORAPWD,
{
PrintError=>0,
AutoCommit=>0,
}
)) ||
confess "failure connecting to $ORASID: ".$DBI::errstr;
# create one temporary table with one numeric column filled with test data
eval { sql_execute("DROP TABLE test_oracle_bug"); };
sql_execute("CREATE TABLE test_oracle_bug (DATA NUMBER)");
sql_execute("INSERT INTO test_oracle_bug (DATA) VALUES (1.73696)");
$DBC->commit;
# fetch numeric from table
my $ret = sql_execute("SELECT DATA FROM
test_oracle_bug")->fetchrow_arrayref;
my ($val) = @$ret;
my $sum = 1.73696 - $val;
is($sum,0,"does sum $sum == 0?");
-----------------<snip>---------------------------
I don't want to get involved in the mechanics of your specific problem
but as an aside if wanted to do what your example does I'd let the
database work it out:
select data - ? from test_oracle_bug
$sth->execute("1.73696")
then all the maths is done at the database end.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
<snipped your analysis>
Martin
