Perl for Oracle DBAs | O'Reilly Media

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Perl is the world's number one solution for transforming and gluing data together, and Oracle is the world's number one solution for storing that data. In this book we'll explore the interface between two of the finest American inventions since baseball and pretzels. We're going to grab that Oracle data, we're going to flip that Oracle data, and we're going to munge that Oracle data. And we're going to do it all in Perl!

The goal of this book is to explore the frontier connecting the Perl and Oracle worlds, having as much fun along the way as possible. There are many routes through this largely unexplored territory, and one we think is particularly important is the one focused on Oracle database administration. We are Oracle DBAs ourselves and we know the frustrations the job can bring. We've found Perl an enormous help to us in performing administrative tasks — both routine ones, like adding new users to the database, and more complex ones, like monitoring database connectivity in real time and tracking down database performance problems by comparing SQL execution plans. We want to share the information we've acquired over the years about Perl and its many Oracle applications. We also want to give you access to our own Oracle database administration scripts, which we've packaged up in the Perl Database Administration (PDBA) Toolkit described in this book and freely available on the O'Reilly web site.

This chapter sets the scene by introducing you to Perl and how it connects to Oracle. We'll look at the following:

Perl's origins and advantages: We'll take a look at where Perl came from and what makes it such a popular and powerful language.
Perl/Oracle architecture: We'll see how Perl connects to the Oracle database via the Perl DBI module, the DBD::Oracle program, and Oracle's own OCI product. These modules interact to allow Perl programs access to Oracle databases.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Perl is a wonderful language with a rich history and culture. Many books have been written about its capabilities and roots. In this book we'll be focusing on how Perl and Oracle work together, and we'll only skim the surface of Perl's overall capabilities, giving you just enough detail so you'll appreciate what Perl can do for you.

In a nutshell, Perl is a freely available interpreted scripting language that combines the best capabilities of a variety of other languages. Despite borrowing other language capabilities, the whole of Perl is far greater than the sum of its parts. Perl was designed especially to be:

Extremely fast, in order to be useful when scanning through large files
Especially good at text handling, because data comes in many different forms and Perl has to handle them all
Extensible, in order for Perl to expand users' horizons, not restrict them

A tutorial for basic Perl is outside the scope of this book. Fortunately, there are many excellent web sites and books containing the information you need to get going. We've collected references to what we consider to be the best Perl books and online documentation in Section 1.4 at the end of this chapter. The appendixes provide quick references to different aspects of Perl's capabilities. For online information, check out the main Perl portals at:

http://www.perl.com
http://www.perl.org
http://www.activestate.com (for Win32)

Before we get into the details of how Perl and Oracle interact, let's take a step back to look at where Perl came from.

Larry Wall created Perl back in 1987 with the goal of making "the easy things easy and the hard things possible" — originally just for himself, but ultimately for a whole generation of developers. Larry had been working on a complex system and had been trying to get Unix's

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

How do Oracle DBAs, developers, and users take advantage of everything that Perl has to offer? The architecture illustrated in the figures in the following sections show how the various Perl and Oracle modules fit together to make the Perl/Oracle connection clean and efficient. In the following sections we'll take a look at the main components of this architecture:

Perl DBI
DBD::Oracle
OCI

Perl DBI and DBD::Oracle are Perl modules available from CPAN. OCI is an Oracle Corporation product that comes with all versions of the Oracle database.

Perl DBI is a generic application programming interface (API). It is similar in concept to ODBC (Oracle DataBase Connectivity) and JDBC (Java DataBase Connectivity), but it has a Perl-based object-oriented architecture. Perl DBI's object-oriented architecture allows it to have a single routing point to many different databases (shown in Figure 1-1), each via a database-specific driver. Oracle uses the DBD::Oracle driver, another Perl module that provides the actual communication to the low-level OCI code. It is OCI that makes the final connection to the Oracle database.

Figure 1-1: Perl DBI can interface to many databases

The beauty of Perl DBI is you can forget the details of the necessary connections beneath its simple API calls. The DBI package glides serenely over the surface of our databases, while the driver module, DBD::Oracle, does all the hard paddling beneath the surface.

Figure 1-2 shows how all the modules fit together on the Perl and Oracle sides.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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,

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

We've collected what we consider to be the best online and offline resources for Perl in the following sections. If you run into problems or just want to expand your horizons, do check out the books, web sites, and mailing lists summarized here.

