5
Installing the PL/SQL Gateway

5.1 System Requirements

The following are the recommended and minimum requirements for installing and running the PL/SQL Gateway:

Operating Systems

• Windows NT 4.0 with Service Pack 3 or above

• Solaris 2.6 and above

• HP-UX 11.0

• IBM AIX 4.3.2/4.3.3

• Compaq Tru64 4.0d

• Intel Solaris 2.7

Oracle Database

• Oracle8i (Release 8.1.6 or 8.1.7)

  Note: The PL/SQL Gateway requires the Oracle 8.1.7 client libraries to be installed in the same Oracle Home as the PL/SQL Gateway. If these libraries are installed, you can still run the PL/SQL Gateway against remote Oracle 8.0.5 or above databases. For example, you can use the PL/SQL Gateway to run PL/SQL procedures installed in a remote 8.0.5 database.

Web Listener

• Oracle HTTP Server (powered by Apache) 1.3.12 for Oracle9i Application Server version 1.0.2

Web Browsers

• Netscape 4.0.8 and above

• Microsoft Internet Explorer 4.0.1 with Service Pack 1 and above

5.2 Before you begin

Before you install the PL/SQL Gateway using the Oracle9i Application Server v1.0 Oracle Universal Installer, satisfy the following requirements:

• You must have a SYS user password on the database where you plan to load PL/SQL Web Agent packages required by the PL/SQL Gateway.

• The database to which you plan to connect the PL/SQL Gateway must be up and running.

• You must have enough disk space on the machine where you plan to run the Oracle Universal Installer.

• You must have write permissions to the directory where the Oracle Universal Installer is writing its oraInventory data.

5.3 Installation

To begin the Oracle Universal Installer, execute the runInstaller application located on your product CD or stage area. Follow the installation instructions, including choosing a directory where you want to install Oracle9i Application Server v1.0.2. This install directory is referred to as <ORACLE_HOME>.

5.4 Installing Required Packages

After installation, manually install additional required packages using the owaload.sql script.

1. Navigate to the directory where the owaload.sql file is located. This directory is <ORACLE_HOME>/APACHE/modpsql/owa.

2. Using SQL*Plus, log into the Oracle database as the SYS user.

3. At a SQL prompt, run the following command:

   @owaload.sql log_file  
   

Table 5-1 Installing Required Packages Parameters

Elements	Description
owaload.sql	Installs the PL/SQL Web Toolkit packages into the SYS schema. It also creates public synonyms and makes the packages public so that all users in the database have access to them. Therefore, only one installation per database is needed.
log_file	The installation log file.

5.4.1 Upgrading from Oracle9i Application Server or WebDB Listener

If you were previously running Oracle9i Application Server or WebDB Listener 2.5 and below:

1. Verify there is no user data (other than the PL/SQL Web Toolkit packages) in the schema.

2. Drop the schema where the old PL/SQL Web Toolkit packages were installed. The new PL/SQL Web Toolkit packages installed in SYS will work.

5.4.2 Upgrading from OAS Installations to Oracle9i Application Server

The PL/SQL Web Toolkit packages shipped with Oracle 8.1.7 and Oracle9i Application Server v1.0 are recommended for installation.

If the new PL/SQL Web Toolkit packages have been installed (either automatically or manually) and you were previously running OAS, note that the 8.1.7 install/upgrade or manual install for mod_plsql installs new PL/SQL Web Toolkit packages in the SYS schema. It also recreates PL/SQL Web Toolkit public synonyms to reference these new packages.

However, if you face problems while running the OAS PL/SQL Cartridge, recreate the older public PL/SQL Web Toolkit package synonyms.

To recreate the old public synonyms do the following:

1. From SQL*Plus, connect as SYS

2. Run the following statements. This drops all PL/SQL Web Toolkit public synonyms created during the upgrade process/

   drop public synonym OWA_CUSTOM; 
   drop public synonym OWA_GLOBAL; 
   drop public synonym OWA; 
   drop public synonym HTF; 
   drop public synonym HTP; 
   drop public synonym OWA_COOKIE; 
   drop public synonym OWA_IMAGE; 
   drop public synonym OWA_OPT_LOCK; 
   drop public synonym OWA_PATTERN; 
   drop public synonym OWA_SEC; 
   drop public synonym OWA_TEXT; 
   drop public synonym OWA_UTIL; 
   drop public synonym OWA_INIT; 
   drop public synonym OWA_CACHE; 
   drop public synonym WPG_DOCLOAD; 
   
   
   

3. Connect to the old PL/SQL Web Toolkit package installation schema (typically OAS_PUBLIC).

