Multiple Oracle clients in same code - no usernames needed

[Jeff]

Hi all,

Hope someone can help.

I need to talk to both an oracle 8 and oracle 10 server in the same
script using their respective "external connections" capabilities (i.e.,
no user name or password -- system authentication on 8 & wallet on
10 ).

Hacked up a version of DBD to get everything renamed from 'Oracle' to
'Oracle8' and built against Oracle 8 libs. The other is built against
Oracle 10 libs. So I've 2 different builds in the same perl build:

./lib/site_perl/5.8.9/i686-linux/DBD/Oracle.pm
./lib/site_perl/5.8.9/i686-linux/DBD/Oracle8.pm
./lib/site_perl/5.8.9/i686-linux/auto/DBD/Oracle/Oracle.h
./lib/site_perl/5.8.9/i686-linux/auto/DBD/Oracle/Oracle.so
./lib/site_perl/5.8.9/i686-linux/auto/DBD/Oracle/Oracle.bs
./lib/site_perl/5.8.9/i686-linux/auto/DBD/Oracle8/Oracle8.so
./lib/site_perl/5.8.9/i686-linux/auto/DBD/Oracle8/Oracle8.bs
./lib/site_perl/5.8.9/i686-linux/auto/DBD/Oracle8/Oracle8.h

( Oracle.pm is oracle 10 & Oracle8.pm is oracle 8 )

I'm reading from a single tns_names.ora.
( SERV2 is Oracle 10 & SERV1 is Oracle 8 )
ORACLE_HOME is the same each time.
LD_LIBRARY_PATH includes 2 entries - 1 for Oracle 10 lib directory
and 1 for Oracle 8.
So the environment is the same every time.

I can:
--------------------------------------------
use DBD::Oracle8;
my $db3=DBI->connect("dbi:Oracle8:SERV1",'','');
--------------------------------------------

Or I can:
--------------------------------------------
use DBD::Oracle;
my $db3=DBI->connect("dbi:Oracle:SERV2",'','');
--------------------------------------------

but:
--------------------------------------------
use DBD::Oracle8;
use DBD::Oracle;

my $db3=DBI->connect("dbi:Oracle8:SERV1",'','');
my $db3=DBI->connect("dbi:Oracle:SERV2",'','');
--------------------------------------------
Will make both connections successfully, but exits
with a segmentation fault

Or if I reverse the connections:
--------------------------------------------
use DBD::Oracle8;
use DBD::Oracle;

my $db3=DBI->connect("dbi:Oracle:SERV2",'','');
my $db3=DBI->connect("dbi:Oracle8:SERV1",'','');
--------------------------------------------
first connection succeeds and second fails.

Also, these obviously fail because of wrong Oracle version:
--------------------------------------------
use DBD::Oracle;
my $db3=DBI->connect("dbi:Oracle:SERV1",'','');
--------------------------------------------
--------------------------------------------
use DBD::Oracle8;
my $db3=DBI->connect("dbi:Oracle8:SERV2",'','');
--------------------------------------------

Any ideas as to why? Thanks.

Jeff

[Martin J. Evans]

I'd hope someone else can come up with a different solution to your
problem but I'd be surprised if it worked as you have done it.

I'm assuming the DBD::Oracle8 is linked against a different set of
client libs to DBD::Oracle?

For a start, the 2 oracle client libs will export a lot of the same
symbols and so when you call oci_xxx where is it resolved - in the
oracle 8 client or the other one. To make this work you'd need the
dynamic linker to group the symbols and work down the group - I think it
/may/ be worth setting PERL_DL_NONLAZY and exporting it (or whatever it
is - run make test for DBD::Oracle and watch the output looking for the
xxxLAZY environment variable). If this works it pretty much proves it
but I'd still hope there is a better solution.

I think you have probably entered a world of pain.

Martin

[Steve Baldwin]

Probably a silly question, but why do you need two versions of
DBD::Oracle? Can't you build DBD::Oracle against an Oracle client
that is able to connect to both versions? I know the 9i client we
currently use will connect to both 9i and 11g DB instances. I'm
pretty sure a couple of years ago we had a version that would connect
to Oracle 7 and 9i.

May be a bit less hassle.

Steve

[Jeff]

Yes, They are built against the different
Oracle libs.

If anyone has solved this before I'm happy
to abandon this strategy
:-)

[jeff]

While any of the clients will connect to either DB with a username &
password,
the key requirement is connecting using an 'external' authentication
( ie, no name or password supplied in the DBI->connect )

The 10 client will use the wallet in Oracle 10, while the 8 client will
use the system login with Oracle 8 in our configurations.

But from what I've experienced the clients are not compatible with this
type of connection.

If I'm wrong please let me know, I would love to use just 1 client !

[Jeff]

Steve,
Were your connections 'external' - no password or name ?
If you can do that with a single client , please let me know. Thanks.