Appendix A summarizes the essential elements of Perl's syntax, up to and including its object orientation. It also provides a full guide to the use of the very helpful perldoc command, which is the best way to access online manual page information on Perl once it has been installed.

Section 1.4.1.1: Perl web sites

The following web sites provide good springboards into the world of Perl:

http://www.perl.com: Contains everything you ever wanted to know about Perl.
http://www.perl.org: Another central resource for Perl users.
http://learn.perl.org: Site dedicated to people fresh to Perl.
http://history.perl.org
http://www.wall.org: Information on the history of Perl.

Section 1.4.1.2: Perl mailing lists

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Now that you've learned how Perl can ease the burden of Oracle database administration, you're probably eager to get started. This chapter explains how to install Perl for use with Oracle. We'll cover the following steps:

Installing Perl: If you're installing Perl on Unix, we recommend that you install directly from source. If you're installing Perl on Win32, we recommend that you use the pre-built ActivePerl distribution, available from ActiveState; ActivePerl has become the de facto standard Perl version for the Win32 platform.
Installing Perl DBI: Once you've installed Perl itself, you need database connectivity for it. We'll describe how to install Perl's generic DBI module, as well as DBD::Oracle, the Oracle-specific driver for Perl DBI.
Installing Cygwin: Cygwin is a complete Unix-like development environment that you can install on your Win32 machine. It allows you to combine the benefits of access to Unix compilers, interpreters, and other tools (e.g., Perl, gcc) with the convenience of traditional Win32 software (e.g,. Microsoft Word, Excel). We'll explain how to install Cygwin and get the various Perl modules running on it.

We described Perl's origins and advantages in Chapter 1. In this section, we'll describe the basics of the installation process for Perl. To obtain Perl and to get more detailed information about this process, check out these web sites:

http://www.perl.com
http://www.perl.org

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

We described Perl's origins and advantages in Chapter 1. In this section, we'll describe the basics of the installation process for Perl. To obtain Perl and to get more detailed information about this process, check out these web sites:

http://www.perl.com
http://www.perl.org: Main Perl portals.
http://www.activestate.com: ActiveState Win32 Perl portal site. You'll find many other projects, including ActivePerl, under the main ActiveState portal; most ActiveState products tend to have fairly fluid web addresses, so we won't attempt to provide them here.

There are three basic ways to get started with Perl on Unix and Linux systems, the third of which is best for reasons we'll explain shortly:

Find Perl already installed on your system.
Download a binary executable that will build Perl for you out of the box.
Configure and build Perl yourself from the source code.

On most Unix versions, if you install Perl as the root user and accept the default installation directories, it will generally embed itself into either one of the following directories:

/usr/bin
/usr/local/bin

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Now that you've installed Perl itself, you need to set things up so your Perl programs can communicate with your Oracle database. The best way to do this is via the magic of the Perl DBI module and its Oracle-specific database driver, DBD::Oracle. These modules let us gain access to our target database through the Oracle Call Interface provided by Oracle Corporation. The architecture for this arrangement, which takes full advantage of the object-oriented features available within Perl 5, is shown in Figure 2-2. This figure also demonstrates how the same Perl DBI interface can be used, with other drivers, to connect to other databases, and how all of these drivers are hidden from your Perl scripts by the DBI package.

This section focuses on the installation of Perl DBI. For more information about Perl DBI's capabilities, see Appendix B, and the book and online references listed in Chapter 1. For complete online information, go to http://dbi.perl.org.

We'll show how to install the DBI modules for both Unix and Win32.

Figure 2-2: The Perl DBI architecture

Before we get to the DBI and DBD::Oracle modules, however, we need to take a step back to discuss the methodology we'll be using for installing Perl modules onto Unix systems, both here and in the rest of the book.

