Loo, Peter # PHX wrote:
Hello All,
I am confused as to why I am getting this error message. I am trying to
connect to two different Oracle instances within one program run. The
two Oracle instances are different in versions. inst1 is 9.2 while
inst2 is 10.2. I am able to connect to the first instance without
problems, but the instance yields "ORA-12154" error. However, when I
reverse the connection order by connecting the inst2 with 10.2 version
first, then connecting to the inst1 with 9.2 version, there is no error.
I hope someone can shed some lite. Here is the code:
#!/usr/bin/perl
use DBI;
$ENV{'ORACLE_HOME'} = "/opt/app/oracle/product/9.2";
$ENV{'PATH'} =
"/opt/app/oracle/product/9.2/bin:/usr/ccs/bin:/opt/studio9/SUNWspro/bin:
/opt/app/oracle/product/10.2/bin:/opt/CA/Shar
edComponents/dmscript/bin:/usr/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/lo
cal/bin:/opt/app/oracle/product/8.1.7.4/bin:/usr/local/bin:/
usr/local/apps/common/devl/bin:/usr/lbin:/usr/ccs/bin:/usr/xpg4/bin:/opt
/pkware:/opt/RICHPse/bin:/opt/RICHPse/examples:/opt/sas82:/o
pt/sas82/sasexe:/opt/Navisphere/bin:/opt/fileport/bin:/opt/syncsort/bin:
/usr/bin:/usr/openwin/bin:/opt/SUNWspro/bin:/opt/mfcobol/cob
ol/bin:/opt/starsql/bin:/opt/uni/bin:/opt/uni/scripts:/etc:/opt/emc/EMCp
ower/bin/sparcv9:/etc/emc/bin:/usr/local/nz/bin/:.";
my $dbh_inst1 = DBI->connect("dbi:Oracle:inst1", "schema", "password", {
RaiseError => 1, PrintError => 1});
$ENV{'ORACLE_HOME'} = "/opt/app/oracle/product/10.2";
$ENV{'PATH'} =
"/opt/app/oracle/product/10.2/bin:/opt/app/oracle/product/9.2/bin:/usr/c
cs/bin:/opt/studio9/SUNWspro/bin:/opt/CA/Shar
edComponents/dmscript/bin:/usr/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/lo
cal/bin:/opt/app/oracle/product/8.1.7.4/bin:/usr/local/bin:/
usr/local/apps/common/devl/bin:/usr/lbin:/usr/ccs/bin:/usr/xpg4/bin:/opt
/pkware:/opt/RICHPse/bin:/opt/RICHPse/examples:/opt/sas82:/o
pt/sas82/sasexe:/opt/Navisphere/bin:/opt/fileport/bin:/opt/syncsort/bin:
/usr/bin:/usr/openwin/bin:/opt/SUNWspro/bin:/opt/mfcobol/cob
ol/bin:/opt/starsql/bin:/opt/uni/bin:/opt/uni/scripts:/etc:/opt/emc/EMCp
ower/bin/sparcv9:/etc/emc/bin:/usr/local/nz/bin/:.";
my $dbh_inst2 = DBI->connect("dbi:Oracle:inst2", "schema", "password", {
RaiseError => 1, PrintError => 1});
$dbh_inst1->disconnect();
$dbh_inst2->disconnect();
exit;
Here is the error message:
DBI connect('inst2','schema',...) failed: ORA-12154: TNS:could not
resolve the connect identifier specified (DBD ERROR: OCIServerAttach)
Thanks.
Peter
Sounds as though you have hit an issue sharing an env.
I'm not sure as I've not done this myself but you may need to look at
the ora_envhp attribute:
The first time a connection is made a new OCI ’environment’ is created
by DBD::Oracle and stored in the driver handle. Subsequent connects
reuse (share) that same OCI environment by default.
The ora_envhp attribute can be used to disable the reuse of the OCI
environment from a previous connect. If the value is 0 then a new OCI
environment is allocated and used for this connection.
The OCI environment is what holds information about the client side con‐
text, such as the local NLS environment. So by altering %ENV and setting
ora_envhp to 0 you can create connections with different NLS settings.
This is most useful for testing.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
