O'Reilly Media | Oracle and Open Source

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

The combination of Oracle Corporation and open source software may appear to be an unlikely pairing. What could Oracle, with its history of ruthless competition, intense marketing, and cutthroat corporate life, have to do with the collaborative, altruistic, and apparently anti-corporate world of open source?

The answer, surprisingly, is quite a lot. In recent years, the gospel of the open source movement has spread far and wide, reaching even the corporate corridors and product lines of organizations like Oracle Corporation. Consider the following recent developments:

Oracle8i has been officially ported to the freely available Linux operating system.
The open source Apache web server is now distributed as part of the Oracle Internet Application Server (iAS).
The open source Perl, Tcl, and Python scripting languages all provide modules supporting connections to Oracle databases.
The Oratcl application, an open source program built on the Tcl scripting language, is now distributed as part of the Oracle Enterprise Manager (OEM) product.
Dozens of excellent applications written by open source developers—Orac, Oddis, Karma, Oracletool, OraSnap, Big Brother, jDBA, GNOME-db, and many more—give Oracle database administrators and developers new tools for managing their databases and building new applications. And if one of these tools doesn't do exactly what's needed in a specific environment, the source code can be modified without restriction.

In our opinion, this new synergy between the corporate world of Oracle and the freewheeling world of open source is a great thing—the blended products we're starting to see truly do represent the best of both worlds.

The purpose of this book is to share what we know about this blending of Oracle and open source. It's a new world, and one that hasn't been examined much to date. Although many books have been written about some of the base open source technologies we'll be exploring in this book (for example, Perl, Tcl, and Apache), there has been very little written about the way these technologies are used with Oracle databases, and even less about most of the Oracle open source applications available to DBAs and developers. Although we can't possibly describe every open source technology in depth within the confines of this single volume, we'll try to provide a foundation. Our overriding goal is to shed light on as many of the open source technologies and applications as possible and on how they communicate with Oracle databases. In trying to achieve this goal, we'll weave together three distinct threads:

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

Before we get into the details of how you can use and build Oracle open source software, let's take some time exploring what open source is and why you might want to use it.

The quick and dirty definition of "open source" is that it is software that's freely available: you can acquire it freely (it's usually downloaded from the Internet at no cost), and you can modify it freely (the source code is provided, not just the executable files). However, it's important that we refine this quick use of the word "free." There are important semantic differences between what the Free Software Foundation (FSF) defines as "free" and what the newer Open Source Initiative defines as "free." We'll explore both movements and their respective definitions later in this chapter. For starters, we'll just note the functional definition of "open source" from the Open Source Initiative web site, at http://www.opensource.org:

Open source promotes software reliability and quality by supporting independent peer review and rapid evolution of source code. To be OSI certified, the software must be distributed under a license that guarantees the right to read, redistribute, modify, and use the software freely.

The OSI web site continues with a description of why open source is of such high quality:

The basic idea behind open source is very simple. When programmers on the Internet can read, redistribute, and modify the source for a piece of software, it evolves. People improve it, people adapt it, people fix bugs. And this can happen at a speed that, if one is used to the slow pace of conventional software development, seems astonishing. We in the open-source community have learned that this rapid evolutionary process produces better software than the traditional closed model, in which only a very few programmers can see source and everybody else must blindly use an opaque block of bits.

A little later in this section, we'll discuss the open source license and the details of what it means to be OSI certified. For now, let's ask a few more questions.

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

Table 1-2 lists all of the open source and other programs mentioned in this book. We tried to include the most up-to-date information possible 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-2: Open Source Technologies and Related Areas
Tool or Application	Chapter	Description and Online Sites
ACS	Chapter 5	Ars Digita Community System, dynamic web-based solutions `http://www.arsdigita.com/pages/toolkit/`
ActivePerl	Chapter 2	Win32 Perl from ActiveState `http://www.activestate.com` `http://www.activestate.com/activeperl/` `http://www.activestate.com/Products/ActivePerl/Download.html` `http://www.microsoft.com/downloads/`
AIX

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 section contains the full text of the Open Source Definition we described earlier in this chapter. You will find the text, with any updates, at:

http://www.opensource.org/docs/definition_plain.html

On the Web, you may encounter other examples of "open source" licenses, particularly in situations where corporations are distributing the source code for their applications (while still retaining strict proprietary control over it). You will generally recognize these situations because at some point you'll be asked to accept some kind of disclaimer before they allow you to continue a download. Just remember that the definition found at http://www.opensource.org/osd.html is the one "true" definition with which any other license provisions must comply. Accept no substitute—especially if you're thinking of helping to develop an open source project of your own.