There are two basic approaches to installing Perl modules (for example, Perl DBI, DBD::Oracle, and the many other modules we'll be discussing in later chapters) on Unix systems. The first is what some people call the traditional method. The second is the CPAN method. We recommend the traditional method, as we describe in the next section, but because the CPAN method is quite popular, we'll describe that one here as well.

Section 2.2.1.1: The traditional method

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Would this book be complete without a "Hello World" example? Of course not, so here goes! Our very simple Perl DBI script (in Example 2-1) will simply connect to the orcl Oracle database as the scott user, run through a straightforward SQL cursor on the DUAL table via a prepared statement, and then print out the result before logging off. We'll run the same script on both Unix and Win32 to demonstrate Perl's operating system independence:

Example 2-1. Our first Perl DBI script, HelloWorld.pl

                  #!perl -w
use strict;
use DBI;
# Connect to Oracle database, making sure AutoCommit is
# turned off and potential errors are raised.
  
my $dbh = DBI->connect( 'dbi:Oracle:orcl', 'scott', 'tiger',
                        { RaiseError => 1, AutoCommit => 0 } );
# Create the SQL.
  
my $sql = qq{ SELECT 'Hello World' FROM DUAL };
  
# Prepare the SQL and execute.
  
my $sth = $dbh->prepare( $sql );
$sth->execute(  );
  
# Fetch output rows into array, plus prepare a 
# print formatter for the results.
  
while ( my($helloWorldString) = $sth->fetchrow_array) {
  
   # Print out the result.
  
   print $helloWorldString, "\n";
}
$dbh->disconnect(  );  # Disconnect

Example 2-1 may look a little scary, but after reading Appendix B, you'll quickly be able to reduce it to the following:

#!perl -w
  
use strict;
use DBI;
  
my $dbh = DBI->connect( 'dbi:Oracle:orcl', 'scott', 'tiger',
                        { RaiseError => 1, AutoCommit => 0 } );
  
print $dbh->selectrow_array(qq{ SELECT 'Hello World' FROM DUAL });
  
$dbh->disconnect(  );

Note the following about Example 2-1:

If you know some Perl already, you may notice how we've only imported the DBI module, within the script, via Perl's use command. The Perl DBI package takes care of picking up DBD::Oracle, for us when we run the DBI->connect call. (The dbi:Oracle:orcl parameter string cleverly indicates that we want to use

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Cygwin is a free, open source Win32 porting layer for Unix applications, originally developed by Cygnus Solutions (now a part of Red Hat, Inc.) The Cygwin library brings to Windows the Unix system calls and environment that Unix programs expect. This makes it fairly easy to port Unix applications to Win32 without having to make extensive changes to the source code.

Cygwin is an increasingly popular solution for Win32 users who want at least occasional access to the compilers, scripts, and favorite Unix commands (e.g., grep, ps, sed) that their Unix counterparts take for granted. Of course, Linux is often a viable choice for PC users, but many of those users aren't willing to give up Microsoft Word, Excel, and the other standard Windows programs. Cygwin is a nice compromise. You can install it and have it available when you need to run a Unix program, without completely changing your environment. It's like having an extra driver in your bag of golf clubs; you may not need it all the time, but every now and then it is awfully useful!

If you're going to be using Perl on a Win32 platform, you may find Cygwin particularly useful. Because the gcc compiler comes with Cygwin, you'll be able to compile Perl, Perl DBI, and/or DBD::Oracle from source if you wish, rather than having to use the prebuilt binaries available from ActiveState. That way, you can customize Perl as needed to suit your own environment. And Cygwin also extends your reach; some of the Oracle applications we describe in this book, such as Oracle::OCI described in Chapter 7, or the latest XML parsers described in Appendix D (and many other open source applications as well), are yet to be available as Win32 executables. New Perl modules requiring C libraries don't tend to be available on ActivePerl for some time because of the required development lead-in time. You can keep ahead of the game with Cgywin.

You can learn much more about Cygwin at:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In Chapter 2, we looked at standard Perl and its database connectivity module, Perl DBI. The command-line interface available in Perl and Perl DBI has served developers well for many years. But over time, people have become more accustomed to graphical user interfaces (GUIs), and there has been a movement towards GUI facilities for Perl. Oracle DBAs in particular appreciate applications that give them an easy-to-use graphical interface for managing their databases and a way to visually inspect difficult-to-interpret database data. In this chapter we'll look at Perl/Tk, an extensive GUI-based toolkit, as well as at a number of applications (many of them based on Perl/Tk) that provide Oracle DBAs with the graphical interfaces they find so useful. We'll cover:

Perl/Tk: A popular toolkit often used to build GUIs in Perl for both Unix and Win32.
OraExplain: A Perl/Tk SQL tuning tool for Oracle that explains SQL execution plans. OraExplain was the first major canned application that combined Perl DBI and Perl/Tk for Oracle. It also inspired many other Perl/Tk applications.
StatsView: A statistics-gathering tool written in Perl/Tk for Oracle DBAs who also perform Unix system administration. This tool collects all kinds of useful statistics and then displays them in enhanced graphical format, courtesy of the gnuplot program.
Orac: A GUI wrapper program built using Perl/Tk that provides a useful way to maintain a repository of configurable SQL scripts for interrogating and managing Oracle databases.
DDL::Oracle

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Perl/Tk module, developed Nick Ing-Simmons, is one of the most popular and useful of the Perl extension modules. Perl/Tk is a toolkit that gives Perl the ability to create interactive and full-fledged GUI-driven applications. Writing GUIs can be complex, but Perl/Tk makes it easy by making available standardized libraries of reusable GUI code (widgets and controls) that you can select as appropriate.

For those interested in writing Oracle DBA GUI applications of their own, we'll try to cover all the bases in this chapter, but we'll mostly focus on the tools currently available for those simply looking for ready-to-use database administration and tuning programs. For more information on generic Perl/Tk issues, these are the online and book resources we consider to be best:

http://www.lehigh.edu/~sol0/ptk/ptk.html: Stephen Lidie's central portal, for all things Perl/Tk.
http://www.perltk.org: Didier Ladner's central Perl/Tk resource.
http://www.oreilly.com/catalog/mastperltk: For those who prefer information in book form, we thoroughly recommend Mastering Perl/Tk, by Nancy Walsh and Stephen Lidie (O'Reilly & Associates, 2002). As one of us helped technically review this book, we must admit a bias, but it's the definitive text.

For a quick example of what Perl/Tk can do for you, take a look at the widgets demonstration program in Figure 3-1, which comes automatically with the Perl/Tk installation.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

OraExplain, an Oracle tuning tool that DBAs can use to explain SQL execution plans and examine their SQL cache, was the first widely available tool that combined Perl/Tk and Perl DBI. OraExplain was created by Alan Burlison, a Solaris kernel gatekeeper for Sun Microsystems, and Tim Bunce quickly added the original ora_explain.pl module to the DBD::Oracle driver download bundle. So when you obtain DBD::Oracle, you'll get OraExplain automatically (at least if you're installing on Unix).

If you're on Win32 and using ActivePerl's DBD-Oracle8 package, you'll find that OraExplain fails to come with the download. However, you can get hold of the source bundle containing the precursor file, ora_explain.PL, from the following site:

http://www.cpan.org/authors/id/TIMB/

If you use WinZip or another decompression tool, you'll find the ora_explain.PL file within the main unload directory, as shown in Figure 3-5.

Figure 3-5: Finding and running OraExplain

Follow these installation steps:

Once you've extracted, or located, the ora_explain.PL precursor file, run the following command:
```
$ perl ora_explain.PL
```
This extracts the actual ora_explain application. Now we can run it:
```
$ perl ora_explain
```
The rest of the steps should be fairly straightforward once you've connected to your target Oracle databases. Check out the program and consult the instructions provided with it if you need help.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

After his triumph with OraExplain, Alan Burlison went on to create yet another superb Perl/Tk application, StatsView, a program designed for use by both system administrators and Oracle DBAs using Unix systems. If you serve both functions at your site, we feel confident that you'll love StatsView too. Although StatsView is aimed clearly at Solaris, the program's Oracle-based monitoring is equally applicable to other Unix operating systems, so we thought we'd install it on Linux to see how far we could push it.

Like many of the best things in life, StatsView comes with a few challenges:

You have to preinstall the gnuplot command-driven plotting program, which itself relies on various C libraries, depending on how you configure it. The gnuplot program plots functions and data points in many different formats in either GIF or PNG image formats. See Figure 3-6 for a typical example.
You'll also need an extra Perl/Tk module containing cutting-edge widgets.

Figure 3-6: StatsView and some gathered tuning figures

Although installing StatsView requires some special challenges, it comes with a silver lining. In the course of getting StatsView to work, you'll have installed some of the best Perl and C libraries around for performing graphical information plotting on both Unix and Win32. Here's where you start going beyond Perl Imperial Trooper rank and start heading towards Perl Sith Lord status!

The first thing we need to do for StatsView is to get hold of the extended Tk::GBARR Perl/Tk module and layer it over the standard Perl/Tk Unix distribution. We'll describe that task and the other installation procedures in the following sections.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Orac is a tool developed by one of your authors, Andy Duncan. It is based on Perl/Tk and its many widgets, and it employs Perl DBI to connect to the Oracle database. It is basically a GUI wrapper containing a large repository of prepared, configurable SQL scripts that allow Oracle DBAs to interrogate and manage their databases. Using Orac, users can rapidly apply these scripts to any target databases without having to copy them from one machine to another via complicated directory structure installs and environment variable setups. If these scripts are no longer up-to-date because of changes to the Oracle data dictionary, they can be modified or changed directly within the repository. Orac makes it easy for you to make changes to the scripts.

Orac owes a great debt to many people besides its main author. Andy received early input from Dave Ensor, coauthor of Oracle Design (O'Reilly) and from Tim Bunce, chief creator of Perl DBI and coauthor of Programming the Perl DBI (O'Reilly).

Many of Orac's central scripts were based largely upon those packaged up by Brian Lomasky in his book, Oracle Scripts, for which he graciously gave permission for adaptation and use within the Orac program. As the program grew, Guy Harrison, author of the excellent Oracle SQL: High Performance Tuning (Prentice Hall), also allowed his very fine tuning scripts to be adapted for use within Orac.

Since Orac was first released on CPAN, many other Oracle DBAs have contributed additional useful scripts. Orac has become a real community effort.

You can download the Orac tarball from here:

http://www.cpan.org/authors/id/A/AN/ANDYDUNC

Section 3.4.1.1: Installing Orac on Unix

Once Orac has been downloaded, unpack it, and set the environment. Once this is completed, we can then simply run it with a single command:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The DDL::Oracle back-end module developed by Richard Sutherland was initially designed to reverse-engineer Oracle DDL (Data Definition Language) from Oracle8i databases, although its functionality is expanding and the module now offers other additional features for Oracle DBAs. It currently resides on a SourceForge web site, but you can still get the latest tarball from the Perl CPAN site:

http://sourceforge.net/projects/ddl-oracle
http://www.cpan.org/authors/id/R/RV/RVSUTHERL

The DDL::Oracle object-oriented module is designed for use by other scripts (such as Orac or debug.pl, as we describe later), rather than as a standalone program. The SourceForge site also provides many of the facilities you'll find useful if you start using DDL::Oracle in a serious way with your own scripts (as we hope you will), including a mailing list:

ddl-oracle-users@lists.sourceforge.net

If you download DDL::Oracle directly, you can install it with the following steps:

$ gzip -d DDL-Oracle-1.10.tar.gz
$ tar xvf DDL-Oracle-1.10.tar
$ cd DDL-Oracle-1.10
$ vi README

The installation of DDL::Oracle follows the usual Perl pattern:

$ perl Makefile.PL
$ make
$ make test
$ make install

Once DDL::Oracle is installed, you can view its documentation from within the installation directory (see Appendix A, for much more information about the perldoc program):

$ perldoc DDL::Oracle

Section 3.5.1.1: Using DDL::Oracle with Orac

You can use DDL::Oracle in many different ways, though mainly through other programs that make use of its facilities. The

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

DDL::Oracle is a very helpful resource, and many of the new Oracle tools coming off the open source Perl conveyer belt from SourceForge.net and FreshMeat.net are based upon it. In this section we'll take a look at one of these tools, Alistair Orchard's SchemaDiff program, which you can use to compare different Oracle schemas.

You can obtain SchemaDiff from:

http://sourceforge.net/projects/schemadiff

We downloaded SchemaDiff-2.3.0.zip and ran it on Win32, after having expanded it into the C:\SchemaDiff directory. (It works equally well on Unix.)

We already had the SCOTT user set up on the ORCL database. We decided to set up the IRISH user on the MYDB database with exactly the same structure. Once we'd done this, we ran the following SQL while logged on as IRISH:

DROP TABLE EMP;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        SSN VARCHAR2(50),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7,2),
        COMM NUMBER(7,2),
        DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
