Programming, Perl + Oracle

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:

$dbh->commit;

$dbh->rollback;

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

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!

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.