E R wrote:
Alright, so here's another mystery:
use DBI;
...
$ENV{NLS_LANG} = ""AMERICAN_AMERICA.WE8ISO8859P1";
$dbh = DBI->connect(...);
$c = chr(228);
print "result: ", select_scalar("SELECT 1 FROM DUAL WHERE '$c' =
chr(228)"), "\n";
The select_scalar subroutine merely returns the first column of the
first row of the query.
The above code emits:
result:
However, if I change NLS_LANG to "AMERICAN_AMERICA.US7ASCII", it emits:
result: 1
Is there another NLS_LANG setting I should use, or should I check my
versions of OCI, DBD::Oracle and perl? I think I'm running OCI version
8. My perl is 5.8.0.
Thanks,
ER
Are you actually running the code you keep sending? In:
sub select_scalar {
my ($dbi, $sql) = @_;
my $sth = $dbi->prepare($sql);
$sth->execute();
my $r = $sth->fetch_row_array();
return $r->[0];
}
there is no such method fetch_row_array - it should be fetchrow_array.
In:
$ENV{NLS_LANG} = ""AMERICAN_AMERICA.WE8ISO8859P1"
there is an extra ".
In:
select_scalar("SELECT 1.....
you have missed off the $dbi argument select_scalar requires.
It is also a good idea to put RaiseError=>1 in you DBI connect for
examples like this so any errors are trapped and use strict, use
warnings never goes amiss.
So fixing all of that we have:
use strict;
use warnings;
use DBI;
$ENV{NLS_LANG} = "AMERICAN_AMERICA.WE8ISO8859P1";
my $dbi = DBI->connect('dbi:Oracle:xxx','xxx','xxx',{RaiseError=>1})
|| die $DBI::errstr;
my $c = chr(228);
print "result: ", select_scalar($dbi, "SELECT 1 FROM DUAL WHERE '$c' =
chr(228)"), "\n";
sub select_scalar {
my ($dbi, $sql) = @_;
my $sth = $dbi->prepare($sql);
$sth->execute();
my $r = $sth->fetchrow_array();
return $r->[0];
}
I think you may be on rather too old an Oracle (at 8) for this. What do
you get when you add:
my $sth = $dbi->prepare(q{select chr(228) from dual});
$sth->execute;
DBI::dump_results($sth);
just after the connect call and what does:
my $nls_params = $dbi->ora_nls_parameters();
foreach my $k (keys %$nls_params) {
print "$k, $nls_params->{$k}\n";
}
output.
Rather interestingly I get "DBD::Oracle::st fetch failed: ORA-29275:
partial multibyte character (DBD ERROR: OCIStmtFetch) [for Statement
"select chr(228) from dual"]" for the select so there might be something
there but then again my database character set is AL32UTF8.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
