OracleBlog

I work with several different databases for several different clients on several different operating systems, often with only remote access to a shell (as opposed to a desktop). Therefore I like to become familiar with very common and simple technologies, like vi, sqlplus (for Oracle) and Perl.

Writing my useful tools in Perl is very handy because I can run them anywhere, and they can work for all databases just by having a switch on the connection string. But of course today's example will focus on Oracle. Perl is also very good at handling and manipulating data: perfect for database utility scripts.

There are many different ways to write code in Perl. Some refer to Perl as a write-only language because it is a lot easier to write than it is to read. But I'll keep my sample simple.

#!/usr/local/perl

use DBI;

# Get a database handle by connecting to the database
$dbh = DBI->connect("dbi:Oracle:host=servername;sid=dbname",
'scott','tiger', {RaiseError => 1, AutoCommit => 1})
 or die "Can't connect to database $DBI::errstr\n";

# Put together your query string 
my $sql = 'SELECT * FROM emp';

# Instead you could do $dbh->do($sql) or execute
$sth = $dbh->prepare($sql);
while (@rows = $sth->fetchrow_array()) {
 
 # You can access a specific field like this: $rows[0];
 print "@rows\t";
} 
print "\n";
$sth->finish();

# If you did an update, you could $dbh->commit() 
# or $dbh->rollback() before disconnecting
$dbh->disconnect();

For more information, here is a quick and dirty FAQ:
http://www.orafaq.com/faqperl.htm

Here are some really good on-line samples
http://www.cri.univ-rennes1.fr/documentations/DBDOracle.html

There is a book on Oracle and Perl that described a bunch of Oracle DBA utilities written in Perl. I have not read it myself, so check it out before purchasing (and let me know what you thought).
"Perl for Oracle DBAs" by Andy Duncan and Jared Still
http://www.amazon.com/gp/product/0596002106/002-3253639-8672853?v=glance&n=283155