connecting toOracle7 and Oracle9 simultaneously

bestrafer has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: connecting toOracle7 and Oracle9 simultaneously by elwarren (Priest) on Jun 03, 2005 at 18:48 UTC
I've done this, so I can answer :-) The interface changed between Oracle v7 and v8. Version 8 compiles against oci8. The DBD::Oracle module compiles against the binaries, so you will need to have the Oracle client software installed on each machine you want to run your script from. The server is licensed, the client is not, so it can be installed where ever you want as long as you don't build a database there. You cannot compile the module for v8 and copy it to machine with v7. When you compile the module against your 9.2.x.x libraries you will be able to connect to the 7.x.x database. Connecting to two databases at the same time, even in same script should not be a problem, I've done this for a dataload in the past. Your standard oracle environment variables should be set: ORACLE_HOME: where you installed the oracle binaries TNS_ADMIN: if your tnsnames.ora and sqlnet.ora files are located somewhere non-standard If you have problems connecting to the v7 from the v9 host, check your tnsnames.ora file to see if the entries have SERVICE names instead of SID entries. I think that's about it, off the top of my head. Good luck!	[reply]
Re: connecting toOracle7 and Oracle9 simultaneously by fauria (Deacon) on Jun 03, 2005 at 15:32 UTC
From DBI DBD::Oracle and OraPerl FAQ: "This example connects to two databases simultaneously:" `use strict; use DBI; my $dbh1 = DBI->connect( "dbi:Oracle:ORCL1", "scott", "tiger" ) or die + "Can't connect to 1st Oracle database: $DBI::errstr\n"; my $dbh2 = DBI->connect( "dbi:Oracle:ORCL2", "scott", "tiger" ) or die + "Can't connect to 2nd Oracle database: $DBI::errstr\n"; $dbh1->disconnect or warn "DB1 disconnection failed: $DBI::errstr\n"; $dbh2->disconnect or warn "DB2 disconnection failed: $DBI::errstr\n";` [download]	[reply] [d/l]
Re^2: connecting toOracle7 and Oracle9 simultaneously by dragonchild (Archbishop) on Jun 03, 2005 at 15:40 UTC
Unfortunately, the problem is connecting to two different database versions with the same DBD::Oracle, which isn't so easy. The problem is that DBD::Oracle compiles against a specific OCI. The OCIs for Ora7 and Ora9 are very different. I'm emailed Tim Bunce (the author of both DBI and DBD::Oracle) informing him of this thread. Hopefully, he will have time to respond. In general, if you think something isn't in Perl, try it out, because it usually is. :-) "What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against?"	[reply]
Re^3: connecting toOracle7 and Oracle9 simultaneously by gsiems (Deacon) on Jun 03, 2005 at 17:57 UTC
Disclamer: I haven't tried this with DBD::Oracle so I could be all wet. At work we regularly connect to oracle databases of one version using clients of a different version (i.e. connecting to a 9i or 10g database using an 8 client). The only issue that I seem to recall was trying to access new 8i functionality with an older (7.2 I think) client. As for which clients should work with which databases, there's a "Oracle Client / Server Interoperability Support" matrix out on the Oracle metalink site. Additionally, this note may also help clarify the OCI question a bit. Hope that helps.	[reply]
Re^4: connecting toOracle7 and Oracle9 simultaneously by jhourcle (Prior) on Jun 03, 2005 at 18:35 UTC
Re^5: connecting toOracle7 and Oracle9 simultaneously by gsiems (Deacon) on Jun 03, 2005 at 19:30 UTC
Re^2: connecting toOracle7 and Oracle9 simultaneously by Fletch (Bishop) on Jun 03, 2005 at 15:45 UTC
That connects to two databases, but it uses the same DBD::Oracle to to it. The OP's problem is that they've got one old and one new that'd (presumably) need two different compiles of DBD::Oracle to talk. This solution might work, but only if v9 is backwards compatible and able to talk to the older v7 instance. If that's not the case (i.e. v9 can't talk to a v7 DB; I don't know, I'm not an Oracle person): Compile DBD::Oracle against v9 (setting `ORAHOME` et al to point at that) Compile it again against v7, but pass a different `PREFIX` setting when you run `perl Makefile.PL` write one script that does a `use lib` to point to where you installed the v7 DBD::Oracle and does whatever you need there and saves the results to an intermediary file write a second script that uses the v9 DBD::Oracle in the default path to process the results from the first If you can't separate things into two programs (you need to consult the v9 database to know what you need from the v7 DB or what not) things get much more harry. In that case you might could use something like RPC::XML and write a server which provides results from one DB and the main program connects to the other DB and calls across to that XML-RPC server as needed. Update: Oop, looks like it is the case that they're not compatible by the above post. Looks like you're in harry land. -- We're looking for people in ATL	[reply] [d/l] [select]
Re: connecting toOracle7 and Oracle9 simultaneously by jfroebe (Parson) on Jun 03, 2005 at 18:35 UTC
Hi, Use the Oracle 9 libs. They are backwards compatible with Oracle 7 server. The reverse (oracle 7 libs accessing Oracle 9 server) is not true. Jason L. Froebe Team Sybase member No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1	[reply]
Re: connecting toOracle7 and Oracle9 simultaneously by dragonchild (Archbishop) on Jun 03, 2005 at 17:15 UTC
You'll need to send your question to dbi-users@perl.org - that's the DBI/DBD::Oracle mailing list. They'll be able to help you there. In general, if you think something isn't in Perl, try it out, because it usually is. :-) "What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against?"	[reply]
Re^2: connecting toOracle7 and Oracle9 simultaneously by etm117 (Pilgrim) on Jun 03, 2005 at 17:51 UTC
... and if you get a chance (and remember), stop back and post the answer as future reference for others.	[reply]
Re: connecting toOracle7 and Oracle9 simultaneously by mifflin (Curate) on Jun 03, 2005 at 18:42 UTC
We currently use DBI to connect to Oracle 7.3.4.5 and 9.2 all the time, and so far, have not encountered any significant problems. Our DBD::Oracle was compiled using the 7.3.4.5 oci but, we use the Oracle 9.0.1 tnslsnr. Oracle 9.0 seems to be a middle version that can communicate with a 7 and 9.2 database. You've probably realized this if you have ever attempted to create a database link in Oracle 7 to try and access an object in Oracle 9.2. You can't. However, you can use a 9.0 database is a hopping point. We have many apps here that take advantage of newer database packages by doing this 7->9.0->9.2 database link trick. It's not exactly the most efficient process but if you're desparate like we were...	[reply]
Re: connecting toOracle7 and Oracle9 simultaneously by samizdat (Vicar) on Jun 03, 2005 at 16:06 UTC
Let me first say that I've never had to do this, but here are several suggestions: Would suggest that you try renaming one DBD module. Since you're working with multiple boxen, port collision wouldn't be a problem. ENV variables might. Or, try writing two scripts, one with the path to O7's DBD and the other with O9's, and call them as transfer scripts from your main program. Take STDIN as SQL and pass it to DBI/DBD, and respond to STDOUT. You'd have to pass the data arrays by SHM, filesystem, or something like that. Alternately, you could run them as daemons on the individual boxen, and just pass SQL and the stringified response back.	[reply]


Your skill will accomplish what the force of many cannot
	PerlMonks