DBD::Oracle | ORAganism

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:

The database-independent part, the Perl-DBI, which is commonly included in many Linux distributions.
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 () {