Here is the text of the Open Source Definition, in italics, with an explanation following each section:

Free Redistribution

The license may not restrict any party from selling or giving away the software as a component of an aggregate software distribution containing programs from several different sources. The license may not require a royalty or other fee for such sale.

You can make as many copies of a program as you like to keep, sell, or give away. You don't have to pay anyone for this privilege.
Source Code

The program must include source code, and must allow distribution in source code as well as compiled form. Where some form of a product is not distributed with source code, there must be a well-publicized means of obtaining the source code for no more than a reasonable reproduction cost—preferably, downloading via the Internet without charge. The source code must be the preferred form in which a programmer would modify the program. Deliberately obfuscated source code is not allowed. Intermediate forms such as the output of a preprocessor or translator are not allowed.

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 Oracle Call Interface (OCI) is the Oracle software that allows the outside world access to the hidden core of the Oracle database. An open source application—or any other type of non-Oracle program—can use OCI to connect directly to Oracle via its internal SQL engine. OCI is a complex product, and this short chapter can't do justice to all of its capabilities. We'll cover only the fundamentals here, examining the basic Application Programming Interface (API) of OCI and how it is typically used with open source software. We'll introduce OCI and its main functions, and we'll explain how it relates to Open DataBase Connectivity (ODBC) and Java DataBase Connectivity (JDBC).

Although you can access OCI directly, most developers prefer a simpler and more convenient interface. As an example of how open source applications use such interfaces to communicate with OCI, we'll take a close look at how Perl applications use the Perl Database Interface (DBI) module (and its Oracle-specific driver, DBD::Oracle) to connect to Oracle databases. We've chosen Perl here because it was one of the first open source languages to communicate directly with Oracle; the interface dates from 1990, with Kevin Stock's original work on Oraperl. We'll describe other interfaces in their respective chapters (for example, Tcl with its Oratcl interface, and Python with its DBOracle interface, in Chapter 3). Throughout this book, we'll mention, as appropriate, how various open source tools make use of OCI.

The Oracle Call Interface is the comprehensive API that is used to connect internally to the Oracle database server. OCI is based on C and provides all the requirements you might need to support your Oracle-based applications, including the following:

High performance
Security features, including user authentication
Scalability

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 Oracle Call Interface is the comprehensive API that is used to connect internally to the Oracle database server. OCI is based on C and provides all the requirements you might need to support your Oracle-based applications, including the following:

High performance
Security features, including user authentication
Scalability
Full and dynamic access to Oracle8i objects
User session handles, dynamic connections, and session management
Multi-threaded capabilities
Support for accessing special Oracle8 datatypes, such as large objects (LOB), BFILE, and LONG
Transactions
Full character set support

At the most basic level, virtually all outside programs, from web applications to standalone GUI applications, interact with Oracle through this program layer. (The one major exception is the JDBC client-side driver, which we'll discuss shortly.)

Fortunately, the OCI libraries are automatically available in every Oracle database installation; there is no special installation process. You'll generally discover the appropriate files under the $ORACLE_HOME/lib and $ORACLE_HOME/include directories. Most open source applications have therefore accepted Oracle's open invitation to the database, and they use OCI to gain their front-door entry into the world of Oracle programming.

As Figure 2-1 indicates, OCI acts as the primary port of destination for every connection to and from the server. To simplify OCI's sometimes complex operations, other database APIs can also be wrapped around the OCI. Examples of such wrap-around APIs include the popular ODBC, the Java-based JDBC (at least partially, as we'll explain), and the Perl DBI. These three APIs are described 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

Perl is perhaps the most popular of all open source tools, and the earliest to appear of the major open scripting languages. It is also used frequently to write database applications. We'll explore the language in more depth in Chapter 3, where we also discuss other scripting languages. For now, we'll focus on how Perl is used to connect to Oracle databases via its Perl Database Interface (DBI) module. (Perl DBI provides a generic interface to a variety of databases, including Oracle.)

Perl has become an important tool for Oracle DBAs and developers because it 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 day generating a dynamic web application. Today's distributed corporate databases, so vital for the success of any business, have become almost too complex for their own survival, and many DBAs and developers have found that Perl is one of the best ways to tackle this complexity. With a script here and a script there, Perl can turn a potential data implosion into a real-world information explosion. Perl's magical ability is all the more magical because it's also freely available.

In the following sections we'll describe how you can obtain and install Perl on Unix, Linux, and Win32 systems. We'll also describe how the Perl DBI and DBD::Oracle modules let Perl programs access the Oracle database, and we'll look at some examples of simple Perl programs that interact with Oracle.

