Using Perl for Oracle Integration Projects

Java, ASP and .NET developers working with Oracle, as well as Oracle DBAs, face increasing pressure to find affordable, full-featured tools to help with database integration development projects. One of the most affordable options, Perl scripting, has now become an even more powerful, cross-platform weapon, according to a recently released hands-on tech volume from O'Reilly. Integration Developer News talked with the author to get tips, tricks, and even some code samples.

Tags: Perl, Oracle, Oracle DBAs, Database, Duncan, Developers, Perl DBI,

Java, ASP and .NET developers working with Oracle, as well as Oracle DBAs, face increasing pressure to find affordable, full-featured tools to help with database integration development projects. One of the most affordable options, Perl scripting, has now become an even more powerful, cross-platform weapon, according to a recently released hands-on tech volume from O'Reilly.

"Perl for Oracle DBAs is authored by Andy Duncan and Jared Still, two practicing Oracle DBAs who have discovered Perl's ability to help with a wide range of DBA tasks -- including setting up connectivity with other applications and databases, monitoring DB connectivity in real time and finding performance bottlenecks.

Integration Developer News caught up with Perl for Oracle DBAs coauthor Andy Duncan to get his view on how Java and ASP/.NET developers and Oracle DBAs might best use Perl -- without having to become Perl experts.

Easier Than You Think
First off, we found that the ramp-up for non-Perl programmers can be surprisingly easy, thanks to the authors' prep work. For starters, in addition to numerous code examples of the many Perl/Oracle technologies discussed, their book includes thousands of lines of ready-to-use DBA scripts and modules in the form of the "Perl DBA Toolkit," a project the authors have also released under the Open Source banner -- and it downloads as an Open Source project.

The Perl DBA Toolkit is a comprehensive suite of specialized scripts designed to help Oracle DBAs perform both routine and special purpose tasks associated with database setup for integration and connectivity, including monitoring the Oracle alert log and databases, creating and managing Oracle user accounts, maintaining indexes and extents, extracting DDL and data, and troubleshooting and tuning database problems.

"We created this toolkit mainly so that other people wouldn't have to code this backbone themselves," Duncan said. The Perl DBA Toolkit comprises of thousands of lines of code, and includes daemons, mailers, alert log checkers and so on. These all tackle the main backbone problems that face an Oracle DBA.s

Keeping Oracle Up and Accessible
"The two most important tasks for Oracle DBAs are to ensure their databases remain up and available within any specified service level agreements (SLAs), and to ensure their databases remain available over the network," Duncan told IDN. "It's no good being a perfectly functioning database on a remote server if nobody can talk to you!"

One of the best bang-for-the-buck projects that an Oracle DBA can build with Perl is to do background monitoring of the database, Duncan told IDN. Using the Perl DBA toolkit will "save the most headaches and costs on any Oracle system," Duncan said. The Perl DBA toolkit even has a configuration feature that will act like a web service notification application -- phoning the developer or DBA in the middle of the night if the database should unexpectedly fail, he added.

But Duncan states, while such an application is valuable and gives a DBA the most raw functionality per line of Perl code, building it correctly requires knowledge of how to use the Perl DBI (Database Interface) . Therefore, Duncan advises, "If you have limited knowledge of Perl, you're going to have to learn how this interface works before you can make Perl do useful work for you as an Oracle DBA."

In addition to using Perl DBI, the book also addresses the second really useful Perl extension module: DDL::Oracle. As its name suggests, this makes the creation of Oracle DDL statements, via Perl programs, a breeze.

Perl and Oracle DBAs Chapter 1 provides an extensive list of the authors' favorite Perl resources (both published and online). Also, the book's Appendix A runs through the major concepts behind Perl, and describes how to drill down on perldoc information to discover even more. In Appendix B, readers will find a complete guide to all of the Perl methods behind the Perl DBI, using concepts (such as object orientation and reference technology) introduced earlier.

DDL::Oracle: A Case in Point
Duncan worked through a quick example of using DDL::Oracle, taking advantage of Perl's object orientation to generate some DDL. "This is a technique that can be applied again and again, across the whole Oracle database," Duncan said, "whether as the SYSDBA, SYSOPER or whatever, in quick script toolkits. "

Duncan noted that DBAs often want to get hold of object
DDL. Here's the basic approach that can be modified a hundred different

1. First of all, set up the Perl script from the top, using warning and strict mode pragmas, setting ORACLE_HOME (which is necessary for Perl DBI to pick up the appropriate Oracle OCI libraries), and then importing the two highly useful Perl modules, DBI and DDL::Oracle, into the main program package.

