Managing Oracle and Other Cool Things You Can Do with Perl

John D. Groenveld


Abstract

Perl has long been the "Swiss Army Knife" in the systems administrator's tool chest. With modules to access Oracle and other data sources, Perl has become one of the dominate programming languages for web-to-database development. In 1999, Orac, a cross-platform, cross-database administration tool, was released to the public. Written entirely in Perl, Orac offers many of the features of Oracle Enterprise Manager and proves that Perl is also an ideal language for developing GUI client/server applications.


Introduction

The power of Perl lies in its ability to solve a wide variety of problems. A typical programmer might initially learn Perl to solve a small, very specific problem like automating the parsing of a log file and then emailing the results. Instead of reinventing the wheel, she could download and use the Net::SMTP module from the Comprehensive Perl Archive Network (CPAN). Later, she could use Perl and the CGI module (CGI.pm) to do simple web scripting. Those scripts would then be connected to the database using Database Interface modules (DBI). After those CGI scripts become popular and performance begins to suffer, she would embed those scripts inside a mod_perl-enabled Apache webserver. While Perl is not always the perfect tool for every job, the availability of numerous modules makes it a good solution to many problems.


Perl

Perl or Practical Extraction and Report Language was created by Larry Wall and it is now supported by a community of developers under Larry's watchful eye. While most often found on UNIX systems, Perl has been ported to Microsoft Windows (95, 98, NT, 2000) and a variety of other operating systems. Perl includes a powerful regular expression engine, but is not just a text processing language. Even though Perl supports complex data structures as well as modular and object-oriented programming styles, the language is easy to learn.


Introduction to DBI

Perl is an ideal tool for manipulating databases; whether for importing data from alternative sources or processing queries. By the early nineties, database programmers had written several vendor-specific Perl extensions. The Oracle extension, oraperl, was developed by Kevin Stock. While these extensions worked well, it was clear that a cross-database architecture would be helpful. Such an architecture became possible with the advent of Perl5. Developed by Tim Bunce and others, the DBI consists of the database-independent interface module (DBI) and the collection of database-dependent drivers (DBD::*). The architecture allows programmers to write cross-database Perl scripts using an interface similar to ODBC. These scripts can access multiple database sources at the same time making it a popular data migration tool.


Introduction to Perl/Tk

Programming graphical user interface (GUI) applications in a low-level language such as C is not an easy task. Writing a GUI in C which will run under both Windows and UNIX is nearly impossible. One of the first cross-platform, GUI languages was the Tool Command Language with the Toolkit extension (Tcl/Tk.) The Tk extension provides a set of common GUI widgets such as buttons, listboxes, and scrollbars that can easily be imported into your program. In 1995, Nick Ing-Simmons ported Tk to Perl and created Perl/Tk.


Introduction to Orac

Given Perl's cross-platform GUI modules and powerful database API, developers quickly went to work on client/server applications. Two of the earliest Perl applications to used the DBI and Tk modules were Aligator Descarte's SevereMangler, an experimental replacement for Oracle's now defunct Motif svrmgrm, and Alan Burlison's ora_explain, a tool to view Oracle's query execution plans. In 1999, Andy Duncan began the ambitious project to create an open source, cross-database administration tool. The tool was named Orac after the fictitious super computer in the BBC television series "Blake's 7." Orac provides a framework for developers to add visualizations to common database-specific administrative commands. Like Enterprise Manager, Orac allows the DBA to view the SQL code associated with each command and it includes an interactive GUI shell. Orac also allows DBA's to easily add their own buttons and menus to the interface.


Web-Database Integration

Even though the World Wide Web was initially invented to distribute static technical reports, server authors quickly realized that users wanted to interface with server-side programs to perform tasks such as database queries. The Common Gateway Interface was created to provide a standard way for the browser to send arguments to those programs (referred to as CGI scripts) and then have those scripts return their results back to the browser. CGI scripts are easy to develop but they do not scale well because they are not persistent and must be invoked for each request. Web developers sought to mitigate performance problems and to extend the functionality of their webservers in more complex ways via server API's. The three most common server API's are Netscape's NSAPI, Microsoft IIS ISAPI, and the Apache API. The Apache API allows programmers to create modules written in C to modify the server. Doug MacEachern's module, mod_perl embeds the Perl interpreter inside the server. With a mod_perl-enabled Apache server, programmers can create their own modules to modify the server's behavior in each phase of a HTTP request. One of the most common mod_perl modules is Apache::DBI which creates persistent database connections. The module Apache::DBILogin allows the webserver to authenticate users against database logins. The HTML::Mason module allows web developers to embed Perl inside HTML. When these modules are combined, it is possible to create powerful, dynamic websites which are easy to maintain and scale well under heavy load.


Conclusion

Perl is an extremely powerful language and should not be relegated to the exclusive domain of UNIX hackers and Web jocks. The DBI provides a compelling reason for Oracle developers and administrators to learn and use Perl.


References

  1. Wall, Larry, Tom Christiansen and Randal L. Schwartz. Programming Perl 2nd ed. Sebastopol, CA: O'Reilly & Associates, 1996.

  2. Siever, Ellen, Stephen Spainhour, and Nathan Patwardhan. Perl in a Nutshell Sebastopol, CA: O'Reilly & Associates, 1999.

  3. Comprehensive Perl Archive Network. http://www.cpan.org/

  4. DBI - A Database Interface Module for Perl5. http://www.symbolstone.org/technology/perl/DBI/

  5. Walsh, Nancy. Learning Perl/Tk Sebastopol, CA: O'Reilly & Associates, 1999.

  6. Kitts, Kevin. "Oracle Database Administration on Linux with Orac-DBA." The Linux Journal Nov. 1999.

  7. Orac - The Open Source Database Administration Tool. http://www.kkitts.com/orac-dba/

  8. Descartes, Alligator and Tim Bunce. Programming the Perl DBI Sebastopol, CA: O'Reilly & Associates, 2000.

  9. Bunce, Tim. "Advanced DBI." Presentation at the Perl Conference 3.0, Aug. 1999.

  10. Stein, Lincoln and Doug MacEachern. Writing Apache Modules with Perl and C Sebastopol, CA: O'Reilly & Associates, 1999.

  11. Mason HQ - High-performance, dynamic web site authoring system. http://www.masonhq.com/


Acknowledgments

I am grateful to the Penn State Applied Research Laboratory for its support of this endeavor. I thank Tim Bunce for authoring and maintaining the DBI and DBD::Oracle modules. I thank Andy Duncan for leading the development of the Orac tool. Finally, I thank the thousands of programmers who contribute their time, energy, and expertise to the Orac, DBI, Perl, Apache, and other open source projects.