0

I am trying to connect to Oracle using Perl.

I am trying to connect from a Windows XP machine that has Perl installed. I also have downloaded Oracle SQL Developer and Oracle Instant Client. I can connect to the Oracle DB using Oracle SQL Developer using the TNS connection type.

I use the following Perl.

use DBI;
$db=DBI->connect( "dbi:Oracle", "username", "password" ) or die "Can't connect $DBI::errstr\n";

I get the following error message.

DBI connect('','username',...) failed: ERROR OCINlsEnvironmentVariableGet(OCI_NLS_CHARSET_ID) Check NLS settings etc. at oracle2.pl line3
Cant connect to database ERROR OCINlsEnvironmentVariableGet(OCI_NLS_CHARSET_ID) Check NLS settings etc.

Do I need to do anything with Oracle Instant Client because it does not have an installer. Is there something else that needs configured in Perl?

Thank you for any help provided.

* EDIT *

Are there any variables I need to set at the beginning of the Perl to link to either SQL Developer or Instant Client?

Gilbert
  • 63
  • 1
  • 2
  • 10

4 Answers4

1

I used a different method to connect Perl to an Oracle DB. I used SQLPlus instead of DBI. This is a command line utility from Oracle that can be called from Perl. Below is a sample of my code. The test.sql file can contain one or multiple queries and must end with exit.

my $connect_string = 'username/password@server'; # connection to the DB
my $file = 'test.sql'; # location of SQL file. The file must end with "exit"

my $sqlcmd = "sqlplus -S $connect_string \@$file"; # sqlcommand
system $sqlcmd; # executes command
Gilbert
  • 63
  • 1
  • 2
  • 10
0

I had the same problem in and at the end it could be solved.

In my case it turned out (after a lot of investigation and reading) that the root of the problem was with mixing of versions. The server was 11.2 meanwhile I used the 12.1 Instant Client package to build Oracle.dll for DBD::Oracle. So I downloaded the 11.2 version (from Oracle) and this error message just disappeared!

I have read the Trooble Shooting manual, but it seems 3 packages are needed: Basic, SDK and sqlplus (the later is used by the build process to determine the server version).

Before build some bash variables had to be set:

export ORACLE_HOME=/cygdrive/c/install/instantclient_11_2
PATH+=:"$ORACLE_HOME"
export TNS_ADMIN="$HOME"

The later needed to find oci.dll which is referenced by Oracle.dll. This is added to PATH as Windows looks for PATH to find DLLs not LD_LIBRARY_PATH. File tnsnames.ora can be in $TNS_ADMINdir, or in /var/opt/oracle or in /etc (or some other places). The defined service names can be listed by DBI->data_sources('Oracle').

And voilà! I hope this could help!

TrueY
  • 7,360
  • 1
  • 41
  • 46
0

With that error message and your piece of code, I'd first check whether it helps to explicitely state the Servername in the first parameter of $db=DBI->connect(.. (if need be, google for some examples).

If that doesn't help, I would then check the value of the environmental variable OCI_NLS_CHARSET_ID.

René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
0

I wanted to connect to Oracle XE running in a Docker container (with ports 1521 and 5500 mapped to their host equivalents) and query the Oracle sample database. Here's what worked for me.

#!/usr/bin/perl

use DBI;

# connect to Oracle...
$dbh = DBI->connect("dbi:Oracle:localhost/xepdb1","ot","Orcl1234");

# prepare and execute the SQL statement
$sth = $dbh->prepare("SELECT first_name, last_name FROM employees");
$sth->execute;

# retrieve the results
printf "%-30s %-30s\n", "First Name", "Last Name";
while(  my $ref = $sth->fetchrow_hashref() ) {
    printf "%-30s %-30s\n", $ref->{'FIRST_NAME'}, $ref->{'LAST_NAME'};
}
exit;
Clarius
  • 1,183
  • 10
  • 10