Re: DBI connects to one oracle instance but cannot connect to others

[John Scoles]

try this

use DBI;
$odsn="DBI:Oracle:";
$ouid="xxxx";
$opwd="xxxx";
$odbh= DBI->connect ($odsn,$ouid."@tmp1",$opwd);

Though this most likely has little to do with DBI or DBD:Oracle and more how
you are using your TNSNAME file

----- Original Message ----- 
From: <[EMAIL PROTECTED]>
To: <dbi-users@perl.org>
Sent: Wednesday, June 13, 2007 11:15 AM
Subject: DBI connects to one oracle instance but cannot connect to others


We run Oracle 10g under Linux.  There are seven database instances:
prod, tmp1,...,tmp6.  The TNSNAMES.ORA file treats all seven instances
equally:
 (DESCRIPTION=
   (ADDRESS_LIST=
     (ADDRESS=
       (COMMUNITY = tcp.world)
        (PROTOCOL=tcp)
        (HOST=localhost)
        (PORT=1521)
     )
   )
   (CONNECT_DATA =
       (SERVER = DEDICATED)
     (SID = prod)
   )
)

.... identical segments for the tmp1 - tmp6 instances

I can sqlplus into any instance.  I can run a local perl script to
connect to the production instance "prod".  I can also run perl
scripts from a remote server to connect remotely to any of these
instances.  But my perl script on localhost cannot connect to any of
the tmp instances tmp1...tmp6.

This script works:

use DBI;

$ENV{"ORACLE_HOME"} = "/u01/app/oracle/OraHome_1";
$ENV{"ORACLE_SID"} = "prod";

$odsn="DBI:Oracle:prod";
$ouid="xxxx";
$opwd="xxxx";

$odbh= DBI->connect ($odsn,$ouid,$opwd);

---  works perfectly BUT the identical for the tmp instances DOES NOT
WORK:

use DBI;

$ENV{"ORACLE_HOME"} = "/u01/app/oracle/OraHome_1";
$ENV{"ORACLE_SID"} = "tmp1";

$odsn="DBI:Oracle:tmp1";
$ouid="xxxx";
$opwd="xxxx";

$odbh= DBI->connect ($odsn,$ouid,$opwd);

I get the following error message:  TNS:could not resolve the connect
identifier specified

What am I doing wrong????

Thank you for any hint.
Anke