Hi folks,
This started off as a "why doesn't this work" post about the non ability
of a 8.1.7.0 64bit DBD:Oracle driver to talk to a 7.3.4.5.0 database.
I could talk ok to a 8.1.6.0 database so I assumed the DBD module was
broken :)
Using truss(1M) on Solaris I managed to figure it out so I'm posting
the recipe instead. My issues revolved around my dislike for the idea
of having a full Oracle installation laying around wasting disk space
when all I required was a few files. Unfortunately I was missing some
files that were needed to connect properly.
Firstly, as has been said here before, ensure the shell that will
run the code has the ORACLE_HOME and LD_LIBRARY_PATH variables
pointing to /oracle and /oracle/lib respectively. (Assumes /oracle
is the path to your Oracle installation) For CGI scripts, have the
HTTP server pre-set the environment variables. For Apache:
SetEnv ORACLE_HOME /oracle
SetEnv LD_LIBRARY_PATH /oracle/lib:/usr/lib/sparcv9:/usr/lib:/usr/local/lib
My test script:
#!/usr/local/bin/perl
#
# Program : test_sql.pl
use DBI;
my $dbh = DBI->connect("dbi:Oracle:MYSID", "account", "password");
$statement = "SELECT MODELS\n";
$statement .= "FROM CARS\n";
$statement .= "WHERE CARS.COLOR = \'RED\'\n";
print STDOUT "$statement\n";
my $sth = $dbh->prepare($statement);
$sth->execute;
while ( ($model) = $sth->fetchrow_array ) {
print STDOUT "$model is a red car\n";
}
What I was getting before was weird null password errors. This
boils down to the differences between Oracle 7 and Oracle 8, you
need to add more files to the local Oracle environment so it can
figure out what it has to send when connecting.
To see what was lacking I used truss(1M) to watch the proceedings.
I captured the output using script(1):
% script /var/tmp/script.log
% truss -x all -w all -r all ./test_sql.pl
[A whole lot of output here]
% exit
%
In the /var/tmp/script.log file was a record of every open(2) call
the program tried. Calls with ENOENT signify that the open() failed
and there is a missing file. Often you will see the open()s go
through your LD_LIBRARY_PATH in sequence until it finds the library
it needs. This is normal behaviour.
If it doesn't find the library it'll bomb out or in the case of the
DBD:Oracle module, I noticed it went off to the network share that
I compiled the DBD module against, taking a long time to open the
library via NFS and map(2) it into memory. (I was wondering why
scripts took a long time to load :)
So using the truss output I went through each file it was missing
and copied it into the local tree. My finished tree consists of:
oracle/tnsnames.ora
oracle/sqlnet.ora
oracle/lib
oracle/lib/libskgxp8.so
oracle/lib/libclntsh.so.8.0
oracle/lib/libwtc8.so
oracle/lib/libclntsh.so -> ./libclntsh.so.8.0
oracle/rdbms
oracle/rdbms/mesg
oracle/rdbms/mesg/expus.msb
oracle/rdbms/mesg/expus.msg
oracle/rdbms/mesg/oraus.msb
oracle/rdbms/mesg/oraus.msg
oracle/rdbms/mesg/ocius.msb
oracle/network
oracle/network/mesg
oracle/network/mesg/tnsus.msb
oracle/network/mesg/tnsus.msg
oracle/network/admin
oracle/network/admin/tnsnames.ora -> ../../../tnsnames.ora
oracle/sqlplus
oracle/sqlplus/mesg
oracle/sqlplus/mesg/sp1us.msb
oracle/sqlplus/mesg/sp1us.msg
oracle/sqlplus/mesg/sp2us.msb
oracle/sqlplus/mesg/sp2us.msg
oracle/ocommon
oracle/ocommon/nls
oracle/ocommon/nls/admin
oracle/ocommon/nls/admin/data
oracle/ocommon/nls/admin/data/lx1boot.nlb
oracle/ocommon/nls/admin/data/lx00001.nlb
oracle/ocommon/nls/admin/data/lx10001.nlb
oracle/ocommon/nls/admin/data/lx20001.nlb
oracle/ocommon/nls/admin/data/lx2001f.nlb
There might be more NLS files you will need based on your locale etc.
It's important to copy the files from the same Oracle installation that
you compiled the DBD module against, otherwise incompatibilities arise.
My mini oracle environment is under 20MB, instead of the 800MB instance
on the remote NFS share.
Now my script will connect to the down rev database and return data!
Hopes this helps anyone else that has had teething troubles.
Cheers,
Mark