4. Run the following statements. This recreates the PL/SQL Web Toolkit public synonyms that were changed during the upgrade process to reference your old PL/SQL Web Toolkit package installation.

   create public synonym OWA_CUSTOM for OWA_CUSTOM; 
   create public synonym OWA_GLOBAL for OWA_CUSTOM; 
   create public synonym OWA for OWA; 
   create public synonym HTF for HTF; 
   create public synonym HTP for HTP; 
   create public synonym OWA_COOKIE for OWA_COOKIE; 
   create public synonym OWA_IMAGE for OWA_IMAGE; 
   create public synonym OWA_OPT_LOCK for OWA_OPT_LOCK; 
   create public synonym OWA_PATTERN for OWA_PATTERN; 
   create public synonym OWA_SEC for OWA_SEC; 
   create public synonym OWA_TEXT for OWA_TEXT; 
   create public synonym OWA_UTIL for OWA_UTIL; 
   create public synonym OWA_INIT for OWA_CUSTOM; 
   create public synonym OWA_CACHE for OWA_CACHE; 
   create public synonym WPG_DOCLOAD for WPG_DOCLOAD; 
   
   

If you have an OAS installation in which the new PL/SQL Web Toolkit packages were never installed and you use Oracle9i Application Server, it is recommended that you install the new PL/SQL Web Toolkit packages. If you continue using the older PL/SQL Web Toolkit packages, in order to use Oracle9i Application Server mod_plsql, you must run the following SQL statements

Note: These statements are part of the new PL/SQL Web Toolkit package install and are required only if you have never installed the new PL/SQL Web Toolkit packages and choose to continue using the older PL/SQL Web Toolkit packages.

1. From SQL*Plus, connect as SYS.

2. Locate the new PL/SQL Web Toolkit packages and install the following packages:

   wpiutl.sql

   wpgdocs.sql

   wpgdocb.sql

3. Grant execute on wpg_docload to public

4. Create public synonym wpg_docload for wpg_docload.

These steps install the required packages needed to run mod_plsql with an older PL/SQL Web Toolkit package installation. In this configuration, you cannot use some of the new features in the PL/SQL Web Toolkit packages.

5.5 Configuring the Oracle HTTP Server Listener

The Oracle9i Application Server installation creates configuration files that you can edit, including the following that affect the PL/SQL Gateway.

5.5.1 apachectl file

The apachectl starts and stops the Oracle HTTP Server. For UNIX, it is located at:

<ORACLE_HOME>/Apache/Apache/bin/apachectl

Inside this file, there are three parameters that affect the PL/SQL Gateway:

Table 5-2 apachectl file Parameters

Parameter	Description
ORACLE_HOME	Oracle Home where the PL/SQL Gateway runs. Default: <ORACLE_HOME>
LD_LIBRARY_PATH (Solaris, Compaq Tru64, Intel Solaris and Intel LINUX) SHLIB_PATH (for HP-UX) LIBPATH (for IBM)	Oracle libraries needed by the PL/SQL Gateway. This must point to an Oracle 8.1.7 installation. Default: <ORACLE_HOME>/lib
WV_GATEWAY_CFG	PL/SQL Gateway configuration file. Default on UNIX: <ORACLE_HOME>/Apache/modplsql/cfg/wdbsvr.app Default on Windows NT: <ORACLE_HOME>\APACHE\modplsql\cfg\wdbsvr.app On NT, it is an environment variable. To access it, click Start->Settings->Control Panel->System. Select the Environment tab, then create a System variable called WV_GATEWAY_CFG that points to the configuration file.

Note: To run the PL/SQL Gateway in another Oracle Home:

For UNIX platforms, change both the ORACLE_HOME and LD_LIBRARY_PATH settings. (For HP-UX, change SHLIB_PATH and for IBM, change LIBPATH settings.) For NT, change the PATH environment setting to <ORACLE_HOME>\bin.

5.5.2 httpd.conf file

This configuration file defines the behavior of Oracle HTTP Server (powered by Apache). You can set your port number as well as other server settings. It is located at:

<ORACLE_HOME>/Apache/Apache/conf/httpd.conf

5.5.3 plsql.conf file

This configuration file describes settings for the PL/SQL Gateway module. It is located at:

<ORACLE_HOME>/Apache/modplsql/cfg/plsql.conf

There settings are configurable:

• LoadModule plsql_module <MOD_PATH> - the location of the PL/SQL Gateway module.
  Default on UNIX: <ORACLE_HOME>/Apache/modplsql/bin/modplsql.so
  Default on HP: <ORACLE_HOME>/Apache/modplsql/bin/modplsql.sl
  Default on Windows NT: <ORACLE_HOME>\bin\modplsql.dll

• Location <MOUNT_PATH> - the prefix in the URL for which the PL/SQL Gateway is invoked. Default: /pls

5.5.4 wdbsvr.app file

This main configuration file describes settings for the PL/SQL Gateway module. For UNIX, it is located at:

<ORACLE_HOME>/Apache/modplsql/cfg/wdbsvr.app

For NT, it is located at:

<ORACLE_HOME>\Apache\modplsql\cfg\wdbsvr.app

It contains all the DAD information. Most of the settings can be edited by using the PL/SQL Gateway Configuration page, which you can access through your browser as shown "Accessing the PL/SQL Gateway Configuration page".

5.5.5 Performance Tuning Oracle HTTP Server

This section describes several Oracle HTTP Server powered by Apache settings to improve performance.

5.5.5.1 mod_expires

For all static files served by the server (images, static pages, etc.), use mod_expires to cache in the browser. This improves performance by having the browser retrieve the files from the cache, avoiding contacting the server for static files which do not change often.

