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.

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

1.3 Perl for Oracle DBAs

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