2

Installing the PL/SQL Gateway

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

• IBM AIX 4.3.2/4.3.3

• Compaq Tru64 4.0d

• Solaris Intel 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

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

• On Windows NT - 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

2.2 Before you begin

Before you install the PL/SQL Gateway using the Oracle9i Application Server v1.0 Oracle Universal Installer, you must satisfy the following prerequisite 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.

2.3 Installation

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

2.4 Installing required packages

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

1. Navigate to the directory where the owaload.sql file is located. This directory should be <ORACLE_HOME>/Apache/modplsql/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  
   
   
   

   where

   log_file is the installation log file.

   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.

2.4.0.1 PL/SQL Web Toolkit Packages

Starting with Oracle 8.1.7 and Oracle9i Application Server v1.0.1, there are a new set of PL/SQL Web Toolkit packages which have additional functionality in them.

• In an Oracle 8.1.7 install/upgrade, the new PL/SQL Web Toolkit packages are automatically installed into the SYS schema.

• In Oracle9i Application Server v1.0.1, users of mod_plsql are required to manually install these packages into the SYS schema.

• In an 8.1.7 install, the new PL/SQL Web Toolkit packages are located under the $ORACLE_HOME/rdbms/admin directory.

• In an Oracle9i Application Server v1.0.1 install, the new PL/SQL Web Toolkit packages are located under the $IAS_HOME/Apache/modplsql/owa directory.

Note that Oracle Portal 3.0 depends on the new PL/SQL Web Toolkit packages.

2.4.0.1.1 Non-OAS Installations

If your installation does not have OAS and you were previously running Oracle9i Application Server or WebDB listener 2.5 and below, you should 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. More than one PL/SQL Web Toolkit package installations ay result in problems. Before dropping the schema, verify there is no user data (other than the PL/SQL Web Toolkit packages) in the schema.

2.4.0.1.2 OAS Installations

The new PL/SQL Web Toolkit packages shipped with Oracle 8.1.7 and Oracle9i Application Server v1.0 many enhancements and 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 will install new PL/SQL Web Toolkit packages in the SYS schema and recreate PL/SQL Web Toolkit public synonyms to reference these new packages. But, if you face issues with running the OAS PL/SQL Cartridge, you will need to recreate the older public PL/SQL Web Toolkit package synonyms. To recreate the old public synonyms do the following:

1. From SQLPlus, connect as SYS

2. Run the following statements. This will drop 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 will recreate 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 choose to use Oracle9i Application Server as well, it is recommended that you install the new PL/SQL Web Toolkit packages. If you decide to 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 already a 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 will install the required packages needed to run mod_plsql with an older PL/SQL Web Toolkit package installation. In this configuration, you will not be able to make use of some of the new features in the new PL/SQL Web Toolkit packages.

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

2.5.1 apachectl file

The apachectl is used to start and stop Oracle HTTP Server on Solaris. It is located at:

<ORACLE_HOME>/Apache/Apache/bin/apachectl

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

• ORACLE_HOME - the Oracle Home in which the PL/SQL Gateway runs. Default: <ORACLE_HOME>

• LD_LIBRARY_PATH - the Oracle libraries needed by the PL/SQL Gateway. This should point to an Oracle 8.1.7 installation. This parameter is for Solaris only.
  Default: <ORACLE_HOME>/lib

• WV_GATEWAY_CFG - the PL/SQL Gateway configuration file.
  Default on Solaris: <ORACLE_HOME>/Apache/modplsql/cfg/wdbsvr.app
  Default on Windows NT <APACHE_HOME>/modplsql/cfg/wdbsvr.app

On Windows NT, the Oracle HTTP Server is installed as a service. To aceess the serive, click Start->Settings->Control Panel->System. Click the Environment tab, then create a System variable called WV_GATEWAY_CFG that points to the
configuration file.

Note If you want to have the PL/SQL Gateway running in another Oracle Home, remember to change both the ORACLE_HOME and LD_LIBRARY_PATH settings.

2.5.2 httpd.conf

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

2.5.3 plsql.conf file

This configuration file describes settings for the 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 Solaris: <ORACLE_HOME>/Apache/modplsql/bin/modplsql.so
  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

2.5.4 wdbsvr.app file

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

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

This is the main PL/SQL Gateway configuration file. It contains all the DAD information. Please do not edit this file directly. Use the PL/SQL Gateway configuration page, which you can access through your browser as shown below.

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

where:

<hostname> is the machine where the application server is running.

<port> specifies the port at which the application server is listening. If omitted, port 80 is assumed.

<admin_path> specifies the URL path element that identifies an admin page. The default is admin_. For example, if you specify the default of admin_, the following URL will invoke the PL/SQL Gateway configuration page, given that the invoking user is listed in the administrators configuration setting:

http://www.myserver.com/pls/admin_/gateway.htm

Configuration settings are protected by the administration security settings. The web administration page can only be invoked by those users whose user names appear in the Administrators setting of the configuration file. See "Configuring the PL/SQL Gateway" for more information.

2.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 
# 
LoadModule plsql_module %APACHE_HOME%/modplsql/bin/modplsql.so 

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

2.7 Starting and stopping the Oracle HTTP Server Listener

To start the Apache listener, type:

<ORACLE_HOME>/Apache/Apache/bin/httpdsctl start 

To start the Apache listener with SSL. support, type:

<ORACLE_HOME>/Apache/Apache/bin/httpdsctl startssl 

To stop the Apache listener, type:

<ORACLE_HOME>/Apache/Apache/bin/httpdsctl stop