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

In reply to Re: How to format numbers pulled back from an Oracle DB using SQL. by dmmiller2k
in thread How to format numbers pulled back from an Oracle DB using SQL. by sanders

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.