Perl talking to Oracle

There is some data in an Oracle database that I need to retrieve regularly with a Perl script. So I need some sort of Oracle client to connect to the database so I can develop and and I need a library for my script to use to talk to it regularly. Oracle offers such a library. After your register you can get some non-Free software which is a pain to install. I'll now rant about the software.

Oracle says:

1. Download the appropriate Instant Client packages for your platform. All installations REQUIRE the Basic package.
2. Unzip the packages into a single directory such as "instantclient".
3. Set the library loading path in your environment to the directory in Step 2 ("instantclient"). On many UNIX platforms, LD_LIBRARY_PATH is the appropriate environment variable.
4. Start your application and enjoy.

The above implies that someone new to Oracle will have an easy time configuring and using this software. Not so. It assumes that you have some other files in your configuration and some other environment variables which it doesn't set.

Missing Secret Steps:

I installed three RPMs (using RHEL4U5):

 instantclient-basic-linux32
 instantclient-sdk-linux32
 instantclient-sqlplus-linux32

which unpack files in /usr/lib/oracle/10.2.0.3/client/lib and set up symlinks for sqlplus. The first think I see when I try to run it is:

sqlplus: error while loading shared libraries: libsqlplus.so: cannot
open shared object file: No such file or directory

Since I've got them installed in /usr/lib/oracle/10.2.0.3/client/lib I simply set my environment variable as per Oracle's instructions:

 LD_LIBRARY_PATH=/usr/lib/oracle/10.2.0.3/client/lib

I googled a little bit and found this so I also needed to set some other variables:

ORACLE_HOME=/home/oracle
ORACLE_BASE=/home/oracle
LD_LIBRARY_PATH=/usr/lib/oracle/10.2.0.3/client/lib
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME
export ORACLE_BASE
export LD_LIBRARY_PATH
export PATH

As you can see from the above I actually created a user oracle (with no password (i.e. !! in /etc/shadow)) and made an empty home directory. The RPMs won't create the oracle home directory which is this somewhat arbitrary place where oracle stores some config files.

Next time I tried to run it I saw:

Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software
directory

Turns out that you need this file in your ORACLE_HOME. Luckily I had a server where Oracle was installed and I found an sqlplus directory in the ORACLE_HOME which I scp'd over to my ORACLE_HOME:

# ls -R $ORACLE_HOME/sqlplus/
sqlplus/:
admin  doc  lib  mesg

sqlplus/admin:
glogin.sql  help  plustrce.sql  pupbld.sql

sqlplus/admin/help:
helpbld.sql  helpdrop.sql  helpus.sql  hlpbld.sql

sqlplus/doc:
cpyr.htm  oracle.gif  README.htm

sqlplus/lib:
env_sqlplus.mk  ins_sqlplus.mk  s0afimai.o

sqlplus/mesg:
cpyus.msb  sp1us.msb  sp2us.msb  sp3us.msb
cpyus.msg  sp1us.msg  sp2us.msg  sp3us.msg

I then tried to start sqlplus and saw: ORA-12162: TNS:net service name is incorrectly specified. Turns out that the names of the systems that you want to connect to are stored in a tnsnames.ora file. This files content's can look just like this:

FOO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = 
        (PROTOCOL = TCP)
        (HOST = db-server.tld)
        (PORT = 2321)
      )
    )
    (CONNECT_DATA = (SID = FOO)
    )
  )

and then you can pass it arguments like this:

 sqlplus user@foo

it will then lookup foo as above and prompt you for a password. Of course you'd change foo to your DB name and HOST to your DB server.

I ended up again borrowing tnsnames.ora from my working server (don't know what I would have done without it). Now, I wasn't certain of where to put it, but I used an evolutionary algorithm, i.e. I was able to waste some time experiementing until I got it right which made me feel like slime trying to evolve. Anyway, I borrowed the entire network directory from my working Oracle server and put it in ORACLE_HOME.

Inside of this directory was the ADMIN directory, at least that's how it was on the server that I borrowed from. I ended up doing a symlink with the lowercase version to finally get it working:

[root@host network]# ll | grep admin
lrwxrwxrwx  1 root   root    6 Aug 21 12:08 admin -> ADMIN/
[root@host network]#

So AFAICT, tnsnames.ora goes in $ORACLE_HOME/network/admin/.

Coming from MySQL all of this is a pain. I like installing my MySQL client with one command. I like being able to specify which host to connect to by passing the -h not editing a tnsnames.ora file (though I did have fun with the S-expressions). I like being able to use my package manager to have instant access to the libraries that I need. Instead the Perl module to talk to the database will complain if it can't find references to the Oracle client above as it tries to compile. Let's talk more about that.

CPAN

Don't think you can just skip here and just use CPAN without Enterprise Grade Genuine Advantage Enterprise Oracle Software.

cpan[3]> install DBD:Oracle
Running install for module 'DBD::Oracle'
...
Trying to find an ORACLE_HOME
Your LD_LIBRARY_PATH env var is set to ''

      The ORACLE_HOME environment variable is not set and I couldn't
      guess it.
      It must be set to hold the path to an Oracle installation
      directory
      on this machine (or a machine with a compatible architecture).
      See the README.clients.txt file for more information.
      ABORTED!
  
Warning: No success on command[/usr/bin/perl Makefile.PL]
  PYTHIAN/DBD-Oracle-1.19.tar.gz
  /usr/bin/perl Makefile.PL -- NOT OK

Even after installing the client as described above and getting sqlplus working from the command line first. I still had some trouble doing the above. I ended up downloading DBD-Oracle-1.19.tar.gz from: cpan into /usr/local/src/ and doing a:

 perl Makefile.PL 
 make 
 make install

I was then finally able to have some code do:

use DBI;
my $dbh = DBI->connect('DBI:Oracle:dbname', 'user', 'pass')
    or die "Couldn't connect to database: " . DBI->errstr;

The above seems to work.