Subject: Re: Help - multiple Oracle versions

From: Kristian Nielsen <[email-masked]>
Date: 05 Jan 2004 09:55:59 +0100

[Add notes to docs covering what's in this thread]

"Anniballi, Fran" <[email-masked]> writes:

> For option two, if I have two versions of DBD:Oracle, how do I tell
> Perl which one to use?  My scripts just say "/usr/bin/perl" in the
> first line.

Basically, when you compile you use the PREFIX option:

   export ORACLE_HOME=/usr/local/oracle8
   perl Makefile.PL PREFIX=/usr/local/oracle8-perl
   make
   make install

   export ORACLE_HOME=/usr/local/oracle9
   perl Makefile.PL PREFIX=/usr/local/oracle9-perl
   make
   make install

Then before running the perl script you should set the PERLLIB
environment variable to point to the correct location for the proper
version of DBD::Oracle. Alternatively, you could have the perl script
select the proper DBD::Oracle location based on the value of
ORACLE_HOME:

  #! /usr/bin/perl
  BEGIN {
    if($ENV{ORACLE_HOME} =~ /8/) {
      use lib '/usr/local/oracle8-perl/REST/OF/PATH';
    } else {
      use lib '/usr/local/oracle9-perl/REST/OF/PATH';
    }
  }
  use DBI;

This is all from the top of my head; you will need to fiddle a bit to
get the details right (like the correct path etc). Also, I think this
has been covered in greater detail before; you might want to try
searching the archives.

 - Kristian.

---

From: Jared Still <[email-masked]>
Date: 06 Jan 2004 08:25:59 -0800

I read quickly through this thread, so my apologies if someone
already pointed this out.

It doesn't matter which version of Oracle you compiled DBD with,
you can connect to all your 8i/9i databases on any platform with it.

Assume you compiled with 9i libs, and you can connect to 9i db's with
no problem, but are unable to connect to 8i, getting the error you
mentioned previously.

It appears that you are changing your Oracle environment via the 
oraenv shell script to change your ORACLE_HOME to the 8i version 
of Oracle.

If so, stop doing that.  Just leave your environment at 9i, and
connect to your 8i database.  It will be fine. This is the way
my linux server is setup, and DBD is used to connect to Oracle
versions 7.3, 8.1.6, 8.1.7 and 9.2.0 every day.

At our site DBD is compiled with 8i libs so that we can still
connect to the version 7 databases.  If you compile with 9i libs,
you will be unable to connect to version 7 databases.

HTH

Jared

On Sun, 2004-01-04 at 05:12, Anniballi, Fran wrote:
> This is all done on one UNIX box.
> 
> If I try option one, it doesn't work. I can't access an oracle8 instance with oracle9 sqlplus (or the other way around).  That is the real problem.  Since I can't do this I have to reassign the pointers before I access it.  I can do this fine with sqlplus but with dbi/dbd I have to compile with one or the other.
> 
> For option two, if I have two versions of DBD:Oracle, how do I tell Perl which one to use?  My scripts just say "/usr/bin/perl" in the first line.
> 
> -----Original Message-----
> From: [email-masked] [mailto:[email-masked]]
> Sent: Saturday, January 03, 2004 1:04 PM
> To: [email-masked]
> Cc: Anniballi, Fran
> Subject: Re: Help - multiple Oracle versions
> 
> 
> > As soon as I recompile the same DBI/DBD with it pointing to Oracle 9
> > environment (libraries), it doesn't work.  It is looking for Oracle9
> > library files and I obviously don't have it pointing to oracle9
> > libraries when I access an Oracle 8 instance.  Oracle 9 is not
> > compatible with Oracle 8 so I have to redirect the environment
> > variables at run time.
> 
> It sounds like you are confusing the Oracle client version and the
> Oracle server version.
> 
> Eg. if you set ORACLE_HOME to /usr/local/oracle8 (or whatever) and use
> /usr/local/oracle8/bin/sqlplus you are using the Oracle 8 client, while
> if you set it to /usr/local/oracle9 and call
> /usr/local/oracle9/bin/sqlplus, you are using the Oracle 9 client.
> 
> Which Oracle instance you access is selected by the connection string;
> for example "sqlplus scott/tiger@DB8" might access the Oracle 8
> instance, and "scott/tiger@DB9" might access the Oracle 9 instance.
> 
> What people are telling you is that you can choose client version
> independently of the server version. For example, it is possible to set
> ORACLE_HOME to /usr/local/oracle9 and call
> 
>     /usr/local/oracle9/bin/sqlplus scott/tiger@DB8
> 
> to access the Oracle 8 instance with the Oracle 9 client. This of course
> requires that the definition for DB8 is present in the file
> /usr/local/oracle-9.2/network/admin/tnsnames.ora.
> 
> So you should either
> 
> 1. Use only the Oracle 9 client, not change ORACLE_HOME, and access both
>    instances with Oracle 9 sqlplus and DBD::Oracle compiled against
>    Oracle 9 libraries.
> 
> or
> 
> 2. Compile DBD::Oracle twice (once against Oracle 8 libraries, once
>    against Oracle 9 libraries, just as you have two versions of sqlplus)
>    and use each one with the proper ORACLE_HOME setting.
> 
> My guess is that you will find possibility 1. the easiest.
> 
> > The tnsnames.ora are all set.  I did what you said already.  
> > 
> > Example: DBI/DBD will work fine if I compile the DBI/DBD pointing to
> > Oracle 8 environment(libraries) and access an Oracle 8 instance.  I
> > didn't have to change tnsnames.ora
> 
> Yes you do: You need to have BOTH Oracle instances defined in EACH
> tnsnames.ora.
> 
> Hope this helps,
> 
>  - Kristian.