[Notice this script is being run on a Win32 system, requiring an ORACLE_HOME of C:ORAHOME. This is partially to demonstrate Perl's ease of use both on Win32, and its original Unix home]


use strict;
use warnings;


use DBI;
use DDL::Oracle;

2. Then connect to the "orcl" Oracle database (or whatever network name you have for your database; i.e., the name you use to contact it via SQL*Plus).

We're going to connect as the SYSTEM user:

my $dbh = DBI-connect( "dbi:Oracle:orcl", "system", "manager",
{ PrintError = 0, RaiseError = 1 });

3. Next, configure DDL::Oracle using the Oracle database handle created above by the object-oriented magic of Perl DBI:

DDL::Oracle-configure( dbh = $dbh );

4. Now, get back to Perl DBI and prepare a statement handle to collect all of the table names owned by SCOTT, for which we'll be creating DDL later. Execute this statement and return a Perl reference scalar, which gives us access to all of the information collected by the SQL statement (All of the underlying detail is covered in Perl for Oracle DBAs Appendices A and B):

my $sth = $dbh-prepare( "SELECT owner, table_name
FROM dba_tables
WHERE owner = 'SCOTT'
my $list = $sth-fetchall_arrayref;

5. DDL::Oracle can now be used to generate a special object with which we can then create the relevant DDL statements for all of the tables gathered by the above SQL statement:

my $obj = DDL::Oracle-new( type = 'table',
list = $list

6. Finally, use the "create" method on the DDL::Oracle object to give us back all of the DDL "CREATE TABLE" statements, for SCOTT's tables. We print these out directly, via Perl's "print" statement:

print $obj-create;

7. Having completed our task, we can now disconnect cleanly from the target Oracle database:


8. The header, and the first table printed out by the $obj-create statement above, are detailed below:
REM This DDL was reverse engineered by
REM Perl module DDL::Oracle, Version 1.01
REM at:
REM from: orcl, an Oracle Release instance
REM on: Thu Sep 12 13:11:31 2002
REM Generating CREATE TABLE statements for:


CREATE TABLE scott.bonus
ename VARCHAR2 (10)
, job VARCHAR2 (9)
, sal NUMBER
, comm NUMBER
Using Perl to Bring Oracle into Your Web Services
Duncan also said Perl offers help for Oracle DBAs and developers seeking to tie their Oracle databases into live web services (interactive services, real-time portals, etc.).

"You could easily use Perl/Perl DBI and Apache to give you great access to web portal application creativity -- and the good news is, many people already have," he said. Perl for Oracle DBAs covers three techniques for using Oracle with Perl and Apache, using such tools as Karma and Oracletool, which already do this. Or, Duncan said, Oracle DBAs could take advantage of Perl XML technologies, particularly XML::DBMS, which Duncan called "superb." .

With the development of Oracle::OCI providing direct, one-to-one access to all of Oracle OCI's functions, the future looks very bright for web services and portal technology, getting right into the heart of the database. "Personally speaking, I'd head down the Perl XML/Perl DBI/mod_perl Apache route to get the most integrated functionality, " Duncan said, "and we've consequently tried to cover as much of this as we could."

Alternatively, DBI::ProxyServer daemon technology can provide the backbone to connect several machines. He also suggests DBAs/developers could take advantage of mod_perl, Apache::OWA, Apache::DBI, Embperl and Mason, in which Perl has been embedded directly within the Apache web server.

Scratching the Surface
The above example "only scratches the surface of what Perl can do for you, in just a few lines of code," Duncan told IDN. The book also includes code samples and techniques for Perl/Tk, mod_perl, Embperl, Mason, embedding Perl into PL/SQL and even gaining direct access to the Oracle OCI libraries via Oracle::OCI.

Duncan added, "If you don't want to get low-down and dirty with Oracle::OCI, Perl DBI still provides a powerful interface, hiding the OCI libraries from you, and because it can also easily connect to MySQL, Sybase and other databases, it once again frees you up, rather than restricting you."

In summary, Duncan said, "The beauty of Perl is that there are no pre-built limitations, especially with its dynamic abilities." He notes that with the 2000+ modules on CPAN (The Comprehensive Perl Archive Network)tackling just about every computing topic under the sun, plus the whole of regular Perl itself, he sees no limits to anyone's creativity.

While today's Perl might require DBA to "hand-crank much of [a coded solution] yourself," Duncan also noted that increased interest in Perl is resulting in more and more technologies that are making Perl easier to use.

Andy Duncan is the creator of the Orac Perl/Tk tool for Oracle DBAs, and has worked as an independent consultant for Oracle and Sun Microsystems, among other companies. Duncan is also a senior instructor for Learning Tree International.