Mod_expires uses expires-based caching technique. You set the time period that the files are valid for. Do this by using the ExpiresActive, ExpiresDefault, and ExpiresByType directives. Refer to www.apache.org to search for these directives and their usage.

For example:

To have a directory of static files cached by the browser when they are served, the directives would be:

Alias /images/ "/iAS/staic_images/" 
<Directory "/iAS/static_images/"> 
AllowOverride None 
Order allow, deny 
Allow from all 
ExpiresActive On 
ExpiresDefault "access plus 8 hours" 
</Directory> 

The browser will cache the material for 8 hours if any of the content in the /iAS/static_images directory is served.

5.5.5.2 KeepAlive Directives

Another family of directives you can use to fine tune performance are the KeepAlive, MaxKeepAliveRequests, and KeepAliveTimeout directives.

These directives tells the server when to destroy a connection between itself and the browser. Since connection establishment is time consuming, this can improve response time if the connection is already established. The default settings are:

Table 5-3 Default Settings

Directive	Default
KeepAlive	On
MaxKeepAliveRequests	100
KeepAliveTimeout	15 seconds

Adjust these settings to achieve better performance. Refer to www.apache.org for more information.

5.5.6 Customizing Error Pages Through the Oracle HTTP Server

For all errors generated by the PL/SQL Gateway, you can customize them to have the same look and feel of your application.

1. Verify the DAD parameter error_style is set to WebServer in your wdbsvr.app file (eg. error_style = WebServer). Refer to "DAD Settings Accessible through the Configuration File".

2. In your plsql.conf file, between the <Location> and </Location> directives, add the following line:

   ErrorDocument 500 /error_500.html 
   
   

   This tells the Oracle HTTP Server to serve the error_500.html file when it encounters a 500 HTTP status code from the PL/SQL Gateway.

3. For other errors, add similar lines:

   ErrorDocument 403 /error_403.html 
   
   

4. You can also use cgi-scripts to generate dynamic error pages:

   ErrorDocument 403 /cgi-bin/gen_error.pl 
   
   

   This tells the Oracle HTTP Server to execute the perl script called gen_error.pl under the virtual path cgi-bin. For example:

   <Location /pls> 
   SetHandler pls_handler 
   Order deny, allow 
   Allow from all 
   ErrorDocument 500 /error_500.html 
   ErrorDocument 403 /cgi-bin/gen_error.pl 
   </Location> 
   

   In this example, any 500 errors from the PL/SQL Gateway will be handled by serving the error_500.html located in the root virtual path. Any 403 errors will be handled by executing the gen_error.pl perl script in the cgi-bin virtual path. For more information about the ErrorDocument directive, refer to Apache's website www.apache.org.

5.6 Accessing the PL/SQL Gateway Configuration page

To access to the PL/SQL Gateway Configuration page, enter the following URL in your Web browser:

http://<hostname>:<port>/pls/DAD/<admin_path>/gateway.htm 

Table 5-4 PL/SQL Gateway Configuration Page Parameters

Parameter	Description
hostname	The machine where the application server is running.
port	The port where the application server is listening. If omitted, port 80 is assumed.
admin_path	The URL path element that identifies an admin page. The default is admin_. For example, if you use the default of admin_, the following URL invokes the PL/SQL Gateway configuration page if the invoking user is listed in the administrators configuration setting: http://www.myserver.com/pls/admin_/gateway.htm Configuration settings are protected by the Administrators setting of the configuration file. Refer to "Configuring the PL/SQL Gateway" for more information.

5.6.1 plsql.conf configuration file

The Oracle HTTP Listener configuration file includes the modplsql configuration file plsql.conf. The contents of plsql.conf are:

# Directives added for the PL/SQL Gateway 

For UNIX:

LoadModule plsql_module <ORACLE_HOME>/Apache/modplsql/bin/modplsql.so 

For HP-UX:

LoadModule plsql_module <ORACLE_HOME>/Apache/modplsql/bin/modplsql.sl 

For NT:

LoadModule plsql_module<ORACLE_HOME>\bin\modplsql.dll

For all:

# Enable handling of all virtual paths beginning with "/pls" by mod-plsql 
# 
<Location /pls> 
  SetHandler pls_handler 
  Order deny,allow 
  Allow from all 
</Location>

5.7 Starting and stopping the Oracle HTTP Server Listener

5.7.1 UNIX

To start the Apache listener, type:

<ORACLE_HOME>/Apache/Apache/bin/apachectl start 

To start the Apache listener with SSL support, type:

<ORACLE_HOME>/Apache/Apache/bin/apachectl startssl 

To stop the Apache listener, type:

<ORACLE_HOME>/Apache/Apache/bin/apachectl stop 

5.7.2 Windows NT

On Windows NT, the Oracle HTTP Server is installed as a service. To start the Oracle HTTP Server with SSL support:

1. Select Start->Settings->Control Panel.

2. Select Services.

3. Highlight the OracleHTTPServer service.

4. Click Start.

To stop the Oracle HTTP Server:

1. Select Start->Settings->Control Panel.

2. Select Services.

3. Highlight the OracleHTTPServer service.

4. Click Stop.