Oracle DBD - can't set NLS variables

[fishiface]

I'm using Perl 5.8.4 on Debian 3.1 with DBI version 1.46 and DBD::Oracle 1.17, built on top of an Oracle 9.2.0.1.0 client.

I'm unable to change NLS settings via the environment or via an "ALTER SESSION" command. They are defaulting to American. The following script shows both techniques failing:

#!/usr/bin/perl

use strict;
use DBI;

my $pass = '828state';
my $user = 'isil';

my $ora = '/usr/oracle/product/9.2.0.1.0';
$ENV{ORACLE_SID}='ORA0';
$ENV{ORACLE_HOME}=$ora;
$ENV{ORACLE_BASE}=$ora;
$ENV{LD_LIBRARY_PATH}=$ora . '/lib';
$ENV{ORA_NLS33}=$ora . '/ocommon/nls/admin/data';
$ENV{NLS_LANG}='ENGLISH';
$ENV{NLS_LANGUAGE}='ENGLISH';
$ENV{NLS_TERRITORY}='UNITED KINGDOM';
$ENV{NLS_DATE_LANGUAGE}='ENGLISH';
$ENV{NLS_DATE_FORMAT}='MM/DD/YYYY';

my $dbh = DBI->connect( 'dbi:Oracle:host=oracle;sid=ORA0;port=1521',
        $user, $pass, { RaiseError => 1, AutoCommit => 0 }
    );

$dbh->do("ALTER SESSION SET NLS_LANGUAGE = 'ENGLISH'");

my $nls = $dbh->selectall_arrayref( q{
        SELECT * from NLS_DATABASE_PARAMETERS
    } );

foreach (@$nls) {
    my ($parm,$val) = @$_;
    print "$parm -> $val\n" if $parm eq 'NLS_LANGUAGE';
}

This produces the output

NLS_LANGUAGE -> AMERICAN
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().

I've got nowhere with google or searching these forums. Has anyone met this before or got a fix?

Thanks in advance,

fish

["]As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs.["]
--Maur

 

[PaulTEG]

Oracle - it's been quite a while, but the NLS settings apply to the database if memory serves, so changing them in the client could have the effects you're seeing.

Can your DBA confirm the NLS settings of the database you're trying to connect to?

This is officially 'straw clutching', you have been warned ;-)

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[fishiface]

Hi Paul, it's been a while!

Unfortunately, I'm the DBA, starting from cold on brand new machines. AFAICT, the server does it's own thing internally and each client is free to do what it wants as well, so that you can run true simultaneous multi-lingual apps.

The Oracle docs don't acknowledge DBI and the DBD docs simply say to put it in the environment. I guess it's one for the dbi-users mail list.

I hope that you are well,

fish

["]As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs.["]
--Maur

 

[PaulTEG]

indeed, as are you I hope, post back your findings, I'm interested now ;-)

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[PaulTEG]

Just had a thought, might be worth a punt in one of the Oracle forums here under Programmers > DBMS

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)