On Thu, 2009-10-29 at 06:48 +1100, Steve Baldwin wrote:

[Alexander Foken]

On 28.10.2009 20:38, jeff wrote:
> Hi all,
>
> Hope someone can help.
>
> I need to talk to both an oracle 8 and oracle 10 server in the same
> script using their respective "external connections" capabilities (i.e.,
> no user name or password -- system authentication on 8 & wallet on
> 10 ).
>

Install the Oracle 10 Client libraries and let them handle the
connection to both Oracle 8 server and Oracle 10 server. A difference
of "only" two major version numbers should be no problem for them. See
below.

> Hacked up a version of DBD to get everything renamed from 'Oracle' to
> 'Oracle8' and built against Oracle 8 libs. The other is built against
> Oracle 10 libs. So I've 2 different builds in the same perl build:
>

Much, much unneeded pain that way.

> I'm reading from a single tns_names.ora.
> ( SERV2 is Oracle 10 & SERV1 is Oracle 8 )
>

So, why don't you use the same client libs?

> ORACLE_HOME is the same each time.
>

That obviously can not work. If you have two sets of client libraries,
you need two different ORACLE_HOMEs.

> LD_LIBRARY_PATH includes 2 entries - 1 for Oracle 10 lib directory
> and 1 for Oracle 8.
>

So one of the DBDs will succed loading the libs, and the other will fail.

> So the environment is the same every time.
>

Sure, but it is broken.

>
> I can:
> --------------------------------------------
> use DBD::Oracle8;
> my $db3=DBI->connect("dbi:Oracle8:SERV1",'','');
> --------------------------------------------
>

Wrong. You NEVER load the DBD manually, DBI does that for you. And your
code lacks the standard error check documented in the DBI documentation.

> Or I can:
> --------------------------------------------
> use DBD::Oracle;
> my $db3=DBI->connect("dbi:Oracle:SERV2",'','');
> --------------------------------------------
>

Wrong, as above.

> but:
> --------------------------------------------
> use DBD::Oracle8;
> use DBD::Oracle;
>
> my $db3=DBI->connect("dbi:Oracle8:SERV1",'','');
> my $db3=DBI->connect("dbi:Oracle:SERV2",'','');
> --------------------------------------------
> Will make both connections successfully, but exits
> with a segmentation fault
>

Of course it segfaults, because one of the DBDs was compiled against
Oracle 8 client libs, and the other against Oracle 10 client libs, and
both attempt to load the same external libraries, because your
ORACLE_HOME is always wrong for one of the DBDs.

> Or if I reverse the connections:
> --------------------------------------------
> use DBD::Oracle8;
> use DBD::Oracle;
>
> my $db3=DBI->connect("dbi:Oracle:SERV2",'','');
> my $db3=DBI->connect("dbi:Oracle8:SERV1",'','');
> --------------------------------------------
> first connection succeeds and second fails.
>

Sure. Same reason.

>
> Also, these obviously fail because of wrong Oracle version:
> --------------------------------------------
> use DBD::Oracle;
> my $db3=DBI->connect("dbi:Oracle:SERV1",'','');
> --------------------------------------------
> --------------------------------------------
> use DBD::Oracle8;
> my $db3=DBI->connect("dbi:Oracle8:SERV2",'','');
> --------------------------------------------
>
> Any ideas as to why? Thanks.
>

Yes. Wrong way.

Get rid of all the files you created by torturing DBD::Oracle into your
DBD::Oracle8. Uninstall the Oracle 8 client. And to be sure, remove
DBD::Oracle and the Oracle 10 client, re-install the Oracle 10 client,
re-install an unmodified DBD::Oracle and compile it against the Oracle
10 client.

This way, you will end with a single DBD::Oracle compiled against Oracle
10 client, and all those binary code provided by Oracle, hidden in
several libraries, will take care of connecting to the Oracle 8 server.

Your Perl code will simply look like this:

#!/usr/bin/perl
use warnings;
use strict;
use DBI;
# no "use DBD::Oracle"!

my $ora8=DBI->connect('dbi:Oracle:SERV1','','') or die "Can't connect to
SERV1: $DBI::errstr";
my $ora10=DBI->connect('dbi:Oracle:SERV2','','') or die "Can't connect
to SERV2: $DBI::errstr";

See also
<http://search.cpan.org/~pythian/DBD-Oracle-1.23/Oracle.pm#CONNECTING_TO_ORACLE>
and
<http://search.cpan.org/~pythian/DBD-Oracle-1.23/Oracle.pm#CONNECTING_TO_ORACLE_II>
for a long discussions of all the strange ways you can use to connect to
Oracle.

Alexander

--
Alexander Foken
mailto:alex...@foken.de http://www.foken.de/alexander/

[Jeff]

Thanks for the input but perhaps the KEY criteria got lost in the
original post:

The script MUST connect to BOTH Oracle 8 and Oracle 10 WITHOUT
a username and password entered. From what I've seen a build on Oracle
10 will succeed ONLY with oracle 10 WITHOUT a user/password. The same
goes for Oracle 8.

There is a difference between versions in this regard and this is why I
can not use the same client libraries.

Have tested and both DBDs DO succeed loading their respective libraries:
oracle. Here are the minimum install for LD_LIBRARY_PATH
libclntsh.so.10.1* libnnz10.so*
oracle8:
libclntsh.so.8.0* libwtc8.so

And from what Ive seen it is LD_LIBRARY_PATH that allows the client libs
to be found - Not ORACLE_HOME. ORACLE_HOME is needed for tns_names.ora.

And I know there's no error checking, but please, lets not get hung up
on syntax - the examples are quick and dirty. ;-)

If can anyone successfully runs Alexander code when SERV1 is Oracle 8
and SERV2 is Oracle 10 - no username or password - PLEASE let me know
how its done.

Its my understanding that Oracle 8 uses system login info and Oracle 10
uses a wallet. If anyone has built a DBD that accomplished this in both
versions PLEASE let me know how.

Thanks. Any other solutions would be GREATLY appreciated.
;-)

[John Scoles]

I would have to agree with martin.

your best bet would be to get 1.17 ~1.20 of DBD::Oracle and then find a
copy of the Oracle9 client It sould be able to connect to 8 and 10
without any problem

[Jeff]

Please read the next post I put up to rephrase my question.
In all cases the connections have to 'external' - No name or password
given. The mechanism changed to the Oracle wallet in 10 for external
connections.

[John Scoles]

Sorry about that didn't see that one

Looking at this I can see your problem.

perhaps you can attempt a proxy type connection?

like though DBD::Gofer

You might get away with this with an 9 client and if memory serves me
correctly it can connect both wallet and the old 8 way

DBD::Oracle would then be able to handle both.

cheers
John Scoles

[Jeff]

9 hey ? Well that sounds promising.

Thanks, will look at that.

[Jeff]

No,luck:

/home/owuser1/perl58> perl test1.pl

ORA_OCI = 9 (9.2.0.4)

OWUSER1 OK :) Oracle8

DBI connect('OWSERV2','',...) failed: ORA-01017: invalid
username/password; logon denied (DBD ERROR: OCISessionBegin) at test1.pl
line 24
No Connection Oracle10

On Thu, 2009-10-29 at 08:06 -0400, John Scoles wrote:

[Jared Still]

On Wed, Oct 28, 2009 at 12:38 PM, jeff <je...@roqc.no> wrote:

>
> I need to talk to both an oracle 8 and oracle 10 server in the same
> script using their respective "external connections" capabilities (i.e.,
> no user name or password -- system authentication on 8 & wallet on
> 10 ).
>
>

I see lots of help offered on solving this problem.

What I am curious about is why is it a problem?

Why can you not use a username and password?

Some may say the answers to that question are obvious,
but that may not be the case.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com

[Jeff]

These perl apps run against internal databases where all users have
writeable access to data from multiple servers and , like it or not,
refuse to use a tool where they'd have to enter names/passwords - just
not an option.

The only oracle client that will handle the wallet on oracle 10 is the
10 client and it will NOT successfully connect to an 'external' defined
user on a remote Oracle 8 server. Again, security be darned, like it or
not, thats the just way it is. [ 'If you don't like go work somewhere
else' ;-) ]

Anyway the solution looks like a DBI::Proxyserver running client 8 and
the apps themselves running client 10 - 2 different perl installations.
Looks like it will work so far.

[Jared Still]

Bummer.

Thanks for the explanation.

I was going to recommend using a password server, but
probably not a good idea for multiple personal account
passwords.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com

[Jeff]

PS: Forgot to mention, these apps need to connect to an 8 and a 10
within the same script simultaneously. :-)

On Fri, 2009-10-30 at 08:27 -0700, Jared Still wrote:

[Matthew Watson]

Might not be an option...

But any reason why you couldn't just setup a database link between the
oracle 10 and oracle 8 servers? Have all clients connect to oracle 10, which
can pass through whatever you need to oracle 8 using the dblink.

Regards,
Matthew Watson
Production Support
Netspace Online Systems
E: matthew...@staff.netspace.net.au
W: (03) 98110010

> ______________________________________________________________________
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> ______________________________________________________________________

This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
Please notify the sender immediately by email if you have received this
email by mistake and delete this email from your system. Please note that
any views or opinions presented in this email are solely those of the
author and do not necessarily represent those of the organisation.
Finally, the recipient should check this email and any attachments for
the presence of viruses. The organisation accepts no liability for any
damage caused by any virus transmitted by this email.

[Jeff]

Going with DBI::Proxyserver. Seems to work well. All Oracle 10
connections are handled by script while all Oracle 8 are handle by
Proxyserver ( built with Oracle 8 libs ). :-)