Section 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 i AS. 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:

1. 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> 

2. 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> 

3. 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