Programming, Perl + Oracle

Monday May 5th 2008 by Sean Hull

Perl is a powerful scripting language that 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.

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:

my $dbh = DBI->connect 
  ('dbi:Oracle:orcl', 'sean', 'hull') || die $DBI::errstr;

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:


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.   

» See All Articles by Columnist Sean Hull

Mobile Site | Full Site