Programming, Perl + Oracle

May 5, 2008

1. Introduction

Perl is a
powerful scripting language that has grown into a fully-fledged programming
language, as good as Java (many would argue better), and though not as
efficient, at least as functional and powerful as C. It can meet your
day-to-day scripting needs as a database administrator, and if used prudently,
and responsibly, as a language to build large web-based applications.

Here's a
quick introduction to using Perl with Oracle. Take a look and I'm sure
you'll see it can be an ally to you for many of your day-to-day needs.

2. Overview

When you
write Perl code, and want to talk to a database, you use DBI, the database
interaction library. It includes drivers for all of the popular databases
so you can write programs that talk to multiple different types of databases,
or port your Perl code later, with ease.

The DBI is
quite mature, so you can expect full functionality, power, and performance when
you use it. Follow these guidelines for a first look at how it works.

a.
connect

The first
thing your Perl script must do is open a connection to your Oracle
database. Here's how you do that:

In this example,
the Oracle SID is "orcl", username is "sean", and password
is "hull". Perl returns a database handle, which you'll use in
your Perl code to interact with the database.

b.
prepare

This is
where you give Oracle your SQL query to parse. If you've used bind
variables, and run this query previously, chances are Oracle will already have
it cached, and this step will only involve Oracle finding the cached version.

$sql = 'select username from users where id = :a';

$sth = $dbh->prepare ($sql);

c. bind

Here's the
binding step. This is where you tell Oracle what values you want to use
for your variables embedded in your sql:

$sth->bind_param(1, '12345');

d.
execute

The execute
phase looks like the following:

$sth->execute || die $DBI::errstr;

e. fetch

There are a
bunch of ways to fetch rows into arrays, array references, hashes, and other Perl
data structures. We'll just use an array for simplicities sake:

@row = $sth->fetchrow_array;

f. other

There are
also the expected compliment of calls such as:

$dbh->commit;

$dbh->rollback;

There are
even some other interesting methods for controlling autocommit, handling LOB
data, metadata, errors, and other interesting stuff.

3. An Administrators Ally

Now that you
understand the fundamentals of how Perl talks to Oracle, let's take a look at
what you can use it for.

a. pdba
toolkit

This toolkit
was written by Andy Duncan and Jared Still to compliment their excellent
O'Reilly title "Perl for Oracle DBAs". Some of the things this
toolkit can help you with include getting the DDL for your db objects, killing
sessions, managing user accounts, and more.

b. cronjobs

As a DBA, we
all use cron to schedule jobs that do database work, backups and so
forth. You may have had your fill of shell scripts, with all their quirks
and idiosyncrasies. Enter Perl to save the day. It's much
more powerful for database access, and more importantly more efficient since
you can create handles, potentially multiple sessions to multiple databases,
perform work in parallel with different statement handles, and on and on.

c. alertlog
monitoring

Every DBA
knows about watching the alert.log file for errors. Now you can consider
using Perl to help you with that task. Perl includes a rich regular
expression implementation, which will come in handy when searching for errors
in your logfile.

d. using
with nagios

Nagios is a
great open source monitoring system which a lot of Unix administrators use to
watch networks, system uptimes, load averages, disk usage, and now
Oracle! From monitoring that your database is accessible to tablespace
usage, metrics or really anything you can write a query for, monitoring Oracle
just became a lot easier!

4. Web Applications

There are lots
of choices these days for building web applications, from Microsofts
proprietary solutions, to PHP and Java. Perl's advantages include mod_Perl,
described below, plus all the advantages you've grown to love about Perl, such
as powerful regular expressions, and a huge compliment of support packages and
libraries to choose from.

a. Using
with Apache

Out of the
box, Apache can run Perl code as CGIs. Essentially Apache asks the OS to
run the program, and whatever it returns, Apache then returns to the
client. This allows for all sorts of dynamic web application building,
and provides a powerful way to build internet applications.

b. Using mod_Perl

It's
important to make sure your Apache server is configured to use mod_Perl.
This brings the Perl engine into Apache for efficiency, caches previously
executed Perl scripts so they don't have to be compiled at call time, and then
thirdly it will cache connections to your database. So, when your scripts
make the DBI->connect call, Apache will first check if it already has a free
handle open to the target database, and give that to you if it does.

For Oracle
especially, this is extremely important. That's because Oracle uses
processes for each new session that is open (on Unix at least, on Windows it
uses threads). These processes are rather expensive to open, resource
& cpu-wise, so you want to open them ahead of time, and keep them
open. mod_Perl gives you this power.

5. Conclusion

Perl has a
strong and vibrant following in the open source world; nd for good reason.
It has the tremendous support of modules and libraries to extend its
functionality, a great community behind it, and the power and flexibility to
perform as both a scripting language for cronjobs and monitoring, as well as a fully-fledged
web-based development platform.