DROP TABLE EMP2;
CREATE TABLE EMP2
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP2 PRIMARY KEY,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7,2),
        COMM NUMBER(7,2),
        DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO2 REFERENCES DEPT);

We wanted to check to see if SchemaDiff would notice that IRISH has the SSN (Social Security number) column added to the standard EMP table, and see if it would also spot the extra EMP2 table.

Let's see how SchemaDiff behaves with the database and user described in the previous section.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Another helpful DDL::Oracle-based product is Martin Drautzburg's Senora, an alternative to Oracle's own SQL*Plus.

You can get hold of Senora here:

http://sourceforge.net/projects/senora

We downloaded the senora-0.4.tgz tarball:

$ gzip -d senora-0.3.tgz
$ tar xvf senora-0.3.tar
$ cd senora
$ vi README.txt

To access Senora, type in something similar to this statement:

$ perl Senora.pm scott/tiger@orcl

You will now see a doppelganger screen that looks amazingly similar to something you may have seen somewhere before:

SEN*Ora: Release 0.4.0.0.4 - Production on Mon Dec 31 21:53:26 CET 2001
  
(c) Copyright 2001 Miracle Exploration.  No rights reserved.
  
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
...
  
0:scott@orcl>

The output in the previous section looks remarkably like SQL*Plus? Why, in the name of the two Larrys, would we bother changing to something new when SQL*Plus comes with all versions of Oracle (and is likely to be included at least until Kurt Vonnegut's Ice-9 has been invented, the whole world has become an icy lake and even the Oracle database has gone open source)? Well, Senora author Martin Drautzburg does put forward some arguments for consideration:

Extensibility: Like most of us, you may love SQL*Plus to bits, but even those devoted to SQL*Plus have to admit that it lacks extensibility. You have to get hold of tools like TOAD or SQL*Navigator to do anything beyond basic SQL*Plus — and even these tools are impossible to extend. If you don't want to pay for a commercial product, you just have to hope and pray that the noncommercial version of TOAD you download every month will now provide the features you need. And you may not yet be in a position to write your own tools with Perl/Tk. Senora fills the gap by giving you much of the browsing and analyzing capabilities of these tools without the need to acquire any other tool or hand over your credit card number.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If you're an Oracle DBA who needs to visualize and report upon lots of complex information, particularly performance statistics, in graphical form, you will benefit from the amazing DBD::Chart. Just two of its many possibilities are displayed in Figure 3-13.

Figure 3-13: Two examples of what DBD::Chart can do

DBD::Chart provides a mechanism within Perl for rendering pie charts, bar charts, line, point, area, and candlestick graphs, and HTML image maps via the use of SQL. The neat thing about DBD::Chart is that it uses Perl DBI methods to create charts directly, rather than requiring you to invoke yet another programming interface. For example, a SELECT statement is used to output a particular chart type, and the WHERE clause is used to determine its dimensions.

If you tried to produce a chart without DBD::Chart, you'd have to select database row information into Perl arrays and then process the arrays separately to create the charts via a special Perl charting API. With DBD::Chart, you can do all this in one operation that is very SQL-like. For example, when you create a new chart, you do it with a CREATE statement just as if you're creating a table. When you insert information into the chart, you do this with an INSERT statement, as if you're adding a row to a table. This is a very neat idea. We particularly like it because virtually all of the dynamic charts we ever create come directly from databases.

DBD::Chart is particularly useful with either Perl/Tk or Perl CGI, when run in conjunction with Perl DBI. (Image maps can also be linked to CGI programs, with HTML usage.) You can see from the following breakdown of the code used to generate the two images in Figure 3-13 just how close DBD::Chart is to ordinary DBI. (See Appendix B, for a summary of the DBI API.)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SchemaView-Plus is another helpful Perl/Tk tool for Oracle DBAs that allows you to examine different database schemas and save them for future reference. It also provides insight into the world of Perl and XML data parsing. SchemaView-Plus was written by Milan Sorm, who also developed the dbMan application (see http://dbman.linux.cz), also written in Perl/Tk. (The SchemaView-Plus tool also uses the Perl DBIx extension, DBIx::SystemCatalog.)

In addition to downloading SchemaView-Plus itself, you'll need to obtain several additional modules. Table 3-5 lists the locations for the software you'll need to install.

Table 3-5: Description and downloads for SchemaView-Plus
C library/Perl module	Description/download address
`expat`	XML parser C library `http://sourceforge.net/projects/expat`
XML::Parser	Parses XML `http://www.cpan.org/authors/id/C/CO/COOPERCL`
XML::Dumper

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Even with all of the wonderful applications profiled in this book, you might still find that none are quite right for your own database administration needs. If you have a serious itch you really need to scratch, you might eventually decide that you need to do some coding of your own. Fortunately, this is becoming easier all the time.

Most Perl devotees are still wedded firmly to the command line and to the use of text editors like vi or emacs for development. However, for those accustomed to the typical Win32 code development style, Open Perl IDE offers an excellent development alternative. Open Perl IDE is an integrated development environment (IDE) for writing and debugging Perl scripts with any standard Perl distribution under Win32. This open source software is written in Delphi 5 Object Pascal and Perl. In addition to providing a complete development environment, it also offers excellent Perl code debugging facilities.

To install Open Perl IDE, follow these steps:

Go to:

http://sourceforge.net/projects/open-perl-ide

We downloaded the following file to its own newly created directory:
```
C:\OpenPerlIDE\Open_Perl_IDE_0.9.8.168a.zip
```
Double-click on this and check the readme.txt file.
Unpack to the same directory.
Double-click on the PerlIDE.exe program. The IDE should now be up and running, as shown in Figure 3-16.

Figure 3-16: Open Perl IDE in action with Perl DBI

Other Perl IDEs you might want to consider include:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

A number of excellent Perl GUI debuggers provide graphical interfaces for diagnosing problems in your Perl programs. The quickest way to get up to speed on the general topic of debugging in Perl is to type the following commands:

$ perldoc perldebug
$ perldoc perldiag

Perlish people tend to use command-line debug programs with major sprinklings of print statements. But several open source GUI debugger programs are out there that you might want to consider. We've listed the best-known in Table 3-6.

Table 3-6: Open source Perl GUI debugging programs
GUI	Description
`perldbgui`	A GUI for the standard Perl debugger: `http://members.tripod.com/~CurtMcKelvey/perldbgui`
`ptkdb`	This can work with Apache Perl scripts and is shown in Figure 3-17: `http://www.cpan.org/authors/id/A/AE/AEPAGE`
Open Perl IDE	This IDE, described in the previous section, can help write and debug Perl programs: `http://sourceforge.net/projects/open-perl-ide`

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter describes the basics of web-based Oracle applications whose output or interface is viewed through a web browser, rather than through the graphical user interfaces (GUIs) offered by the applications described in Chapter 3. The advantage of employing a web solution is that you need to deploy it only once, at one web address, and thereby provide a tool that anyone with a web browser can access from anywhere on the network. Using the web as an interface can make life a lot easier for Oracle database administrators who may manage dozens of databases and who need to maintain a satellite's eye view of their operation and performance. DBAs have long dreamed of being able to monitor their databases from the beach via remote-control applications that require nothing more than browser control from a Palm pilot or some other type of PDA, a cellular phone, or a laptop. That particular scenario is likely to remain a dream, but it's certainly true that use of the Web adds a convenient dimension to database administration.

Many web-based database applications are implemented as Perl CGI scripts. CGI (Common Gateway Interface) programs are typically small programs (running on the web server) that have historically been used to provide dynamic content to web pages. The output of a CGI program is simply an HTML page that is read by a web browser. How do CGIs work? On the client side, the browser calls a CGI in the same way that it would call a static web page — by making a request for a file from the web server. By calling a CGI, though, the client is actually telling the server to run a small program. In the case of an Oracle CGI script, running that program pulls data from the Oracle database and thus produces the dynamic content for the web page. We won't attempt to describe the details of CGI in this book. If you are interested in learning more, we recommend the following classic text by Lincoln Stein, the creator of the CGI.pm module:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Apache is an open source web server — and the most popular web server in use today (including both open source and commercial web servers). Apache is fast, efficient, easy to configure, and very stable on the widest variety of platforms. Apache runs on virtually every operating system, including Win32, Linux, BSD, Solaris, and many other varieties of Unix. The main web site for Apache is:

http://www.apache.org/

Apache and Perl are fast friends. This chapter focuses on Perl-based applications for Oracle that are implemented as CGI scripts or daemons. In Chapter 5, we'll discuss Apache's mod_perl module, which makes the Apache/Perl connection a more efficient one.

To obtain the downloads and information concerning Apache for Unix, Win32, and Oracle's use of Apache, check out the following URLs:

http://www.apache.org/: Main Apache umbrella web site for all the Apache Software Foundation (ASF) related projects.
http://httpd.apache.org/: Central site for the actual Apache web server, the ASF's core offering.
http://httpd.apache.org/dist/httpd/: Main download page for Apache on Unix.
http://httpd.apache.org/docs/window

Table of Contents

Section 1.4.1.1: Perl web sites

Section 1.4.1.2: Perl mailing lists

Section 2.2.1.1: The traditional method

Section 3.4.1.1: Installing Orac on Unix

Section 3.5.1.1: Using DDL::Oracle with Orac