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.
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.
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.
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.
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);
binding step. This is where you tell Oracle what values you want to use
for your variables embedded in your sql:
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;
also the expected compliment of calls such as:
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.
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.
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.
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.
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
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.
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.