PERL: DBI connect('','system',...) failed: ERROR OCIEnvNlsCreate while using dbi personal package

Question

I'm trying to create an application class with uses DBD::Oracle. I have an issue with ORACLE_HOME and ORACLE_SID environment variables.

When I set those variables with in a BEGIN enclosure, it works fine, see example:

#!/usr/bin/perl
use strict;
use warnings;
use Switch;
use DBI;

# Debug
use Data::Dumper;

package DBIAgent;

BEGIN
{
    $ENV{ORACLE_HOME}="/app/oracle/product/11.2.0/db_1";
    $ENV{ORACLE_SID}="DB01";
}

sub new {
    my $class = shift;
    my $self = {
        _username => shift,
        _password => shift,
        _database => shift,
        _logger => shift
    };

    $self->{_oracleConnected} = 0;

    bless $self, $class;
    return $self;    
}

sub TO_JSON {
  return { %{ shift() } };
}

sub connect {
    my ( $self ) = @_;
    foreach (sort keys %ENV) { 
        $self->{_logger}->log ("INFORMATION - $_  =  $ENV{$_}");
    }

    $self->{_logger}->log ("INFORMATION - Username " . $self->{_username});
    $self->{_logger}->log ("INFORMATION - Password " . $self->{_password});

    eval {
        $self->{_oracleConnexion} = DBI->connect("dbi:Oracle:", $self->{_username}, $self->{_password}, {ora_verbose=>6}) or die ("ERROR - Unable to connect to database " . $self->{_database} . " - " . $! . " SQL ERROR: " . $DBI::errstr);
    };

    if ($@) {
        $self->{_oracleConnected} = 0;
        $self->{_logger}->log ($@);
    } else {
        $self->{_oracleConnected} = 1;
        $self->{_logger}->log ("INFORMATION - Connected to database " . $self->{_database});
    }

    return $self->{_oracleConnected};
}

sub getSqlResult {
    my ( $self, $sqlName, $sqlText ) = @_;
    my $resultSet;

    eval {
        $resultSet = $self->{_oracleConnexion}->selectall_arrayref($sqlText) or die ("ERROR - Execution for sql " . $sqlName . " on database " . $self->{_database} . " failed - SQL ERROR: " . $DBI::errstr);
    };

    if ($@) {
        $self->{_logger}->log ($@);
        return "";
    } else {
        return $resultSet;
    }
}

sub close {
    my ( $self ) = @_;

    $self->{_oracleConnexion}->disconnect();
    $self->{_oracleConnected} = 0;
    $self->{_logger}->log ("INFORMATION - Disconnected from database " . $self->{_connexionInfo}->{database});
}

# Getters / Setters

sub getConnected {
    my( $self ) = @_;
    return $self->{_oracleConnected};
}

1;

When I remove the BEGIN section, I get the following error: DBI connect('','system',...) failed: ERROR OCIEnvNlsCreate

My Oracle architecture may be with more than one ORACLE_HOME, so that's why I would like to make it as a variable parameter.

I tried to set it before creating the DBIAgent object but I get the same error. I own that I have to set those variables before the use DBI; command is called.

Is there a way to parameter the BEGIN section, or is there another way to do that ?

Thanks for your help. Ragards

Answer 1

You're using 2 environment variables here with two very different purposes.

---

If you want to connect to different Oracle Instances, dump/override the ORACLE_SID environment variable and use the connection string in DBD::Oracle->connect to pass the SID. One line from the docs shows this example:

$dbh = DBI->connect('dbi:Oracle:host=foobar;sid=DB;port=1521', 'scott/tiger', '');

More details @ https://metacpan.org/pod/DBD::Oracle#connect

---

ORACLE_HOME on the other hand points to the location of the Oracle Client software installed on your machine and tells the XS components of DBD::Oracle where to find the necessary libraries with which to link. I can't really think of a good reason you'd want to make this variable in a production environment. The fact that a fresh installation of DBD::Oracle requires the compilation of those XS components against those same client libraries could easily complicate your attempts. Odds are you'll want to use the most recent version on the machine. ORACLE_HOME is almost always set by login script, /etc/init.d script or by specific cron jobs.

One exception might be in a testing environment, but in that case you'll probably be well equipped to vary an environment variable to test each candidate client.

Answer 2

The %ENV hash is a way to access your shell's environment variables. So you can fix the problem by setting the environment variables outside of your program, before you call it.

$ export ORACLE_HOME=/app/oracle/product/11.2.0/db_1
$ export ORACLE_SID=DB01
$ ./name_of_your_program

Don't forget to remove the whole BEGIN block as well.