5.2 Apache::OWA

5.2 Apache::OWA

Apache::OWA was written by Svante Sörmark and was named originally after the Oracle Web Application server, which has since morphed into Oracle iAS. Apache::OWA's mission is to give Apache direct access to Oracle Corporation's PL/SQL Web Toolkit. These packages ship automatically with later Oracle servers, from Oracle Version 8.1.7 onward, and they allow PL/SQL programs to create web content.

Although this module is not obviously aimed squarely at Oracle DBAs, we've included it for several reasons:

Many DBAs have database administration tools that are driven by the PL/SQL Web Toolkit or have tools that they would like to access over the Web. If that is true at your site and you either don't have access to a web application server or you want to bypass the complexities of working within your particular server setup, Apache::OWA may be a good option. Essentially, this module will provide you with the necessary access to the PL/SQL Web Toolkit and save you from having to do all of the necessary configuration on your own. You'll end up being able to combine Oracletool, Karma, your own Perl and PL/SQL Web Toolkit DBA scripts, and perhaps some Perl-based system administration web pages too — all within one personalized centrally controlled environment.
Many Oracle DBAs are also Web Application Server administrators, and for those folks Apache::OWA may be a critically important tool.
This module is a great piece of work. In just a few hundred lines of Perl code, Apache::OWA is one of the greatest examples of Oracle-accessing code we've seen. If you're thinking of writing your own Apache Perl modules at any point — for example, to drive your own web and data needs, we highly recommend that you use the OWA.pm file work as a code skeleton.^[12]

^[12] Richard Sutherland's DDL::Oracle tool (described in Chapter 3) is also an excellent code template.

You can find out more about Apache::OWA from the following web sites:

http://sourceforge.net/projects/owa

http://owa.sourceforge.net

The main packages in the PL/SQL Web Toolkit are described in the following list.

HTP

HyperText procedures that generate HTML and send it to the browser. Most HTP procedures bear the name of the HTML construct they're responsible for. For example, HTP.ANCHOR creates HTML anchor statements such as:

<A HREF="http://www.oreilly.com">Input text...</A>

HTF

HyperText functions that help corresponding HTP procedures by wrapping input with various HTML constructs.

OWA_UTIL

A collection of utility procedures and functions divided into three groups:

HTML utilities: A typical procedure here would retrieve the values of CGI environment variables or perform a URL redirection operation.
Dynamic SQL utilities: These produce web pages with dynamically generated SQL.
Date utilities: These simplify date handling.

OWA_OPT_LOCK

This package imposes optimistic locking strategies in order to prevent lost updates.

OWA

Holds internal procedures called by the Oracle PL/SQL Agent itself.

OWA_PATTERN

These pattern-matching utilities perform string matching and substitution with regular expression functionality. Many of the regex definitions used here are the same as those defined in Appendix C.

OWA_TEXT

This set of utilities is used by OWA_PATTERN to manipulate large data strings. They have also been externalized for direct implementation.

OWA_IMAGE

A set of utilities for manipulating HTML image maps.

OWA_COOKIE

Datatypes, procedures, and functions for manipulating HTML cookies.

For more information about these packages, check out http://technet.oracle.com — in particular, pages like the following:

http://technet.oracle.com/doc/windows/was.21/psqlwtlk.htm

5.2.1 Installing Apache::OWA on Unix

Before installing Apache::OWA, you should be aware that this module relies upon the presence of another module called Apache::Request, which lives on CPAN under the name libapreq. You can get this module from:

http://www.cpan.org/authors/id/J/JI/JIMW

Apache::Request was developed by Jim Winstead and mimics the abilities of CGI.pm to deal with GET and POST program parameters. However, it does this in a quicker way for Apache/Perl modules, giving Apache::OWA more execution speed. To improve performance even more, we recommend that you install Apache::DBI for use with Apache::OWA.

The Perl modules contained within the generic Apache::Request library make use of the underlying libapreq C library. They're installed as follows:

$ gzip -d libapreq-1.0.tar.gz
$ tar xvf libapreq-1.0.tar
$ cd libapreq-1.0
$ vi README INSTALL
$ perl Makefile.PL
$ make
$ make test
$ make install

(Make sure to check out the README and INSTALL files with libapreq, particularly on Solaris 8 and Red Hat Linux. There were some issues noted with Version 0.31, although Version 1.0 should have resolved them.)

We can now download and install Apache::OWA proper. We got hold of the Apache-OWA-0.7.tar.gz tarball from the following site:

http://www.cpan.org/authors/id/S/SV/SVINTO

