Re: ORA-12154: TNS:could not resolve the connect identifier specified

On Fri, Dec 14, 2007 at 10:47:16AM -0700, Loo, Peter # PHX wrote:
I am not familiar with the tnsnames.ora file. Here is what they are
set to. I did not find anything out of the ordinary.

If both ORACLE_HOME's have the same contents in the tnsnames.ora, then
you should be able to connect to both instances using either one.

To answer
your question about why I am changing the ORACLE_HOME, it is because
I spawn a "sqlldr" using system() within the program. If I don't
set the ORACLE_HOME to the relevant version of Oracle, I get the
SQL*Loader-951 and ORA-00942 errors when I spawn system($sqlldrCmd).

I am not all that familiar with sqlldr, but I think you can supply a
username/password/instance on the command line. So in theory it
should work too unless the loading format is different between
versions. If you do continue to switch ORALCE_HOME and PATH, I would
definitely recommend switching LD_LIBRARY_PATH too (so sqlldr picks up
the correct shared libraries when you spawn it).

INST1.domain.com =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname.domain.com)(PORT = 1521))
)
(SDU = 32767)
(CONNECT_DATA =
(SID = inst1)
)
(TDU = 32767)
)

INST2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname.domain.com)(PORT = 1521)
)
)
(CONNECT_DATA =
(SERVICE_NAME = inst2)
)
)
)

-----Original Message-----
From: David Dooling [mailto:banjo@xxxxxxxxxxxxxxxxxxxxx]
Sent: Wednesday, December 12, 2007 2:01 PM
To: dbi-users@xxxxxxxx
Subject: Re: ORA-12154: TNS:could not resolve the connect identifier specified

On Wed, Dec 12, 2007 at 01:28:37PM -0700, Loo, Peter # PHX wrote:
I tried setting both variables at the very beginning before any
connection is made and the result is still the same.

$ENV{ora_envhp} = 0;
$ENV{ORA_ENVHP} = 0;

Why are you switching the ORACLE_HOME at all? The 9i client should
be able to connect to the 10g database just fine (and vice versa).
All you need to do is ensure you have the appropriate entries for
the 9i and 10g instances in the network/admin/tnsnames.ora of
whatever client you are using.

Switching as you are is quite problematic since the Oracle shared
libraries are probably only loaded once, the first time DBD::Oracle
is loaded by Perl. Switching ORACLE_HOME and PATH (it seems you
would also have to change LD_LIBRARY_PATH) after loading one set of
shared libraries is going to lead to unpredictable behavior.

--
David Dooling
.