Why does Perl's DBI complain about "failed: ERROR OCIEnvNlsCreate" when I try to connect to Oracle 11g?

Question

I am getting the following error connecting to an Oracle 11g database using a simple Perl script:

 failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var  or PATH (Windows) and or NLS settings, permissions, etc. at

The script is as follows:

#!/usr/local/bin/perl

use strict;
use DBI;

if ($#ARGV < 3) {
print "Usage: perl testDbAccess.pl dataBaseUser dataBasePassword SID dataBasePort\n";
exit 0;
}
my ($user, $pwd, $sid, $port) = @ARGV;

my $host = `hostname`;
my $dbh;
my $sth;
my $dbname = "dbi:Oracle:HOST=$host;SID=$sid;PORT=$port";

openDbConnection();
closeDbConnection();

sub openDbConnection() {
        $dbh = DBI->connect ($dbname, $user ,$pwd , { RaiseError => 1}) || die "Database connection not made: $DBI::errstr";
}

sub closeDbConnection() {
        #$sth->finish();
        $dbh->disconnect();
}

Anyone seen this problem before?

Juxon

John, when you do get this fixed, please remember to come back here and "accept" your favourite answer. If you have to modify something for your environment, you can even post that solution and accept it, and then "up-vote" the answers that helped you get there. This will properly retire the question for the SO archives. – Scolopendrid

Answer 1

Check your Oracle client configuration (including, as the message says, ORACLE_HOME), check file permissions, etc. It's unlikely that DBI per se has anything to do with the problem, and I know for a fact that DBD::Oracle is compatible with the 11g libraries (at least the 11g InstantClient).

Karmen

Exhaustive troubleshooting information - start with the files README and README.help.txt. – Pomology

If I set ORACLE_HOME to the 10g instance, it's able to connect to the 11g database, however if I set ORACLE_HOME to the 11g instance I get the error... – Juxon

OK so ORACLE_HOME is changed, if there are references to the 10g binaries in your PATH are you resetting those also to their 11g counterparts as well? – Abnormal

Don't just change ORACLE_HOME to point to a different oracle client library WITHOUT rebuilding DBD::Oracle - it most likely won't work properly. When you build DBD::Oracle is examines the header files and client to see what version you have and builds a DBD::Oracle appropriate for your client libraries. There are a load more reasons too many to put in a comment. – Misdeem

Answer 2

• Install the perl module DBD::Oracle
• Add use DBD::Oracle; into your perl script.

This made the problem go away for me.

Aglaia

Answer 3

Afther upgrade Oracle form 10.2.0.4 to 10.2.0.5 my Perl-CGI not working, the error.log file of Apache was the error:

DBI connect('DB','user',...) failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or PATH (Windows) and or NLS settings, permissions, etc. at ... OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or PATH (Windows) and or NLS settings, permissions, etc.! at ...

adding the following directive solve the problem:

my $ORACLE_HOME = "/usw/app/oracle/product/10.2";
$ENV{ORACLE_HOME}=$ORACLE_HOME;

Antirachitic

Answer 4

Sorry for bringing this thread alive again, but I've been trying to solve this issue for weeks. Nowhere did I find that what finally solved it for me.

Environment: RedHat 6.5, Oracle Instant Client 12.1, Apache 2.2

What solved it for me: Change the permissions of the directory where Oracle Instant Client is installed., which must also be the value of LD_LIBRARY_PATH. The directory wasn't accessible to "other", just owner and group. This command made the whole difference:

# chmod o+rx INSTANTCLIENT-DIRECTORY

I had been trying to create every possible combination of ORACLE_HOME and LD_LIBRARY_PATH. (Even "unsetting" ORACLE_HOME; I found suggestions that would solve the issue, since Instant Client needed only LD_LIBRARY_PATH and !NOT! ORACLE_HOME.) Nothing helped, until ... it hit me to look further in the error message:

    failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var
    or PATH (Windows) and or NLS settings, permissions, etc.

permissions being the key word!

Hope this helps someone else!

Noxious

Please add a little more explanation to make the solution clear. Some of our citizens aren't fluent in English, such that leaving the answer implied may not lead them to a solution. – Scolopendrid

Thanks! Added some more text to the actual solutions. I was so eager to share this when I found the problem. So I probably wasn't too educational =) Feel free to change it further if you think that is missing information. – Noxious

For me the solution was part of what you wrote here: I just needed to set LD_LIBRARY_PATH to the path to the instant client directory. – Creatinine

Answer 5

2021 Update for CentOS 8 etc:-

If you use systemctl, nothing in your environment or Apache setup can pass environment vars to DBI!

you need to edit the /usr/lib/systemd/system/httpd.service file and add appropriate lines like this after the [Service] section:

[Service]
Environment=ORACLE_HOME=/opt/oracle/product/19c/dbhome_1

Then re-start apache as usual:-

/bin/systemctl restart httpd.service

(Vote this up if it helped you: this web page is the #1 google result for this old problem which systemctl "security" has rebirthed)

Fauna

Answer 6

Check the #!/usr/bin/perl (first line of your script) is the right perl version you wish to use too !

Pyrognostics

Answer 7

Previous answers don't solve this problem, but they did point me in the right direction: The launched environment was missing the DYLD_LIBRARY_PATH for Oracle instantclient. You can check by calling: launchctl export

or

launchctl getenv variable_name in OS X 10.10

I compared the output from my .bash_profile and found that the path to instanclient wasn't in launchctl PATH and DYLD_LIBRARY_PATH - Once I added them with the following it worked:

launchctl setenv PATH /path/to/instantclient
launchctl setenv DYLD_LIBRARY_PATH /path/to/instantclient

Marston

Answer 8

The environment variable used to specify the Oracle libraries may be different from one system to another. On most systems, use LD_LIBRARY_PATH but it may also be LIBPATH (notably on AIX), DYLD_LIBRARY_PATH, or maybe others.

Sardonyx

Answer 9

I too went through the same issue. In my case ORACLE_HOME environment variable was incorrect.

Attalanta