How to install DBD::Oracle

I am going to put here all the steps required to install DBD::Oracle CPAN module, which is not a straightforward installation. The purpose is to reduce headache and turn it into a repeatable process.

Jul 02 2013

Perl

SQL

Requirements

Use Oracle Instant Client

I really recommend to use your own Oracle lib, cause if your DBAs upgrade or dismiss or whatever the official Oracle installation, you will need to be warned and to reinstall DBD::Oracle since it links to the Oracle shared libs. Trust me, use Oracle Instant Client, at the time of this writing you can download it from here or just google it.

Select your platform, download the files and put them somewhere in your home dir or some other path you can read/write. Check also the right version for your installation: latest is 12.1.0.1 right now, but I had to use an older one, for instance, 10.2.0.5.

Ask your DBAs which version you should use, probably the safe choice is to pick up the same version as the remote database. So don’t guess, login to the database server and launch

$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Mar Mar 25 15:25:03 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL>

Type quit to exit. But remember that,

Oracle Instant Client right version depends only on your host Operating System version.

I’m going to put files under $HOME/opt/Oracle and store the .zip in the packages folder, so here there are my files

$ ls $HOME/opt/Oracle/packages
basic-10.2.0.5.0-linux-x64.zip
sdk-10.2.0.5.0-linux-x64.zip
sqlplus-10.2.0.5.0-linux-x64.zip

cd $HOME/opt/Oracle
unzip packages/basic-10.2.0.5.0-linux-x64.zip
unzip packages/sdk-10.2.0.5.0-linux-x64.zip
unzip packages/sqlplus-10.2.0.5.0-linux-x64.zip

Setup your environment

I use to create a .oracle_profile with environment variables I need, so I can source it from .bash_profile or from another session (for example from a CPAN shell, see below).

It is important you use the same environment variables as in your scripts as at the time of installation, since installation will create linked libraries.

$ cat $HOME/.oracle_profile

export ORACLE_BASE=$HOME/opt/Oracle
export ORACLE_HOME=$ORACLE_BASE/instantclient_10_2
export PATH=$ORACLE_HOME:$PATH
export TNS_ADMIN=$HOME/etc
export NLS_LANG=ITALIAN_ITALY.WE8ISO8859P15

# Set LD_LIBRARY_PATH avoiding a final colon.
if [ -z "$LD_LIBRARY_PATH" ]; then
	export LD_LIBRARY_PATH=$ORACLE_HOME
else
	export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
fi

Anyway avoid set LD_LIBRARY_PATH with a final colon (:) otherwise you can get unexpected results.

Yes I know! It is said that LD_LIBRARY_PATH is evil, but I couldn’t complete a working installation without it.

Also note that if you are using a full Oracle client, or even an Oracle server you probably should set it as LIBRARY_PATH=$ORACLE_HOME/lib.

Set NLS_LANG according to your needs. In the example above, it is ok for Italy and euro accounting (Western European character set).

TNS_ADMIN is the directory where you put your tnsadmin.ora, since we are not pointing to an Oracle server installation, we probably need also our own tns configuration.

Don' t forget to source it from your .bash_profile!

Latest DBI

Configure A CPAN client that works like a charm and install latest DBI

cpan DBI

Installation

Get DBD::Oracle CPAN module

Download latest version of DBD::Oracle CPAN module, right now it is DBD-Oracle-1.64.tar.gz: you can download it manually from CPAN, untar it and cd into it

wget http://search.cpan.org/CPAN/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.64.tar.gz
tar xzf DBD-Oracle-1.64.tar.gz
cd DBD-Oracle-1.64

or just open a cpan shell and type

$ cpan
...
cpan> look DBD::Oracle
...
Working directory is /home/wa/.cpan/build/DBD-Oracle-1.64-KIWHyH

Now you can source the .oracle_profile you created, to load the environment variables.

source $HOME/.oracle_profile

Check at least that sqlplus is working

sqlplus /nolog

If you see a SQL> prompt, it means your installation have good chances to complete successfully. If not, probably you picked up an Oracle Instant Client version that is not compatible with your Operating System. Create a log dir in your $ORACLE_HOME, otherwise you will find Oracle logs in your home dir.

