On Tue, Sep 21, 2004 at 12:30:17PM -0700, Ravi Kongara wrote:
> 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.
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.
> If, i use TO_CHAR function i get 39 digits from both source and target.
That's a key point. Oracle itself is happy to truncate the number.
I'm not sure why SQLPLUS behaves differently. It probably asks
Oracle to fetch the NUMBR as a raw number and then uses OCI library
functions to format it. That different code path may be the cause
of the different behaviour.
Tim.
> 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
>
