Programming, Perl + Oracle
May 5, 2008
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.
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.
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.
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);
Here's the binding step. This is where you tell Oracle what values you want to use for your variables embedded in your sql:
The execute phase looks like the following:
$sth->execute || die $DBI::errstr;
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;
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.
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.
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.