Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

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

by dmmiller2k (Chaplain)
on Mar 18, 2002 at 04:57 UTC ( #152405=note: print w/replies, xml ) Need Help??

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

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 " "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.


Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://152405]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (4)
As of 2023-09-24 03:43 GMT
Find Nodes?
    Voting Booth?

    No recent polls found