Section 1.3. Perl for Oracle DBAs

1.3 Perl for Oracle DBAs

Perl has become an increasingly popular tool for Oracle DBAs who need a quick way of handling the 101 different jobs a DBA is expected to do every day. Perl is operating system-independent, powerful, flexible, remarkably quick to code, and extremely fast in execution. These capabilities are especially important if you are working in a rapidly changing environment where one day you might be populating a data warehouse from a difficult data source, and the next you might be generating all of the information for a dynamic web application ” and the whole time you're performing all of your usual administrative tasks . That certainly describes the diverse world of an Oracle DBA!

Of course, the focus of any Oracle site's business is data. And from the start, Perl was designed to be a data-processing engine, perhaps the finest and quickest in the world. It can find data, clean data, parse data, substitute data, print data, eat data, and spit data out from the other end in the exact format you require. It can do all of this with text data, binary data, and network data.

There are a variety of ways that Oracle DBAs can combine the power of Perl and Oracle. We describe four main paths in this book; the following list provides a road map:

Existing modules and applications

All kinds of excellent Perl modules and complete open source applications are freely available for Oracle DBAs to use. The chapters in Part II of this book describe the Perl/Oracle applications that we consider the best of the bunch; these are listed in Table 1-3 and fall into several categories:

Perl GUI applications

In Chapter 3, we describe Perl/Tk, Perl's own tookit for developing graphical user interfaces, along with a variety of graphical Oracle applications and helper modules: OraExplain, StatsView, Orac, DDL::Oracle , SchemaDiff , Senora , DBD::Chart , SchemaView-Plus, as well as some Perl GUI integrated development environments ( IDEs ) and debuggers .

Perl web-based applications

In Chapter 4, we discuss the use of Apache with Perl and Oracle and describe two particular applications, Oracletool and Karma. In Chapter 5 we show how using the Apache mod_perl module can greatly improve the performance of Perl web-based scripts. And in Chapter 6, we discuss two embedded Perl web scripting applications, Embperl and Mason.

Connectivity tools

In Chapter 2, we describe how to install Perl DBI and DBD::Oracle to allow your Perl programs to interact with Oracle databases with great ease and efficiency. Later chapters describe some additional connectivity tools. In Chapter 7 we describe the new Oracle::OCI module that provides higher performance and a true one-to-one mapping with functions of the Oracle Call Interface. In Chapter 8, we describe Perl's extproc_perl , Oracle's EXTPROC, and the other modules that allow Perl to be essentially embedded into Oracle's own PL/SQL language.

Database administration scripts

Just about every Oracle DBA has his or her own set of scripts they've written to make their daily lives easier. Many of these DBAs have been kind enough to share the wealth with their peers. Following this trend, we've packaged up our own set of scripts and modules into an open source collection we call the PDBA Toolkit. As a side benefit, the toolkit provides us with a living breathing entity whose code we can use to illustrate the use of Perl. We describe this toolkit in Part III of this book.

Data-processing scripts

Many Oracle DBAs spend at least part of their time dealing with data warehousing as well as database administration. They often need to clean and transform data that originates in other databases and applications and is now destined for Oracle. Perl, with its regular expressions and high performance, is one of the best solutions around for preparing data for use in data warehouse applications. Data munging is the term used to describe the data cleaning, formatting, and transformation often required by data warehouses. Appendix C, provides an essential guide to Perl regular expressions, and Appendix D, summarizes the many Perl modules available to perform data-processing and data-munging operations on all kinds of data, including numeric, text, date, and XML formats.

Custom scripts

Helpful as all of these packaged solutions may be, DBAs often find it necessary to write their own custom queries and scripts to solve their immediate problems. Every DBA ends up needing to write quick 5- or 10-line ad hoc programs simply to glue things together in their databases. They also may find that the canned applications and tools available for Oracle are great, but not quite right for their needs. The nice thing about Perl is that it makes it easy for you to add, change, or customize. All of the applications we describe throughout this book are available in source form so you can modify them to suit your needs. Our own toolkit is designed specifically to accommodate such customization. The modular nature of the scripts, coupled with the documentation provided in Part III of this book (see Chapter 13, in particular) should make it easy for you. You'll also find the appendixes helpful in learning the basics of Perl.

Table 1-3 lists all of the applications and tools mentioned in this book. We tried to include the most up-to-date information possible in this book at the time of publication, but because most of these programs are continually being enhanced, make sure to check out the sites listed in the table for current information.

Table 1-3. Perl/Oracle applications and related tools

