http://www.perlmonks.org?node_id=166540

vroom has asked for the wisdom of the Perl Monks concerning the following question:

When I do a straight up select from a table in Oracle from SQL-Plus I get my number returned in scientific notation.
select large_number from mytable; LARGE_NUMBER ------------ 4.1142E+11
when what I want is something like: 411420431014 Easy enough to fix in theory with TO_CHAR:
select to_char(large_number) from mytable; LARGE_NUMBER ------------ 411420431014
Or somewhat more explicitly with a number format:
select to_char(large_number,'999999999999') from mytable; LARGE_NUMBER ------------ 411420431014
These both return what I want. However when I run the queries with Perl and DBI I get my number back in scientific notation?

Anyone got a clue on this?

Replies are listed 'Best First'.
Re: Weird problem with Oracle, DBI, and Scientific Notation
by Zaxo (Archbishop) on May 15, 2002 at 00:06 UTC

    411420431014 should be ok on a perl with 64 bit integers, but it's too big for 32 bits. Perl switches to float representation as needed. Math::BigInt gives a portable way to keep long digit strings as integers.

    After Compline,
    Zaxo

Re: Weird problem with Oracle, DBI, and Scientific Notation
by DigitalKitty (Parson) on May 15, 2002 at 00:02 UTC
    Hi.

    I received an answer to 'my' DevShed posting ( about this question ) and this was the response.

    From DevShed.com ( Perl forum ): Exactly what behavior do you want to change? Do you mean that after you retrieve the variable from the database and print it, it is still in scientific notation but you don't want it to be? If you are just retrieving it and printing it then the value that you get from the database is never converted to "number" context, its printed in string context. Example:
    my $value = "4.1142E+11"; print "$value\n"; $value *= 1; print "$value\n"; If you run this it prints: 4.1142E+11 411420000000
    After multiplication by 1 (or any arithmatic operation) $value is changed from string context to integer context. One way you could explictily do this by using the int() function
    my $value = "4.1142E+11"; print int($value),"\n"; # or # $value = int($value); # print "$value\n";
    which now prints 411420000000 There is a limitation, though. This won't work if the number has more than 15 digits. You can force the output using printf and converting to a float but the number becomes unpredicatble in the lower digits. If your numbers are that large then you should use the Math::BigInt module.
    End of Perl forum
    Hope this helps.
    If not, I can ask my friend Mark about it.
    Thanks,
    -DK
Re: Weird problem with Oracle, DBI, and Scientific Notation
by theorbtwo (Prior) on May 14, 2002 at 23:00 UTC

    My suspicition is that DBI (or perhaps DBD) is seeing that you're getting a number back, and filling in the numeric part of the scalar structure but not the string part. sprintf is your friend.

    Update: Relized the problem with that: lost resolution. You're going to have to dig a bit, but I think I'm on the right track.

    (I should probably admit at this point that I have no DB experince.)


    We are using here a powerful strategy of synthesis: wishful thinking. -- The Wizard Book

Re: Weird problem with Oracle, DBI, and Scientific Notation
by rbc (Curate) on May 14, 2002 at 23:08 UTC
    I don't know if you would want to do this but ....
    you can alway do an end run on DBI with Oracle
    by doing something like this ...
    my $cmd="sqlplus scott/tiger\@whatever"; open SQLPLUS, "|$cmd" or die "Ah poo!:$!\n"; print SQLPLUS<<DOIT; select to_char(large_number,'999999999999') from mytable; DOIT close SQLPLUS;
    ... might want to save this as a last resort.