Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

connecting toOracle7 and Oracle9 simultaneously

by bestrafer (Initiate)
on Jun 03, 2005 at 14:58 UTC ( #463296=perlquestion: print w/replies, xml ) Need Help??

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

Hi all. sorry for my english. I just started using perl, so I am not very experienced. I need to access an Oracle v7.x.x on Linux-box and Oracle v9.2.x on another one in the same script. I know that i need to install DBD-Oracle, but for which database version? What environement varibles should be set to access to oracle7 and oracle9? Is it possible at all to access two oracle databeses at the same time? Thanks in advance
  • Comment on connecting toOracle7 and Oracle9 simultaneously

Replies are listed 'Best First'.
Re: connecting toOracle7 and Oracle9 simultaneously
by elwarren (Curate) 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!
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";
      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?"

        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.

      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

Re: connecting toOracle7 and Oracle9 simultaneously
by jfroebe (Parson) on Jun 03, 2005 at 18:35 UTC


    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

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 - 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?"
      ... and if you get a chance (and remember), stop back and post the answer as future reference for others.
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 and 9.2 all the time, and so far, have not encountered any significant problems.
    Our DBD::Oracle was compiled using the 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...
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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://463296]
Approved by jfroebe
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (6)
As of 2019-11-17 13:34 GMT
Find Nodes?
    Voting Booth?
    Strict and warnings: which comes first?

    Results (86 votes). Check out past polls.