Apache::OWA bends the old conventions a little by naming its unpack directory differently from the download tarball, but hey, we like a little individuality to break up these installation runs. Also note that the make test step was a little skimpy with our tarball, though this may have changed by the time you come to download your own latest version. Let's work through the steps:

$ gzip -d Apache-OWA-0.7.tar.gz
$ tar xvf Apache-OWA-0.7.tar
$ cd OWA
$ vi README
$ perl Makefile.PL
$ make
$ make test    # May be a little skimpy, just yet! :-)
$ make install

That should be it. Now let's try the same under Win32.

5.2.2 Installing Apache::OWA on Win32

Once again, those great folks at ActiveState have done us proud. Just start up a command console while connected to the Internet, and then click in about 50 letters. We'll highlight the places you actually have to type:

C:\Perl\site\lib\Apache> ppm
PPM interactive shell (2.1.5) - type 'help' for available commands.
PPM> install libapreq
Install package 'libapreq?' (y/N): y
...
Writing C:\Perl\site\lib\auto\libapreq\.packlist
PPM> install Apache-OWA
Install package 'Apache-OWA?' (y/N): y
...
Writing C:\Perl\site\lib\auto\Apache\OWA\.packlist
PPM> quit

5.2.3 Configuring Apache::OWA

Those who have wrestled in the past with the various Oracle Webserver products and their sometimes cumbersome administration suites will appreciate how minutely scaled the same process is for Apache::OWA in comparison. It's the size of The Incredible Shrinking Man at the end of the film, when he escapes from the spider (i.e., very small indeed). We simply edit httpd.conf, to create a DAD (Database Access Descriptor). Follow these steps:

For applications with little need for authentication, all you'll require is the following. This calls procedures in the orcl database, living under scott's schema:
```
<Location /scott/ >
   SetHandler perl-script
   PerlHandler Apache::OWA
   PerlSendHeader ON
   PerlSetVar DAD orcl:scott:tiger
</Location>
```
When we create the scott.HelloApacheOWA procedure, in Example 5-6, we'll call it from a browser as follows:

http://localhost/scott/HelloApacheOWA

If by using the same URL we decide instead to call a similarly named procedure under another schema (e.g., webaccess, we'd do it like this):
```
<Location /scott/ >
   SetHandler perl-script
   PerlHandler Apache::OWA
   PerlSendHeader ON
   PerlSetVar DAD orcl:scott:tiger
   PerlSetVar SCHEMA webaccess
</Location>
```
Alternatively, if we set the correct public synonyms and execute permissions, and we want all of our users to log in to the web pages with their individual Oracle username and passwords, we'd do it like this:
```
<Location /owa_db_auth/ >
   AuthName owa_db_auth
   AuthType Basic
   PerlAuthenHandler Apache::OWA
   PerlSendHeader ON
   Require valid-user
   PerlSetVar DB orcl
   PerlSetVar SCHEMA webaccess
   PerlSetVar DB_AUTH true
</Location>
```
(Other more complex security possibilities are documented within the Apache::OWA download.)

As an example of how Apache::OWA works, let's take the first simple configuration we worked through preceding, and insert it into our httpd.conf file. We created the HelloApacheOWA procedure in Example 5-6, under scott:

Example 5-6. HelloApacheOWA.sql

create or replace procedure HelloApacheOWA as
   cursor curs_dept is
      select deptno, dname, loc
        from dept
       order by deptno;
begin
   htp.htmlOpen;
   htp.headOpen;
   htp.title('Apache::OWA, Perl Apache Module for Oracle PL/SQL');
   htp.headClose;
   htp.bodyOpen( cattributes => ' bgcolor="WHITE" ' );
   htp.centerOpen;
   htp.header(1, 'Hello Apache::OWA! :-)');
   htp.hr;
  
   htp.tableOpen( cattributes => ' border="2" width="80%" ');
   htp.tableRowOpen;
   htp.tableHeader( 'Department Number' );
   htp.tableHeader( 'Department Name' );
   htp.tableHeader( 'City Location' );
   htp.tableRowClose;
  
   for rec_dept in curs_dept loop
      htp.tableRowOpen;
      htp.tableData(rec_dept.deptno);
      htp.tableData(rec_dept.dname);
      htp.tableData(rec_dept.loc);
      htp.tableRowClose;
   end loop;
  
   htp.tableClose;
   htp.hr;
   htp.centerClose;
   htp.bodyClose;
   htp.htmlClose;
end;

When we call this procedure via the browser, it generates the output in Figure 5-5. At this point, you may already be thinking of a hundred ways you could expand your own usage of mod_perl and Apache::OWA to create the mother of all remote DBA web toolkits, driven by Perl and PL/SQL. Go for it!

Figure 5-5. Apache::OWA calls PL/SQL