DB Connectivity issue

schluckie has asked for the wisdom of the Perl Monks concerning the following question:

Please enlighten me! Why does the first DBI->Connect work and does the second DBI->Connect throw an ORA-12154?

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

print "DB connectivity test\n";

my $user='system';
my $password='manager';

my $DSN='dbi:Oracle://127.0.0.1:1521/db.company.local';
my $dbh = DBI->connect($DSN, $user, $password,
                      { RaiseError => 1, AutoCommit => 0 });

print "This works\n";                 
my $DSN='dbi:Oracle:host=127.0.0.1:sid=db.company.local:port=1521';
my $dbh = DBI->connect($DSN, $user, $password,
                      { RaiseError => 1, AutoCommit => 0 });
print "This doesn't\n";
[download]

Running the code yields the following result:

DB connectivity test
This works
DBI connect('host=127.0.0.1:sid=ps.kadaster.local:port=1521','system',
+...) failed: ORA-12154: TNS:could not resolve the connect identifier 
+specified (DBD ERROR:
 OCIServerAttach) at conn_test.pl line 16.
[download]

Comment on DB Connectivity issue Select or Download Code

Replies are listed 'Best First'.
Re: DB Connectivity issue by Tux (Canon) on Jun 01, 2016 at 05:46 UTC
Because you did not read the documentation for DSN well enough. The DSN is a three part string separated by colons (`:`), where the third part is options separarted by semicolons (`;`): `dbi``:``Oracle``:``host=127.0.0.1``;``sid=db``;``port=1521` Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]
Re: DB Connectivity issue by stevieb (Canon) on May 31, 2016 at 16:23 UTC
I'm wondering if this is because you're using the same DB handle to open the second connection before closing the previous one. With `use warnings;`, you would have received a warning similar to: `"my" variable $dbh masks earlier declaration in same scope...` [download] Because you've declared it twice with `my`. Try either disconnecting then reusing the same `$dbh`: `my $dbh = ...; print "This works\n"; $dbh->disconnect; ... $dbh = DBI->connect ... # no my declaration` [download] ...or just using a different handle name: `my $dbh_1 = ... ... my $dbh_2 = ...` [download]	[reply] [d/l] [select]
Re^2: DB Connectivity issue by schluckie (Initiate) on Jun 01, 2016 at 05:10 UTC
Included the "use warnings" and used the two different database handels as suggested, but that does not change the output. The second DSN string keeps throwing the Oracle error.	[reply]
Re: DB Connectivity issue by graff (Chancellor) on Jun 01, 2016 at 01:55 UTC
I'm not familiar enough with the general syntax specs for connection strings, let alone the specific syntax variants supported by Oracle and/or DBD::Oracle, to say why the first $DSN value works and the second does not. (With reference to stevieb' reply above, I assume that: `my $DSN='dbi:Oracle:host=127.0.0.1:sid=db.company.local:port=1521';` [download] yields a failure even when you try it first.) In any case, given that you have a form of $DSN that works, is there a compelling reason for finding a different form that also works? (I wouldn't be inclined to regard the failure of the above syntax to be "unusual" or "surprising" - probably because I don't have enough relevant experience to expect it to work. But having found a method that works, I'd just use that and stop worrying about it.)	[reply] [d/l]