DBD::Oracle module for Perl

This is a shameless post as my friend, Pawel Krol, pointed this out to me so I must give all credit to him. As I am a big fan of the Perl-DBI I thought this was too good to share.

Oracle 11.2.0.1 on Linux and possibly earlier versions is shipped with Perl environment complete with Perl-DBI and the “database dependent” Oracle::DBD module. I would expect it is included in other Oracle releases too, e.g. for AIX. I think Perl is a really useful skill for any DBA working on a Unix-like platform, maybe even Windows as well, and it’s even better when you can embed SQL statements in your code instead of calling SQL*Plus and trying to parse the output. Perl also gives you the ability to use bind variables properly and re-use statement handles, something you would need to write PL/SQL for, if using SQL*Plus.

There are two parts to the Perl database interface:

  1. The database-independent part, the Perl-DBI, which is commonly included in many Linux distributions.
  2. The database-dependent part, in Oracle’s case DBD::Oracle. This is rarely installed by default in a Linux distribution; you will usually just find the MySQL-specific module and one for handling CSV files.

Of course you could download and compile DBD::Oracle yourself but this makes life much easier in environments you don’t have control over as you can use it by just setting a few environment variables.

So how do you use it? There are cleverer ways, I know Pawel has one, but I just have a quick bash script I source into my environment:

#!/bin/bash

if [ -n $ORACLE_HOME ]; then
echo "Setting up Perl environment. ORACLE_HOME set to $ORACLE_HOME"
export ORACLEPERL=$ORACLE_HOME/perl
export PERL5LIB=$PERL5LIB:$ORACLEPERL/lib/5.10.0:$ORACLEPERL/lib/site_perl/5.10.0
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib32:$ORACLE_HOME/lib
export LIBPATH=$ORACLE_HOME/lib32
export PATH=$ORACLEPERL/bin:$PATH
fi

And then I can run my test script which just selects the current date from the dual table:

#!/u01/app/oracle/product/11.2.0/db_1/perl/bin/perl

use strict;
use warnings;
use DBI;

&main;

sub main () {

my %params = (
'database' => 'ORCL',
);

my $dbh = &dbconnect(\%params);

my ($sysdate) = $$dbh->selectrow_array("SELECT sysdate FROM dual");
print "Current date is $sysdate\n\n";

&dbdisconnect($dbh);

}

sub dbconnect () {

my $params = shift;
my $dbh = DBI->connect("dbi:Oracle:$$params{'database'}", 'username', 'password') or die ("$DBI::errstr\n\n");
return (\$dbh);
}

sub dbdisconnect () {

my $dbh = shift;
$$dbh->disconnect();
}

The world of Perl is now hopefully your oyster and there is plenty of documentation here.

About these ads

One thought on “DBD::Oracle module for Perl

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s