Re: DBD::Oracle and Perl's internal representation of strings

[E R]

On Nov 8, 2007 3:26 AM, Martin Evans <[EMAIL PROTECTED]> wrote:
>
> Are you actually running the code you keep sending?

Thanks for the help. You're right, I am hand copying the code. I have
attached the latest version of the script I am using which
demonstrates the behavior I'm seeing. Just add your own connection
parameters.

> 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);

Here's what I get:?

'ä'
1 rows
Can't locate object method "ora_nls_parameters" via package "DBI::db"
at ./t10 line 30.

Do I need to upgrade my OCI, or DBI or DBD::Oracle?

As for the attached test script, here's some output:

$ ./t8.new --home /product01/oracle/9iAS/9.0.4b/ -nls 0
ORACLE_HOME = /product01/oracle/9iAS/9.0.4b/
NLS_LANG    =
ORA_NLS     =
ORA_OCI()   = 8

test 0: 1
test 1: 1
test 2: 1
test 3: 1
test 4:

$ ./t8.new --home /product01/oracle/9iAS/9.0.4b/ -nls 1
ORACLE_HOME = /product01/oracle/9iAS/9.0.4b/
NLS_LANG    = AMERICAN_AMERICA.WE8ISO8859P1
ORA_NLS     =
ORA_OCI()   = 8

test 0:
test 1:
test 2:
test 3:
test 4:

$ ./t8.new --home /product01/oracle/9iAS/9.0.4b/ -nls 2
ORACLE_HOME = /product01/oracle/9iAS/9.0.4b/
NLS_LANG    = AMERICAN_AMERICA.US7ASCII
ORA_NLS     =
ORA_OCI()   = 8

test 0: 1
test 1: 1
test 2: 1
test 3: 1
test 4:

#!/product01/perl/5.8.0/bin/perl

use strict;
use DBI;
use Data::Dumper;

my $tns         = ...
my $db_username = ...
my $db_password = ...

my $opts = {
                AutoCommit => 1,
                RaiseError => 1,
                LongReadLen => 65535,
                LongTruncOk => 1
            };

my ($dbi, $dbh);

sub select_scalar {
  my ($dbi, $sql) = @_;
  my $sth = $dbi->prepare($sql);
  $sth->execute();
  my @r = $sth->fetchrow_array;
  return $r[0];
}

# my $dbi = DBI->connect($tns,$db_username,$db_password);
# my $dbh = new dbh($dbi);

my $c228 = chr(228);
my $u = $c228 . 'a';

my $x = 'a';
my $y = $x.chr(1024);
chop $y;

sub new_dbh {
  $dbi = DBI->connect($tns, $db_username, $db_password);
}

sub test {
  print "test 0: ", select_scalar($dbi, "SELECT 1 FROM DUAL WHERE '$c228' = 
chr(228)"), "\n";
  print "test 1: ", select_scalar($dbi, "SELECT 1 FROM DUAL WHERE '$u' = 
(chr(228) || 'a')"), "\n";
  print "test 2: ", select_scalar($dbi, "SELECT 1 FROM DUAL WHERE '${c228}a' = 
(chr(228) || 'a')"), "\n";
  print "test 3: ", select_scalar($dbi, "SELECT 1 FROM DUAL WHERE '${c228}${x}' 
= (chr(228) || 'a')"), "\n";
  print "test 4: ", select_scalar($dbi, "SELECT 1 FROM DUAL WHERE '${c228}${y}' 
= (chr(228) || 'a')"), "\n";
  print "\n";
}

my ($opt_nls, $opt_ora_nls, $opt_home);
use Getopt::Long;

my $result = GetOptions("nls:i" => \$opt_nls, "ora_nls:i" => \$opt_ora_nls, 
"home:s" => \$opt_home);

if ($opt_home) {
  $ENV{ORACLE_HOME} = $opt_home;
}

if    ($opt_nls == 0) { undef $ENV{NLS_LANG}; }
elsif ($opt_nls == 1) { $ENV{NLS_LANG} = "AMERICAN_AMERICA.WE8ISO8859P1"; }
elsif ($opt_nls == 2) { $ENV{NLS_LANG} = "AMERICAN_AMERICA.US7ASCII"; }
else { die "bad -nls option: $opt_nls\n" }

if    ($opt_ora_nls == 0) { undef $ENV{ORA_NLS} }
elsif ($opt_ora_nls == 1) { $ENV{ORA_NLS} = 
"$ENV{ORACLE_HOME}/ocommon/nls/admin/data" }
else { die "bad -ora_nls option: $opt_ora_nls\n" }

new_dbh();

print "ORACLE_HOME = ", $ENV{ORACLE_HOME}, "\n";
print "NLS_LANG    = ", $ENV{NLS_LANG}, "\n";
print "ORA_NLS     = ", $ENV{ORA_NLS}, "\n";
print "ORA_OCI()   = ", DBD::Oracle::ORA_OCI(), "\n";
print "\n";

test();