DB Connectivity issue

[schluckie]

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";

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.

[Tux]

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

[stevieb]

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...

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

...or just using a different handle name:

my $dbh_1 = ...
...
my $dbh_2 = ...

[schluckie]

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.

[graff]

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';

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.)