These are the main web sites for Perl:

http://www.perl.com
http://www.perl.org: The main Perl portals

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 the first two chapters of this book, we set the scene for using and developing Oracle open source applications. In the remaining chapters, we'll look at the open source technologies that are best suited for developing the kinds of applications needed by Oracle database administrators and developers. The first port of call in our journey through the software cosmos of Oracle and open source is the world of scripted GUI solutions. In this chapter, we'll examine the three major open source scripting languages: Tcl, Perl, and Python. All three languages have been around for more than a decade, tracing their command-line origins back to the late 1980s. All three provide excellent functionality, are relatively easy to learn, run efficiently, have enthusiastic and active developer communities, and offer solid interfaces to the Oracle database. We'll describe the languages very briefly, discuss how to obtain and install them, and explore ports and interfaces available to Oracle with these languages:

Tcl/Tk

Tcl is an excellent and highly extensible scripted language developed by John Ousterhout back in 1987. In 1988, Ousterhout also started working on a GUI toolkit for Tcl that he called Tk. Over the years, Tcl/Tk has become the favorite of many engineers, software architects, and academics. Tcl/Tk provides an excellent approach to developing applications, including those based on Oracle. Tcl allows developers to create applications that run advanced control systems and C backend libraries with minimal amounts of scripted code, while controlling these applications via GUI applications (rapidly prototyped using Tk) on the frontend. Tcl/Tk becomes even more powerful when these GUI frontends are attached to Oracle backends, as we'll see later in the chapter. We'll also explain how to obtain and install Tcl and Tk, and we'll look at the following programs for Tcl:

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

Before we plunge into the details of how Tcl/Tk, Perl/Tk, and Python/Tkinter connect to the Oracle database, let's take a step back and examine why we might choose a scripted GUI solution in the first place, as opposed to choosing the web, Java, and Linux solutions we'll cover in later chapters.

Anyone who's struggled to write even a simple interactive, forms-based program using the C curses libraries or the Hungarian notation of the Windows 3.1 API knows that even the most basic table maintenance program can require thousands of lines of frustrating code. Once you've written a few such programs, however, you quickly realize you're doing much the same thing on each occasion, painting virtually identical command buttons and text panels onto standardized program frames, with the programs themselves often driven by predictably structured menu loops. To take advantage of this repetition, standardized libraries of reusable GUI code (widgets or controls) have evolved in order to make such GUI programs far easier to code.

In what the evolutionist Stephen J. Gould would call an example of preadaptation, scripted languages became virtually ideal vehicles for creating GUIs out of these widget code libraries. Each line of a VHLS (Very High Level Script) language can represent many hundreds of lines of C widget code. Therefore, the potential is there to turn a thousand-line C program into a functionally identical scripted program of just ten lines (or even less, if you're a fan of obfuscation). By decreasing code length in this way, you'll save, by orders of magnitude, both time and the need to support lengthy programs.

There is a downside to VHLS languages, especially when they are used with interpretation rather than compilation, and that is their lack of speed. However, with GUIs operating in the colored world of human time and space, rather than the black-and-white nanosecond existence of a batch processor program, speed issues become pretty much irrelevant. Even hundreds of commands can be attached to a single button or movement on a VHLS application, and the high CPU power of modern workstations will smooth away its hidden wrinkles.

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

Only you can decide which of the three scripted solutions is best for your particular application, platform, and personal preference in programming. You can be assured that all three of them will provide you with an attractive GUI toolkit. What's also crucial for this book is that they also provide an excellent interface to the Oracle database, as we describe next.

Tcl/Tk and Oratcl: This is the best established and most widely known scripting combination for use with Oracle. Tcl currently has one big nontechnical advantage as a scripting solution for Oracle applications, and that is its corporate acceptance. John Ousterhout's Tcl development work with Sun Microsystems between 1994 and 1998, in combination with Oracle Corporation's adoption of the Oratcl interface program in its own Oracle Enterprise Manager product, gives Tcl a certain blue-chip respectability. Tcl has also won acceptance in the commercial world because of the presence of its Tcl/Tk web plug-ins that are ready to run out of the box, as well as a number of other useful add-ons.

Perl/Tk, Perl DBI, and DBD::Oracle: Perl/Tk, in conjunction with its database interface module, Perl DBI, provides you with everything you need to create effective GUI solutions for Oracle-based applications. Perl DBI cleanly wraps up OCI via its DBD::Oracle driver module, and Perl/Tk wraps up the Tk GUI widgets. Some major advantages for Perl as a scripting solution for Oracle applications are the rapidly expanding number of Perl/Tk widgets available for download (they're easily created because of Perl 5's object orientation) and the growing army of Perl DBI programmers out there. These battalions of enthusiasts are continually pushing back the functionality frontiers, particularly Perl CGI programmers using Apache Perl modules who need DBI to be right up on the instantiated jagged edge!

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'll start our journey through the major scripting languages with Tcl (Tool Command Language, pronounced "tickle") and its companion graphics toolkit, Tk (prounounced "tee-kay"). Tcl/Tk grew from a sense of frustration. Back in 1987, Dr. John Ousterhout and his students at the University of California Berkeley were spending way too much time writing specific and widely differing command tools for their projects, rather than focusing their time directly on the goals of these projects. Ousterhout realized that a lot of this precious time could be saved if a robust, general-purpose, extensible scripted language could be implemented as a C library. That library could then be reused for all of the team's subsequent projects. Tcl was thus conceived; Ousterhout mostly wrote the new language in his spare time, and the first version was born in the spring of 1988. By 1989 Tcl was being released to early adopters, and after a USENIX conference in January 1990, the source code was placed on Berkeley's public FTP site and Tcl was quickly adopted by many engineers, software architects, and academics.

When Apple released their superb GUI HyperCard system with great fanfare in 1987, Ousterhout realized that such a massive and monolithic development effort could not be matched by his students. However, he didn't want them locked out of the future of interactive software. Instead (in what was a forerunner to Eric Raymond's "bazaar" methodology), Ousterhout decided to gradually build reusable components that could plug directly into the rapidly evolving X11 Windows toolkit, which was itself a major innovation for Unix systems. He started work on Tk in late 1988 and framed it within the highly adaptable Tcl script control language. Within two years, Tk possessed enough functionality to be truly useful. Ousterhout had created a masterpiece in Tcl/Tk.

