How to format numbers pulled back from an Oracle DB using SQL.

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

I have a problem where I need to display numbers with 2 decimal places (including zero values). The following command works ok on a command line in sqlplus but not inside my perl script.

"column monthly_cost format 9999999999999.99"
[download]

If anyone knows how to insert formatting into the bit below please let me know.
-----
Excerpt from script. monthly_cost is the field to be formatted on it's way out of the DB.

# Pull the DB record for the service_number passed 
my $dbh = DBI->connect( "dbi:Oracle:$dbase", $user, $pass,
  { AutoCommit => 0, RaiseError => 1, PrintError => 0 })
        or die $DBI::errstr;
    my $sth = $dbh->prepare(
        # sqlcode
        "select 
        siteid, monthly_cost
        from comms.services where service_number = ?"
        );
    $sth->execute($svce);
[download]

Thanks
Scott

Comment on How to format numbers pulled back from an Oracle DB using SQL. Select or Download Code

Replies are listed 'Best First'.
Re: How to format numbers pulled back from an Oracle DB using SQL. by dmmiller2k (Chaplain) on Mar 18, 2002 at 04:57 UTC
My Oracle is a little rusty, but can you not format that particular column in your result set using Perl? For example, adding to your code, # Pull the DB record for the service_number passed my $dbh = DBI->connect( "dbi:Oracle:$dbase", $user, $pass, { AutoCommit => 0, RaiseError => 1, PrintError => 0 }) or die $DBI::errstr; my $sth = $dbh->prepare( "select siteid, monthly_cost " "from comms.services " "where service_number = ?"); $sth->execute($svce); while ( my ( $siteid, $monthly_cost ) = $sth->fetchrow_array ) { my $s = sprintf( 'Service Number (%s): site ID = %s, monthly cost = +%16.2f', $svce, $siteid, $monthly_cost ); print "$s\n"; } [download] Update: There's no particular reason not to simply replace the value returned from the fetch method with the formatted value, after which you may do whatever you want with the new formatted value: `while ( my ( $siteid, $monthly_cost ) = $sth->fetchrow_array ) { $monthly_cost = sprintf '%16.2f', $monthly_cost; ... }` [download] In Sybase (which I've been using more recently), it's possible to convert the number to a string (VARCHAR) with precisely the format you need right in the SELECT statement, but while I know Oracle can do the same, I cannot remember exactly how. dmm	[reply] [d/l] [select]
Re: How to format numbers pulled back from an Oracle DB using SQL. by rdfield (Priest) on Mar 18, 2002 at 10:36 UTC
In Oracle SQL: `select siteid, round(monthly_cost,2) from comms.services where service_number = ?"` [download] rdfield	[reply] [d/l]