Application/tool	Chapter	Description/download site
ActivePerl	1	Precompiled binary Win32 Perl from ActiveState http://www.activestate.com http://aspn.activestate.com/ASPN/Downloads/ http://aspn.activestate.com/ASPN/PPM/FAQ http://downloads.activestate.com/
Apache	4	Apache web server software http://www.apache.org/ http://httpd.apache.org/ http://httpd.apache.org/dist/httpd/ http://httpd.apache.org/docs/ windows .html http://httpd.apache.org/dist/httpd/binaries/win32/ http://httpd.apache.org/docs/mod/directives.html
Apache mod_perl	5	Apache Perl integration http://perl.apache.org/ http://www.cpan.org/authors/id/DOUGM/ http://www.modperl.com/ http://www.refcards.com/about/mod_perl.html http://theoryx5.uwinnipeg.ca/ppmpackages (Win32) http://theoryx5.uwinnipeg.ca/guide/ http://mathforum.org/epigone/modperl
Apache::DBI	5	Caching Perl DBI connections with mod_perl http://www.cpan.org/authors/id/MERGL/
Apache::OWA	5	Linking Perl to Oracle's PL/SQL Web Toolkit http:// sourceforge .net/projects/owa/ http://owa.sourceforge.net/ http://www.cpan.org/authors/id/S/SV/SVINTO http://www.cpan.org/authors/id/J/JI/JIMW/ (libapreq) http://technet.oracle.com http://technet.oracle.com/doc/windows/was.21/psqlwtlk.htm [2]
CPAN (2000+ packages)	1	The Comprehensive Perl Archive Network http://www.cpan.org http://search.cpan.org
Cygwin (and DJGPP)	2	Unix-like environments for Win32 http://www.cygwin.com/ http://cygwin.com/cygwin-ug-net/using-cygwinenv.html http://www.delorie.com/djgpp/
DBD::Chart (see also, zlib , gd , and PNG)	3	SQL-like chart generation using Perl DBI http://www.presicient.com/dbdchart/ http://www.cpan.org/authors/id/D/DA/DARNOLD/ftp://ftp.uu.net/graphics/jpeg http://www.ijg.org/ http://www.cpan.org/authors/id/NI-S/ ( Tk::JPEG )
DDL::Oracle	3	Perl package for specific Oracle DDL generation http://sourceforge.net/projects/ddl-oracle/ http://www.cpan.org/authors/id/R/RV/RVSUTHERL/
Embperl (see also HTML::Template and Mason)	6	HTML embedded Perl system http://perl.apache.org/embperl/ http://www.cpan.org/authors/id/GRICHTER/ ( Apache::SessionX and stable Embperl source) http://www.cpan.org/authors/id/A/AM/AMS/ ( Storable ) http://www.cpan.org/authors/id/JBAKER/ ( Apache::Session )http://theoryx5.uwinnipeg.ca/ppmpackages (Win32)
Exception (see also Perl GUI debuggers)	B	Java-like try and catch structures in Perl http://www.cpan.org/authors/id/P/PJ/PJORDAN/
extproc_perl (see also Perl DBI)	8	Oracle Perl Procedure Library (Perl linkage to PL/SQL) http://www.smashing.org/ http://www.cpan.org/modules/by-authors/Jeff_Horwitz http://technet.oracle.com http://download.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a88876/adg11rtn.htm http://download.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/manproc.htm http://otn.oracle.com/deploy/security/alerts.htm (Oracle security alerts)
gcc (see also Unix freeware)	2	GNU C compiler http://www.gnu.org/
gd (see also PNG and zlib )	3	Graphics drawing packages with Perl http://www.cpan.org/authors/id/LDS/ http://www.boutell.com/gd/ (gd)
gdb (see also gcc )	8	GNU debugger (for particular usage with gcc ) http://www.gnu.org/software/gdb/
HTML::Template (and Template Toolkit) (see also Embperl and Mason)	6	HTML embedded Perl system http://www.cpan.org/authors/id/S/SA/SAMTREGAR/ http://www.cpan.org/authors/id/ABW/ http://www.openinteract.org/ http://openinteract.sourceforge.net/ http://perl.apache.org/features/tmpl-cmp.html
Karma	4	Web tool for Oracle DBAs http://hypno.iheavy.com/karma/index.html http://www.cpan.org/authors/id/M/MA/MARKOV/ (MailTools) http://www.cpan.org/authors/id/KJALB/ (TermReadKey) http://www.cpan.org/authors/id/GBARR/ ( libnet )
LWP	5	Library for WWW access in Perl http://www.cpan.org/authors/id/GAAS/ (LWP,URI, MIME::Base64 , HTML::Parser , Digest::MD5 ) http://www.cpan.org/authors/id/S/SB/SBURKE/ ( HTML::Tagset ) http://www.cpan.org/authors/id/GBARR/ ( libnet ) http://www.cpan.org/authors/id/KWILLIAMS/ ( HTML::SimpleParse )
Linux packages	2	Linux application and package download sites http://www.redhat.com/apps/download/ http://www.suse.de/us/support/download/ http://www.linux-mandrake.com/en/ftp.php3 http://www.caldera.com/download/mirrors.html http://www.debian.org/distrib/ftplist http://www.turbolinux.com/download/ http://www.slackware.com/packages/
MSI (as standard from Win2000 onward)	2	Microsoft software package installer http://download.microsoft.com/download/platformsdk/wininst/1.1/NT4/EN-US/InstMsi.exe http://download.microsoft.com/download/platformsdk/wininst/1.1/W9X/EN-US/InstMsi.exe
Mason (see also HTML::Template and Embperl)	6	HTML embedded Perl http://www.masonhq.com/ http://www.cpan.org/authors/id/J/JS/JSWARTZ/ http://www.cpan.org/authors/id/DEWEG/ ( Time::HiRes ) http://www.cpan.org/authors/id/GSAR/ ( MLDBM ) http://www.cpan.org/authors/id/ILYAZ/modules/ ( FreezeThaw ) http://www.cpan.org/authors/id/A/AM/AMS/ ( Storable ) http://www.cpan.org/authors/id/D/DR/DROLSKY/ ( Params::Validate )
MySQL	D	Open source database http://www.mysql.com/ http://sourceforge.net/projects/mysql/ http://www.cpan.org/authors/id/JWIED/ ( DBD::mysql )
NMAKE (see also ActivePerl)	6	Pure Perl Win32 package compilation http://download.microsoft.com/download/vc15/Patch/1.52/W95/EN-US/Nmake15.exe
OraExplain (see also Perl DBI)	3	Perl/Tk Oracle SQL tuning tool http://www.cpan.org/authors/id/TIMB/
Orac	3	Perl/Tk general Oracle DBA tool http://www.cpan.org/authors/id/A/AN/ANDYDUNC/
Oracle::OCI	7	Direct Perl interface to Oracle Call Interface http://www.perl.com/CPAN/authors/id/TIMB/ http://archive.develooper.com/[e-mail]/ http://www.cpan.org/authors/id/T/TB/TBONE/ ( Data::Flow ) http://www.cpan.org/authors/id/HVDS/ ( C::Scan ) http://technet.oracle.com http://technet.oracle.com/tech/oci/ http://otn.oracle.com/tech/oci/htdocs/faq.html http://www.orafaq.org/faqoci.htm
Oracletool	4	General web tool for Oracle DBAs http://www.oracletool.com/ http://www.cpan.org/authors/id/GAAS/ ( Digest::MD5 ) http://www.cpan.org/authors/id/D/DP/DPARIS/ ( Crypt::IDEA and Crypt::Blowfish ) http://www.cpan.org/authors/id/LDS/ ( Crypt::CBC )
PNG (see also zlib and gd )	3	Portable Network Graphics (GIF image alternative) http://www.libpng.org/pub/png/ http://www.cpan.org/authors/id/NI-S/ ( Tk::PNG )
Perl	1	The main Perl portals and download sites http://www.perl.com http://www.perl.org http://www.perl.com/CPAN/README.html http://www.perl.com/CPAN/src/stable.tar.gz http://learn.perl.org/ http://history.perl.org/ http://www.wall.org http://lists.perl.org/ http://archive.develooper.com/
Perl DBA Toolkit (see also DDL::Oracle )	9	Our Perl toolkit for Oracle DBAs http://www.oreilly.com/catalog/oracleperl http://www.oreilly.com/catalog/oressentials/chapter/defrag.pdf http://www.cpan.org/authors/id/GBARR/ ( TimeDate ) http://www.cpan.org/authors/id/S/SI/SIFUKURT/ ( Crypt::RC4 ) http://www.cpan.org/authors/id/M/MI/MIVKOVIC/ ( Mail::Sendmail ) http://www.cpan.org/authors/id/SBECK/ ( Date::Manip ) http://www.roth.net ( Win32::Daemon )
Perl DBI and DBD::Oracle	1	Perl DBI portals and resources http://dbi.perl.org http://dbi.perl.org/doc/faq.html http://xmlproj.dyndns.org/dbi/faq.html http://archive.develooper.com/[e-mail]/ http://www.xray.mpe.mpg.de/mailing-lists/dbi/ http://xmlproj.com/PPM/ (latest Win32 packages) http://www.perl.com/CPAN/modules/by-module/DBI http://www.perl.com/CPAN/modules/by-module/DBD http://www.cpan.org/authors/id/TIMB/
Perl DBI ProxyServer(version numbers may change; if so, try http://search.cpan.org)	2	Proxy serving for Perl DBI http://search.cpan.org/doc/TIMB/DBI-1.20/lib/DBI/ProxyServer.pm http://search.cpan.org/doc/TIMB/DBI-1.20/lib/DBD/Proxy.pm http://www.cpan.org/authors/id/A/AM/AMS/ ( Storable ) http://www.cpan.org/authors/id/JWIED/ ( Net::Daemon and PlRPC ” which contains RPC::PlServer and RPC::PlClient )
Perl GUI debuggers (see also Exception)	3	Perl GUI debug tools http:// members .tripod.com/~CurtMcKelvey/perldbgui/ (perldbgui) http://www.cpan.org/authors/id/A/AE/AEPAGE/ (ptkdb) http://sourceforge.net/projects/open-perl-ide/ (OpenPerlIDE)
Perl IDE tools	3	Perl GUI development tools http://sourceforge.net/projects/open-perl-ide/ (OpenPerlIDE) http://sourceforge.net/projects/kpad/ (KakePad) http://www.xarka.com/optiperl/ (OptiPerl) http://www.activestate.com/Products/Komodo/ (Komodo) http://www.ultraedit.com/ (UltraEdit)
Perl SQL tools (see also Senora)	3	SQL*Plus-like Perl tools http://www.perldoc.com/perl5.6.1/lib/DBI/Shell.html http://dbishell.sourceforge.net/ http://piqt.sourceforge.net/ http://sourceforge.net/projects/dsql/
Perl XML (see later for specific XML Perl modules)	D	Perl XML resources http://www.xml.com/pub/q/perlxml http://xmlxslt.sourceforge.net/ http://perl.apache.org http://xml.sergeant.org/ http://www.xmlsoft.org/ http://www.xmlproj.com/perl-xml-faq.dkb http://www.perlxml.net http://www.cpan.org/modules/by-module/XML/ http://sourceforge.net/projects/perl-xml:
Perl conversion modules	D	Perl's main data conversion modules http://www.gnu.org/software/recode/recode.html,ftp://ftp.gnu.org/gnu/recode/ http://www.cpan.org/authors/id/CXL/ ( Convert::EBCDIC ) http://www.cpan.org/authors/id/COLINK/ ( Convert::SciEng ) http://www.cpan.org/authors/id/GENJISCH/ ( Convert::Translit ) http://www.cpan.org/authors/id/R/RR/RRWO/ ( Convert::Units ) http://www.cpan.org/authors/id/ANDK/ ( Convert::UU ) http://www.cpan.org/authors/id/E/ED/EDAVIS/ ( Convert::Recode )
Perl date modules (and other required helper packages)	D	Perl's major date-handling modules http://www.cpan.org/authors/id/D/DE/DESIMINER/ ( Date::Business ) http://www.cpan.org/authors/id/STBEY/ ( Date::Calc and Date::Pcalc ) http://www.cpan.org/authors/id/H/HF/HFB/ ( Date::Christmas ) http://www.cpan.org/authors/id/M/MI/MIDI/ ( Date::Decade ) http://www.cpan.org/authors/id/RBOW/ ( Date::Easter ) http://www.cpan.org/authors/id/B/BB/BBEAUSEJ/ ( Date::Handler ) http://www.cpan.org/authors/id/M/MI/MIYAGAWA/ ( Date::Japanese::Era ) http://www.cpan.org/authors/id/JTOBEY/ ( Date::Simple ) http://www.cpan.org/authors/id/T/TM/TMTM/ ( Date::Range ) http://www.cpan.org/authors/id/SBECK/ ( Date::Manip ) http://www.cpan.org/authors/id/B/BZ/BZAJAC/ ( DateTime::Precise ) http://www.cpan.org/authors/id/GAAS ( Mime::Base64 ) http://www.cpan.org/authors/id/D/DA/DANKOGAI ( Jcode ) http://www.cpan.org/authors/id/ADESC ( Devel ::CoreStack ) http://www.cpan.org/authors/id/MSCHWERN ( Test::Harness and Test::Simple )
Perl numeric modules	D	Perl's major numeric modules http://www.cpan.org/authors/id/L/LU/LUISMUNOZ/ ( Number::Encode ) http://www.cpan.org/authors/id/WRW/ ( Number::Format ) http://www.cpan.org/authors/id/S/SB/SBURKE/ ( Number::Latin ) http://www.cpan.org/authors/id/K/KE/KENNEDYH/ ( Number::Phone::US ) http://www.cpan.org/authors/id/W/WI/WIMV/ ( Number::Spice ) http://www.cpan.org/authors/id/L/LH/LHOWARD/ ( Number::Spell )
Perl/Tk	3	Perl GUI toolkit (derived originally from Tcl/Tk) http://www.lehigh.edu/~sol0/ptk/ptk.html http://www.perltk.org/ http://www.oreilly.com/catalog/mastperltk/ http://www.cpan.org/authors/id/NI-S/ http://www.cpan.org/authors/id/SREZIC/
SSL	5	Secure Sockets Layer for Perl and the Web http://www.openssl.org/ (OpenSSL) http://www.cpan.org/authors/id/C/CH/CHAMAS/ ( Crypt::SSLeay ) http://www.cpan.org/authors/id/SAMPO/ ( Net::SSLeay ) http://www.cpan.org/authors/id/A/AS/ASPA/ ( IO::Socket::SSL )
SchemaDiff	3	DDL::Oracle and Perl/Tk Oracle schema comparisons http://sourceforge.net/projects/schemadiff/
SchemaView-Plus (see also XML::Dumper and XML:: Parser )	3	Perl/Tk Oracle tool for viewing schema connections http://www.cpan.org/authors/id/M/MI/MILSO http://dbman.linux.cz ( dbMan , earlier related tool)
Senora (see also Perl SQL tools)	3	DDL::Oracle-based SQL*Plus-like tool http://sourceforge.net/projects/senora/
StatsView (see also PNG, zlib , OraExplain)	3	Perl/Tk statistics for Oracle on Unix http://www.cpan.org/authors/id/ABURLISON/ http://www.gnuplot. info / http://sourceforge.net/projects/gnuplot/
Unix freeware (also good sources for gcc binary packages; see also gcc )	2	Binary Perl packages for Unix http://sunfreeware.com/ (Solaris) http://ftp.univie.ac.at/aix/andftp://aixpdslib.seas.ucla.edu/pub/ (AIX) http://jazz.external.hp.com/src/index.html (HP-UX) http://freeware.sgi.com/index.html (IRIX) http://www.openbsd.org/ports.html (OpenBSD)
XML::Dumper	3	Dump Perl data to structured XML http://www.cpan.org/authors/id/E/EI/EISEN
XML::Generator::DBI	D	DBI and XML linkage package http://www.cpan.org/authors/id/M/MS/MSERGEANT http://www.cpan.org/authors/id/KMACLEOD/ (libxml-perl and XML::Parser::PerlSAX ) http://www.cpan.org/authors/id/K/KR/KRAEHE/ (XML::Handler::YAWriter) http://www.cpan.org/authors/id/GAAS/ ( MIME::Base64 )
XML::LibXML	D	Alternative Perl XML parser http://www.cpan.org/authors/id/M/MS/MSERGEANT/ ( XML::LibXML and XML::Sax ) http://www.xmlsoft.org (libxml2)
XML:: LibXSLT	D	Perl Extensible Stylesheet Language Transformations http://www.cpan.org/authors/id/M/MS/MSERGEANT/ ( XML::LibXSLT ) http://xmlsoft.org/XSLT/downloads.html ( libxslt ) http://www.w3.org/TR/xslt http://xmlsoft.org/XSLT/
XML::Parser	3	Perl XML parser http://sourceforge.net/projects/ expat (CXMLparser) http://www.cpan.org/authors/id/C/CO/COOPERCL
XML::XMLtoDBMS (see also XML::Parser and XML::LibXML )	D	The Perl port of XML-DBMS from Java http://www.rpbourret.com/xmldbms/index.htm (Perl port accessed from this page) http://www.cpan.org/authors/id/GBARR/ ( TimeDate )
XML::XPath (see also XML::Parser )	D	Perl and XPath http://www.cpan.org/authors/id/M/MS/MSERGEANT http://www.w3.org/TR/xpath
zlib (see also PNG)	3	Gzip's back-end compression library http://www.gzip.org/zlib/ http://www.zlib.org http://www.gzip.org/ http://www.info-zip.org/pub/infozip/ http://www.pkware.com/

^[2] As with most technet.oracle.com pages, this requires password-protected membership, which can be freely acquired from http://technet.oracle.com/membership/.