When Tk itself was released and made freely available via FTP, an explosion of development ensued. Many talented people created their own modules, which linked into Tcl/Tk to extend its basic core set of functionality.

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

As we discussed in Chapter 2, Perl was created by Larry Wall back in 1987. It is an extremely fast, interpreted scripting language that combines some of the best features of a variety of other languages, Perl is especially good at expression handling, scanning and manipulating text, and performing system management tasks.

Although some programmers prefer Perl and some prefer Tcl, everyone loves Tk. As we mentioned earlier, Tk was originally available only for Tcl, but in 1995, Nick Ing-Simmons (following up from Malcolm Beattie's initial work at Oxford University) removed Tk's original intrinsic need for Tcl and produced a much more portable set of code. He made this code directly acessible to many more languages, and it became known as pTk (portable Tk). As an encore, he wrapped this ported code in Perl, creating the Tk.pm Perl module. Thus, another open source star—Perl/Tk—was born.

Perl/Tk is an excellent scripted alternative to Tcl/Tk. If you've decided that the Perl DBI application interface is a good bet for quickly prototyping useful Oracle database tools and applications, then you'll find that one of the best ways of wrapping them up is within a Perl/Tk GUI program. Aided and abetted by a growing army of evangelists (including the indefatigable Stephen Lidie), Perl/Tk has flowered into an amazing collection of widgets, fulfilling virtually every widget collector's desire. Ing-Simmons' development of the Tk800 port (encompassing Win32's look-and-feel), combined with the robust growth of ActivePerl, means that Perl/Tk is now available on Solaris, Linux, and virtually every other flavor of Unix and Windows. Of course, the major benefit is that we can now use all of our Perl DBI/DBD::Oracle functionality within a superb GUI toolkit across a wide range of platforms.

These are the main web sites for Perl itself:

http://www.perl.com

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

Just when you thought it was safe to put this book down, download Perl or Tcl, and get going writing Tk applications for Oracle, there's yet another open source GUI scripted language you have to consider—Python. When used with the DCOracle database access library extension, Python (combined with its Tkinter windowing system) provides another excellent scripted solution for working with Oracle.

Figure 3-10: The Perlplus Netscape Perl plug-in at work

Python grew out of Guido van Rossum's work with the Amoeba distributed operating system and his need to create a more useful tool for Amoeba system administration. He first began working on Python in late 1989 and by 1991 was ready to post it to the Internet. Python grew out of the earlier ABC language and a blend of other design ideas—notably, those based around Modula-3. Van Rossum wanted to create a language that was portable and easy to learn and had a powerful, standard library and the capacity for extension and operating system independence.

People often assume that the Python language is named after the famous snake; however, it is in fact named after one of England's finest exports, "Monty Python's Flying Circus." Python now has a large and independent clan of enthusiastic supporters, and it is Eric Raymond's choice as the best first language for budding hackers. Python is similar in programming scope to both Perl and Tcl (that is to say, it is without bounds). Python is a fully object-oriented language, which offers as many facilities and modules as are available for Perl and Tcl. The language can be used for whatever programming purpose you can think of (GUIs, XML, email, you name it). Python has now been successfully ported to most other operating systems, including virtually all flavors of Unix, Linux, Win32, and Macintosh.

The following are the main web sites for Python:

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 previous chapter introduced the use of the most popular open source scripting languages—Tcl, Perl, and Python, along with their GUI toolkits—and the Oracle interfaces built upon them. In this chapter, we'll take a detailed look at two particular Oracle applications, one from the Perl camp and the other from the Tcl camp:

Orac: A Perl/Tk GUI tool designed mainly for database and system administrators; it performs database management and performance tuning and also makes use of the Perlplus Netscape plug-in.

Oddis: A Tcl/Tk GUI tool conceptually similar to Orac and designed for both DBAs and developers; it performs database management and performance tuning, with a special focus on SQL tuning.

As yet, no major Python Tkinter Oracle application making use of DCOracle is generally available. We're hoping one of our faithful readers will remedy this situation in the near future.

In addition to describing what Orac and Oddis do (and briefly mentioning a few related tools, such as dbMan), we'll also spend some time looking at the implementations of these two Oracle applications. We're hoping that looking at these implementations might give you some good ideas for how to approach building your own applications. Although these relatively large applications were developed entirely independently from each other, it's interesting to see how their functionality overlaps.

In the second part of this chapter, we've also provided a small but fully worked-out example designed to show you the way that existing tools can be extended. We'll take two existing base tools and then blend them together in a hundred or so lines of code to provide a new example application called TableSpacer. The purpose of TableSpacer is to graphically display Oracle table space usage. It is based on the following tools:

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 built upon the base of Perl/Tk and its many widgets, and it employs Perl DBI to connect to the Oracle database. In this section, we'll describe how the Orac program makes use of many of the Perl/Tk widgets.

The Orac program was originally developed to provide a way of keeping a useful collection of Oracle DBA scripts wrapped up together in one central place. It is basically a GUI wrapper containing a large repository of prepared, configurable SQL scripts for interrogating and managing 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 first came about because Andy was working on a two-man team looking after 25 or so revenue-critical production databases and about 15 development databases at a large corporate data center. Although not particularly massive, the databases were constantly being upgraded by large teams of developers and being hit by as many as 500 sales representatives at a time. Something was required to ease the constant workflow generated by this activity. Orac became that something, starting out as a Perl/Tk GUI-wrapped script to kill spinning processes and gradually growing into a complete DBA toolbox with some system administration aids thrown in for good measure. Although Orac was originally aimed directly at DBAs (and partially at system administrators), as the program developed, it also acquired a number of Oracle development aids.

Orac's central SQL driving scripts were based largely upon those developed by others. Many of these ubiquitous scripts have floated around for years, and we can't easily attribute them. Some, though, are based on Brian Lomasky's superb collection (packaged up in his book,

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

Oddis (Oracle Data Dictionary Information System) is a Tcl/Tk-based graphical user interface for navigating visually throughout the entire Oracle data dictionary (ODD). Oddis follows in the footsteps of Tom Poindexter's Wosql and IUD Maker programs (covered in Chapter 3), but it is a much more comprehensive Tcl/Tk Oracle application, and it provides an excellent example for aspiring Oracle open source developers. The program is based on original work by Rainer Gruetzner and is currently maintained by a team at the University of Hanover in Germany, including Michael Bethke, Axel Schlüter, and Regine Kasten, who have moved the program forward mostly via their computer science projects.

Intended primarily for DBAs, both novice and expert, Oddis aims to make the data dictionary as simple as possible to traverse, while providing the maximum amount of useful information. In addition to providing tools for Oracle DBAs, Oddis also possesses excellent facilities for storing and running SQL files as well as superb EXPLAIN PLAN functionality (including diagrammatic trees), making it potentially useful for developers too.

Oddis is far more than the data dictionary viewer it modestly claims to be. It is also a fully interactive OLTP tool, as well as a general reporting engine. It offers complete facilities for transactions, commits, rollbacks, auto-commits, and so on, in the main interface, all available via its various menus. The program provides an excellent demonstration of what's possible using TclX and Oratcl, and it offers substantial room for expansion in the future. We can't wait to see Oddis 3.0 and any subsequent developments. (Oddis 3.0 is rumored to be nearly ready for release and may even provide some kind of linkup with Java. Stay tuned.)

The large number of Tcl/Tk web plug-ins and other developments from Sun, as well as Oracle Corporation's own efforts to push Oratcl (which we described in Chapter 3) make the future for Tcl/Tk Oracle applications such as Oddis very bright indeed.

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

As promised, before we leave the world of Tk, we'll create our own small application for measuring Oracle table space usage. Building this tool, which we'll call TableSpacer, will extend our use of Tk beyond using downloaded applications as is. This example builds on two excellent Tcl/Tk products: George Howlett's BLT, described in this section, and Tom Poindexter's Oratcl, already described in Chapter 3.

BLT is an extension to Tcl/Tk that adds combinations of plotting widgets (graphs, barcharts, etc.) to Tk canvases, saving you hundreds—possibly thousands—of lines of code when creating a typical on-the-fly graph within a Tk canvas object. (Not only is BLT one of the best chart-making packages around, but also the letters BLT can apparently stand for anything you want them to.)

The following are the main web sites for BLT:

http://www.tcltk.com/blt: General information on the BLT package, upgrades, latest news, and so on.

ftp://ftp.tcltk.com/pub/blt/: Central download site for the latest BLT code releases and self-installing pre-compiled Windows executable files.

ftp://tcltk.sourceforge.net/pub/tcltk/blt/: SourceForge site for all things Tcl/Tk. In this directory you will find two very useful PDF tutorial files (it's the same tutorial in different layouts), handouts.pdf and slides.pdf.

BLT is also described in Paul Raines and Jeff Tranter's Tcl/Tk in a Nutshell, referenced in Appendix C.

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

Web technology powers the Internet, and databases provide the underlying content and the ability to access, display, and manipulate that content. Although the fundamental relationship between databases and the Web is straightforward, building a web site that has an Oracle backend database is no simple matter. There are a great many interactions between a variety of different programming languages, protocols, and components. This chapter tries to demystify these interactions by examining the main technologies used to power the Web, paying particular attention to how these technologies relate to Oracle. We'll discuss the various layers of magic operating between the web server and the database, and we'll touch on the advantages and disadvantages of each approach to building your site. We'll take a little extra time on these basics because they are so crucial to building modern, web-based applications.

Once we've given you a feel for some basic web technologies and concepts, we'll introduce the various open source implementations on which most of the Oracle applications described in this book are based. Here's a brief overview:

Apache: The web server is the heart of your web application. In this chapter, we'll describe Apache, the most popular web server in use today. Apache is open source, but even large corporations like Oracle are now using it.

Perl and the Web: Your web applications themselves get served via the web server. The programming can be done in various languages, but Perl is a common choice. The Perl Database Interface module (DBI), discussed in some detail in Chapter 2, and Chapter 3, provides an interface for accessing data stored in Oracle (or other) databases. In this chapter, we'll also discuss mod_perl, an Apache module that can speed up your Perl CGI (Common Gateway Interface) scripts dramatically by incorporating the Perl interpreter directly into Apache.

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

One of the most exciting developments for databases today is the growing proliferation of backend datastores that drive the Internet's web sites and, indirectly, the world's economy. Everywhere you dig in the fertile ground of the Internet you'll find a hidden database hiding just beneath the surface, from news sites such as cnn.com or nytimes.com to information archival sites such as edgar-online.com. And let's not forget the juggernaut e-commerce sites, such as amazon.com, with their trailer loads of product information. Online trading, financial news, search engines, airlines, auction sites, and portals—all of them utilize a database on the back end. Luckily for Oracle developers, that database is very often one emanating from Redwood Shores, California.

An interesting aspect of the backend databases is that, when they access a web site, most end users aren't even aware that they're interacting with a database server. Backend complexity is almost always hidden behind the scenes. Indeed, when it isn't, and the loose wiring becomes frayed and visible, commercial web sites often fail, sparking and fizz-popping into business oblivion. So for the outside viewer, how a web page knows who you are when it recommends the latest Stephen King novel to you is often a complete mystery (a feat of magic, especially if you are a Stephen King fan).

We're aiming to become privileged members of this magic circle, and we need to know what's going on under the hood. So in the rest of this chapter we'll take a look behind the scenes at the various web-based technologies used to build the Oracle applications described in Chapter 6, and those you'll need to understand in order to build your own applications. First, though, we'll describe the most important concepts that underlie the connection between the Web and the databases behind it: dynamic content, personalization, CGI programming, and caching.

Dynamic content is the programming that enables a web site to change from day to day or hour to hour, as well as between one user and another. These days, most sites are dynamic; there aren't many sites that present only static content—pages that are the same every time you visit them.

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 a hugely successful piece of open source software. It is the most popular web server in use today—including both open source and commercial web servers. Netcraft reports that over 60% of global Internet sites currently employ Apache.

Apache originally evolved from the HTTP daemon program (httpd ) developed by Rob McCool at the National Center for Supercomputing Applications (NCSA), University of Illinois, Urbana-Champaign. When McCool left NCSA in 1994, development of the httpd program faltered. A group of eight core programmers, headed up by Brian Behlendorf and Cliff Skolnick, decided to continue McCool's public domain work, forming the Apache Project (named after "patching," their standard method of code modification). Other developers in the original core Apache group included Roy T. Fielding, Rob Hartill, David Robinson, Randy Terbush, Robert S. Thau, and Andrew Wilson.

The Apache group has now become the Apache Software Foundation (ASF), whose purpose is to provide organizational and legal support for all of the various Apache software projects and to ensure that these projects continue even if individual volunteers leave.

The main web site for Apache is:

http://www.apache.org

Apache runs on virtually every operating system, including Win32, Linux, BSD, Solaris, and many other varieties of Unix. Apache's modular design allows the functionality of the basic Apache code to be extended through the use of its easily accessible API; that design greatly enhances Apache's power and flexibility. Among the important Apache modules are the following:

mod_perl (http://perl.apache.org): Provides an interface between Apache and Perl. It allows Perl code to be cached in the web server's memory space. This substantially improves performance over standard CGI applications. The use of

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 an excellent language for enabling web communication with an Oracle database. Perl DBI, which we describe in some detail in Chapter 2, is the interface you'll use for this communication. Chapter 3 contains more general information about installing and using Perl.

You will be reassured to know that connecting to the database via your Perl CGI scripts is much the same as connecting to the database via a standalone Perl program. You open a database handle and connect to the database, open a cursor or statement handle, specify a query, parse or prepare the statement, and then execute it. If the statement is a SELECT, you then fetch rows (perhaps the user's favorite books, as generated the previous night by your monster data warehouse). When you're all done spitting back the information, you close down your cursor and disconnect from the database.

Historically, there have been some performance problems with this approach, however. Whenever a Perl program is run, the rather sizeable Perl interpreter must first be brought into memory before it can interpret and execute your Perl program. Unfortunately, this can be Sssssslowwwwwww with a capital S. (Java servlet fans have pointed to this slowness of Perl in advocating that their technology be used instead of Perl—via ConnectionPool.class and PoolManager.class statically instantiated objects, as we describe later in this book).

Has Perl taken this challenge from the Java evangelists lying down? Certainly not. The mod_perl module, developed by Doug MacEachern and available at http://perl.apache.org, brings the Perl interpreter directly into the heart of the Apache kernel, thus avoiding the overhead of loading the interpreter into memory for each script executed on the server. As well as doing memory caching, this incredible module also allows you to extend the Apache server in the Perl language itself. In the following sections we'll describe how to install mod_perl and use it to connect to an Oracle database.

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

Java provides an alternative approach to Perl for enabling web communication with an Oracle database. In this section, we'll provide a rough outline of using Java for web applications. Refer to Chapter 7 and Chapter 8 for much more detail about Java and its use with Oracle.

Java has its own version of an embedded HTML language, aptly called JavaServer Pages (JSP), and its .jsp pages are analogous to Microsoft's .asp Active Server Pages. Using JSP, you add code to an HTML template page, rather than write a script that generates an HTML page, as is the case with Perl. The Java approach is similar to that of other embedded scripting languages such as PHP, which we'll describe later in this chapter. In addition to accessing pure Java, a web page can also access precompiled Java Beans, which facilitates component-based code and interoperability and allows you to encapsulate functionality outside the page.

JavaServer Pages, like the various embedded scripting technologies discussed later in the chapter, allow you to embed code in your HTML pages. The code is preprocessed, and a complete HTML page is then returned by the web server.

Java servlets are bits of code which run on the server machine and are invoked by the web server. Java servlets are an alternative to JSPs. They operate in much the same way that other CGI programs do. But to make Java servlets work, you need a special module—namely, JServ for Apache, which we'll describe in a moment.

We prefer using Java servlets for web work with Java, since JavaServer Pages can get messy pretty quickly when you need to embed an entire Java program within an HTML template. However, if you especially like Java, you might want to give JSP a try. Typical directives include the following:

Import an appropriate Java package:
```
<%@ page import="java.util.*" %>
```

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

So far in this chapter, we've looked at how you can build open source web-based applications with Perl and Java technologies that use CGI scripts. Another approach to dynamic web programming is to use embedded scripting. With the embedded scripting approach, you embed snippets of code in your HTML pages, rather than writing CGI programs that output HTML pages. This is an elegant way to separate web and database programming from web page design (though the separation works well only if the embedded language is a simple and straightforward one).

There are various web programming solutions loosely based on the idea of embedding code into HTML pages and then preprocessing it. With Java, for example, you use JavaServer Pages (JSP), as we mentioned in the previous section. Microsoft's version of this technology is known as Active Server Pages (ASP). There are also a number of excellent open source solutions. In this section we'll take a look at four exciting and widely used open source solutions: PHP, EmbPerl, Mason, and Aquarium.

PHP is a powerful embedded scripting language that supports an extensive set of Oracle functions. PHP differs from CGI scripting in a fundamental way. With CGI scripting, you return an HTML page that's generated directly as the result of running your script. PHP lets you generate dynamic web pages simply by embedding pieces of PHP code inside your HTML. PHP is similar to JavaServer Pages, Active Server Pages, and Cold Fusion technologies, but PHP is easier to use. With JSP, for example, you must have a fair amount of familiarity with the considerably more complex Java language.

PHP was conceived and developed by Rasmus Lerdorf, who started work on the project back in 1994. By 1995, PHP was known as Personal Home Page Tools and had grown rapidly into a large collection of scripts. It then became PHP/FI as an HTML Form Interpreter and other modules were added. Gradually, as the download rate of PHP across the Internet became exponential, the toolset became known as PHP: Hypertext Processor. The tool has now morphed from its rewritten PHP3 incarnation into PHP4 (driven by the Zend scripting engine,

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 world of web-based Oracle applications is bursting at the seams these days, with a well-rounded and growing list of tools ranging from snapshot-based performance monitoring tools and database administration gizmos to data browsing and remote online transaction processing (OLTP) systems.

What are web-based applications? We loosely define such applications as those whose output or interface is viewed through a web browser. Some of these applications are Perl-based CGI scripts; others are just SQL scripts whose output is HTML. Chapter 5, describes the fundamental technologies on which these applications are built. If you've read that chapter, you will have a solid foundation for understanding the web-based Oracle applications described in this chapter.

The advantage to 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. This is a powerful application paradigm indeed, especially for busy administrators who may manage dozens of databases and who want to maintain a satellite's-eye view of what's going on. If you deploy over the Internet, as well as over your corporate intranet (with the appropriate security measures, of course), you'll be able to administer your systems much more simply and effectively, especially if you're away from your base system, need instant access, and sometimes have difficulty remotely logging in to your intranet. Web-based applications are also useful to the many non-technical people at your site who need different levels of access to browse the database, but who don't want to know, or don't need to know, SQL.

There are many different types of web-based applications available in the Oracle world. In this chapter, we'll focus on a number of excellent applications, highlighting those that help demonstrate a variety of open source implementation strategies. Check out the web sites mentioned in the preface for pointers to other helpful open source web-based applications for Oracle. We'll cover the following applications in this chapter:

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

Karma is an open source tool that can help Oracle DBAs with their daily work. The program is especially helpful in automating the tracking of important but tedious-to-collect information—information that you need to know but are too busy to gather personally. Karma's comprehensive configuration capabilities let you select the particular features and database events to monitor and how often and how strictly to monitor them. You can also break up your many databases into groups, each with their own monitoring criteria and thresholds.

Even if you don't have the time to keep checking Karma's continuously updated web page, the product's new email notification capability will email you with alerts about potential database problems. This way, you'll find out when something goes wrong before anyone else notices it (and the suits need never know!).

The Karma project has been in development since July 1999, when Sean Hull, your faithful author, first started work on it, and it has grown exponentially in popularity since its first release. Its feature list has expanded too, as well as its stability, driven particularly by the comments and bug fixes of its ever-widening group of users. Karma is written in Perl and runs as a daemon, generating HTML pages in a location you can specify.

The main web site for Karma is:

http://www.iheavy.com/karma/

Karma's installation follows the pattern of many Perl-based tools, so if you're familiar with the perl Makefile.PL drill described in Chapter 2, you shouldn't have too much trouble installing Karma. Before you install Karma, you must make sure that the following are installed on your system:

Perl
The Perl DBI and DBD::Oracle modules

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|

Table of Contents