Error ORA-12154 on DBI->connect to Oracle database with Oracle Instant Client in Solaris 10

Question

I've been pulling my hair out over this problem for two days now:

I'm trying to get a perl script to interface with an Oracle database. I have a new server I'd like to deploy my application on. This script previously worked.

Here's what I've done so far:

Placed my tnsnames.ora file in instantclient/network/admin:

ls -la network/admin/
total 8
drwxrwxrwx   2 m staff        512 Apr 19 09:54 .
drwxrwxrwx   3 m staff        512 Mar 28 15:56 ..
-rwxrwxrwx   1 m staff        777 Apr 19 09:54 tnsnames.ora

My Perl script looks like this:

  12 use CGI;
   13 use DBI;
   14 use Data::Dumper;
   15 use strict;
   16 
   28 $ENV{ORACLE_HOME} = "/xxx/instantclient/";
   29 
   32 $ENV{'LD_LIBRARY_PATH'} = "xxx/instantclient/lib";
   33 
   35 use DBD::Oracle;
   36 
   37 print "DBI::VERSION: $DBI::VERSION\n";
   38 print "$DBD::Oracle::VERSION\n";
   66 my $dbh = DBI->connect("dbi:Oracle:host=computer;port=1521;sid=mydatabase", "user", "pass");
   67 my $sth = $dbh->prepare("SELECT sysdate FROM dual");
   68 my $rv = $sth->execute; 
   69 DBI::dump_results($sth) if $rv;
   70 $dbh->disconnect;
   71 
   72 print "$database $dbUser $dbPassword \n";
   73 
   74 my $dbh = DBI->connect( $database, $dbUser, $dbPassword ) or die("PROBLEM WITH LINE:\n$! , stopped");

This script produces this output:

DBI::VERSION: 1.609
DBD::Oracle version: 1.24
'19-APR-13'
1 rows
dbi:Oracle:mydatabase user pass 
DBI connect('mydatabase','user',...) failed: ORA-12154: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at ./code.pl line 74

My tnsnames.ora file contains the following entry:

mydatabase =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = computer )(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = service.computer.com)
    )
  )

This tnsnames.ora file has been copied from a working machine, so I'm confident it works.

The strange thing is, I can connect to the code without using the tnsnames.ora file, but when I try to use it, it breaks.

Any suggestions?

Answer 1

Not sure if it help, but the following worked for me:

TO connect locally using tnsnames.ora (inside $ORACLE_HOME/network/admin directory):

my $db = DBI->connect( "dbi:Oracle:mydatabase", "scott", "tiger" );

and to connect not using the tnsnames.ora:

my $db = DBI->connect("dbi:Oracle:host=$host;sid=$sid;port=1521",$user,$passwd)
    || die( $DBI::errstr . "\n" );

where the $host, $sid have to be defined beforehand.