I was intending to use ALTER SESSION SET NLS_TERRITORY='GERMANY' to
allow me to subsequently use the G and D formatting characters in a
TO_CHAR format string. For example
SELECT TO_CHAR (Invoice_Total, '999G999G999D9999') from ...
However, once I issue the ALTER SESSION, *all* numeric values come back
with the decimal point replaced by a comma (in this instance). Here's a
simple example script ...
#!/usr/bin/perl -w
use strict ;
use DBI ;
my $dbh = DBI -> connect ("dbi:Oracle:xxx", "usr/pwd", "", {'RaiseError'
=> 1, 'PrintError' => 0,}) ;
show_sum('A') ;
$dbh->do(q(alter session set nls_territory = 'GERMANY')) ;
show_sum('B') ;
show_sum('A') ;
$dbh->disconnect ;
sub show_sum {
my ($run) = @_ ;
my $sth = $dbh->prepare(q(select decode (:x, 'A', 1234.5678,
9876.5432) from dual)) ;
$sth->bind_param(':x', $run) ;
$sth->execute ;
my $row = $sth->fetchrow_arrayref ;
print ">> Amount = $row->[0] <<\n" ;
$sth->finish ;
}
Running this script gives the following ...
bash-2.04$ perl a2.pl
Connected
>> Amount = 1234.5678 <<
>> Amount = 9876,5432 <<
>> Amount = 1234,5678 <<
The problem is that even though the values returned by the database are
numeric (as shown by $sth->TYPE), the values accessible to the script
are not valid Perl numbers and cannot be manipulated as numbers.
I know I can supply NLS numeric characters as an argument to the TO_CHAR
function, but this seems somewhat clunky. Has anyone got any
suggestions/solutions ?
Thanks,
Steve
