Connecting to more than one oracle database

--=__Part81A5A485.1__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

I am running Apache 2.0.55 with mod_perl 2.0.1 and Perl 5.8.1 on a Sun
Solaris machine. I am using DBI/DBD to connect to an Oracle database. I
am running DBI 1.39 and DBD-Oracle 1.16.

In order to connect to an oracle database on a different machine I have
a tnsnames.ora file with the appropriate definition. I also have a file
startup.pl with the following information:
$ENV{ORACLE_HOME} = '/usr/local/ora9i/9.2.0';
$ENV{ORACLE_SID} = 'asdb';
$ENV{TWO_TASK} = 'asdb';
$ENV{TNS_ADMIN} = '/usr/local/ora9i/9.2.0/network/admin/DEV_wiccdb';
In httpd.conf I have the line
PerlRequire "/www/httpd/conf/startup.pl"

I am currently connecting to an oracle database on a different machine
and everything is working fine. I would now like to connect to another
oracle database on another machine. My problem is how to define my
environment variables. ORACLE_HOME and TNS_ADMIN do not seem to be a
problem since I assume they can be the same. However, how do I define
ORACLE_SID and TWO_TASK so that they allow connecting to 2 different
databases on 2 different machines?

Thanks for any help.


Malka Cymbalista
Webmaster, Weizmann Institute of Science
malki.cymbalista@weizmann.ac.il
08-934-3036

--=__Part81A5A485.1__=--

RE: Connecting to more than one oracle database

Malka Cymbalista wrote:
> I am running Apache 2.0.55 with mod_perl 2.0.1 and Perl 5.8.1 on a Sun
> Solaris machine. I am using DBI/DBD to connect to an Oracle database.
> I am running DBI 1.39 and DBD-Oracle 1.16.
>=20
> In order to connect to an oracle database on a different machine I
> have a tnsnames.ora file with the appropriate definition. I also have
> a file startup.pl with the following information:
> $ENV{ORACLE_HOME} =3D '/usr/local/ora9i/9.2.0';
> $ENV{ORACLE_SID} =3D 'asdb';
> $ENV{TWO_TASK} =3D 'asdb';
> $ENV{TNS_ADMIN} =3D '/usr/local/ora9i/9.2.0/network/admin/DEV_wiccdb';
> In httpd.conf I have the line
> PerlRequire "/www/httpd/conf/startup.pl"
>=20
> I am currently connecting to an oracle database on a different machine
> and everything is working fine. I would now like to connect to
> another oracle database on another machine. My problem is how to
> define my environment variables. ORACLE_HOME and TNS_ADMIN do not
> seem to be a problem since I assume they can be the same. However,
> how do I define ORACLE_SID and TWO_TASK so that they allow connecting
> to 2 different databases on 2 different machines?

TWO_TASK and ORACLE_SID are defaults, and are not necessary if you can
specify the database name when you connect. Since you're using TNS, you
can just reference the TNS names in your connect string, e.g.:

my ($db,$user,$pass);

($db,$user,$pass) =3D ('asdb','user','pass');
my $dbh_1 =3D DBI->connect("dbi:Oracle:$db",$user,$pass)
|| die "can't connect: $DBI::errstr";

($db,$user,$pass) =3D ('other_db','user','pass');
my $dbh_2 =3D DBI->connect("dbi:Oracle:$db",$user,$pass)
|| die "can't connect: $DBI::errstr";

Regards,
Philip

Re: Connecting to more than one Oracle database

--=__Part7C584949.1__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Hello to all of you, Last week I sent an email asking how to connect to
more than one remote Oracle database. My problem was that in my
startup.pl file, I am setting the following environment variables:
ORACLE_HOME, ORACLE_SID, TWO_TASK and TNS_ADMIN. If I am trying to
connect to 2 different databases, then ORACLE_SID and TWO_TASK are not
the same and I did not know what to do.
SEveral people answered me and said that I should not define
ORACLE_SID and TWO_TASK in startup.pl but that I should define both
databases in tnsnames.ora and simply connect giving the name of the
database.

So now, I am doing the following:
My tnsnames.ora looks like:

asdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = computer1.weizmann.ac.il)(PORT
= 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = asdb.weizmann.ac.il)
)
)

asdb2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = computer2.weizmann.ac.il)(PORT
= 1521))
)
(CONNECT_DATA =
(SID = ORCL92)
)
)
I connect in the following manner:

DBI->connect("DBI:Oracle:asdb","user1","password1");
and
DBI->connect("DBI:Oracle:asdb2","user2","password2");

I have a script that works from the command line but does not work when
I run it from the web. When I run from the web it gives very
inconsistent results. Sometimes it succeeds in connecting to the first
database I specify and sometimes it can't connect to either. I tried
specifying the environment variables ORACLE_SID and TWO_TASK from within
the script but that didn't seem to help consistently. That is,
sometimes it worked and sometimes it didn't and we couldn't figure out
exactly when it would or would not work.
Does anyone know how I can connect via the web to 2 different oracle
databases sitting on 2 different machines from within the same script.

Thanks for any help.



Malka Cymbalista
Webmaster, Weizmann Institute of Science
malki.cymbalista@weizmann.ac.il
08-934-3036

--=__Part7C584949.1__=--

RE: Connecting to more than one Oracle database

Malka Cymbalista wrote:
[snip]
> I connect in the following manner:
>=20
> DBI->connect("DBI:Oracle:asdb","user1","password1");
> and
> DBI->connect("DBI:Oracle:asdb2","user2","password2");
>=20
> I have a script that works from the command line but does not work
> when I run it from the web. When I run from the web it gives very
> inconsistent results. Sometimes it succeeds in connecting to the
> first database I specify and sometimes it can't connect to either. I
> tried specifying the environment variables ORACLE_SID and TWO_TASK
> from within the script but that didn't seem to help consistently.=20
> That is, sometimes it worked and sometimes it didn't and we couldn't
> figure out exactly when it would or would not work.
> Does anyone know how I can connect via the web to 2 different oracle
> databases sitting on 2 different machines from within the same script.

I don't see anything in your example that would prevent you from making
connections to both databases from inside a web server. At this point,
it would be helpful to post a small program that demonstrates the
problem you're having.

Regards,
Philip

RE: Connecting to more than one Oracle database

Rohit V. Bhute wrote:
>> Does anyone know how I can connect via the web to 2 different oracle
>> databases sitting on 2 different machines from within the same
>> script.
>
> If its Perl CGI and Apache, have you added the following to your
> httpd.conf?
>
> SetEnv ORACLE_HOME

The OP found his problem and emailed me directly. It was a logic error,
apparently.

Regards,
Philip