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

[dmmiller2k]

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";
}

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;
  ...
}

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