mkdir $ORACLE_HOME/log

Compile

Now, clean your mind and pray the mantra

perl Makefile.PL
make

Troubleshooting

If make fails with an error like

/usr/bin/ld: cannot find -lclntsh
collect2: error: ld returned 1 exit status
make: *** [blib/arch/auto/DBD/Oracle/Oracle.so] Error 1

don’t worry! You can fix it creating a symbolic link

Check your actual libclntsh.so version

$ ls $ORACLE_HOME/libclntsh.so*
/path/to/your/instantclient_11_2/libclntsh.so.xy.z

for instance libclntsh.so.10.1, and then link it to the shared lib ld need to compile

ln -s $ORACLE_HOME/libclntsh.so.11.1 $ORACLE_HOME/libclntsh.so

Now try again compile steps.

Test

Run tests, it is always a good idea! But set the ORACLE_USERID and ORACLE_DSN env vars, otherwise it probably will default to scott/tiger and dbi:Oracle:testdb hence will fail (unless you have a brand new Oracle installation with the famous scott/tiger still around :).

export ORACLE_USERID=pippo/password123
export ORACLE_DSN='dbi:Oracle:mydsn'

make test

And finally

make install

Unit test

If some tests fails or you want to check connectivity to one or more databases to be sure the installation will not be useless, here it is a simple test you can fill with your credentials and run launching

prove oracle.t

use strict;
use warnings;
use DBI;
use File::Spec;
use Test::More;

my $user = 'scott';             # Change me
my $pass = 'tiger';             # Change me
my $conn = 'DBI:Oracle:testdb'; # Change me

ok $ENV{ORACLE_BASE}, '$ORACLE_BASE env var is defined';
ok $ENV{ORACLE_HOME}, '$ORACLE_HOME env var is defined';
ok $ENV{TNS_ADMIN},   '$TNS_ADMIN env var is defined';

ok -d $ENV{ORACLE_BASE}, '$ORACLE_BASE dir exists';
ok -d $ENV{ORACLE_HOME}, '$ORACLE_HOME dir exists';
ok -e File::Spec->catfile($ENV{TNS_ADMIN}, 'tnsnames.ora'), 'tnsnames.ora exists';

ok my $dbh = DBI->connect($conn, $user, $pass,{ PrintError => 1, }), 'database connection';

done_testing
__END__

Optimize Maxmind database loaded on Redshift using Analytical functions: If you need to associate an IP address to a country or a city probably you will use MaxMind data. If you load it in a relational database you will write a SQL statement that joins your traffic data with MaxMind data, which can be really heavy. This is an attempt to optimize queries by reducing the number of MaxMind data rows.
Redshift tips: I am using Redshift since two years ago, and as every database it has its SQL dialect and its secrets. I will write here everything I discover and it is worth to be annotated.
Getting started with PostGIS: PostGIS is a PostgreSQL extension that adds support for geographic objects allowing location queries to be run in SQL.
Compute ISP with Maxmind: This is a quick and dirty Perl script to compute the ISP of a given IP, using a Maxmind CSV file
AWS Redshift compatible PostgreSQL client: How to install a PostgreSQL client (psql) that is compatible with AWS Redshift
S3 to RedShift loader: Load data from S3 to RedShift using Lambda, powered by apex. Our goal is: every time the AWS Elastic load balancer writes a log file, load it into RedShift.
Early Perl6: Try first Perl6 stable release.
The camel replicates!: Install and use CPAN packages on an host without Internet access. On conquering hosts with a camel.
The camel survives!: About installing Perl in hostile environments.
A CPAN client that works like a charm: How to use and configure properly a CPAN client.
How to drop a user on Netezza: You are trying to drop a user but Netezza complains cause it "owns objects"? This article shows how to solve this problem.
How to collect Netezza history: Collecting your Netezza query history is a mandatory step before optimization. Read this article to know how to collect Netezza history easily.
Use nzpassword!: How to authenticate securely on Netezza.
sqlplus tips: Tricks and tips about your everyday Oracle sqlplus usage.
prove imProveD: Instructions to get a Perl test suite that checks everything is ok in your home dir.
How to use Devel::Cover: Quick introduction and usage of Devel::Cover to get code coverage metrics for Perl