losing precision while selecting NUMBER values from Oracle 7.3

From: Ravi Kongara (Ravi.Kongara_at_Sun.COM)
Date: 09/21/04 

Date: Tue, 21 Sep 2004 12:30:17 -0700
To: DBI-Users <dbi-users@perl.org>

Hi,

I'm using DBI 1.42, DBD::Oracle1.14 built on Oracle 8, Perl 5.6.1 on
Solaris 9.
Purpose of the script is to compare values between source and target
databases.
Source db is Oracle 7.3 and target db is Oracle 8.1.7.
Again and again i'm getting into the problem of losing precision/scale
of NUMBER values.
DBI script selects/displays 39 digits after the decimal point,
eventhough database has stored 40 digits, cutting off
last digit while selecting from source. Strangely, it displays all 40
digits from target, which is a Oracle 8 database.

When i select from SQLPLUS session, values are shown correctly. Both
source and target show 40 digits and they match.
Is there any clue what's happening..? why the last digit is lost when i
select using DBI from Oracle 7.

If, i use TO_CHAR function i get 39 digits from both source and target.

Code snippet
--------------------------
<. . . . . >
use DBI;
use Getopt::Long;

my $src_test_value = $src_dbh->selectrow_array("select
 CONVERSION_RATE from $src_schema_name.$src_table_name where
FROM_CURRENCY_CODE = 'ZAR' and conversion_date = (select
max(conversion_date) from $src_schema_name.$src_table_name where
FROM_CURRENCY_CODE = 'ZAR')");
 print "src conv rate = $src_test_value\n";

 my $tgt_test_value = $tgt_dbh->selectrow_array("select
 CONVERSION_RATE from $ods_schema_name.$ods_table_name where
FROM_CURRENCY_CODE = 'ZAR' and conversion_date = (select
max(conversion_date) from $ods_schema_name.$ods_table_name where
FROM_CURRENCY_CODE = 'ZAR')");
 print "ods conv rate = $tgt_test_value\n";
<. . . . . . >
--------------------------
values printed are...

Comparing src -> tgt
src conv rate = .159616919393455706304868316041500399042
ods conv rate = .1596169193934557063048683160415003990423
------------------------------------------------------

Thanks,
Ravi

Relevant Pages

  • Single application - multiple target databases - which DB driver?
    ... I'd be interested in peoples thoughts on which database driverto ... use for an application where the target db varies.... ... Currently our main application is Oracle based using Direct Oracle ... particularly users of SQLDirect ...
    (borland.public.delphi.thirdpartytools.general)
  • Re: losing precision while selecting NUMBER values from Oracle 7.3
    ... > DBI script selects/displays 39 digits after the decimal point, ... > digits from target, which is a Oracle 8 database. ...
    (perl.dbi.users)
  • CProviderTypes - Doubt
    ... I need to create some intermediate temp tables in the target ... database as same as the source. ... Server's but again it should be compatible for oracle. ... getting the right data type bcos OLEDB gives data type number to a group ...
    (microsoft.public.data.oledb)
  • Re: Trade Press Needs to Talk with HP Disaster Tolerence Customers
    ... This isn't so much targeted at you Tom but everyone in cov... ... Keith is right on target. ... > Oracle from improving their professional marketing of these advanced ... > I have repeatedly discussed the scientific basis of the design advantages ...
    (comp.os.vms)
  • Re: rman connection authorization
    ... Copyright 1982, 2005, Oracle. ... C:\TEMP>rman target / nocatalog ... initialization of internal recovery manager package failed ... RMAN-04005: error from target database: ...
    (comp.databases.oracle.server)