2 Using mod_plsql

This chapter describes how you can set up and use mod_plsql. It contains the following sections:

• Before You Begin

• Installing Required Packages

• Accessing the mod_plsql Configuration Page

• Configuring mod_plsql for Use with OracleAS Portal 3.0.9

2.1 Before You Begin

Before you run mod_plsql, you must satisfy the following requirements:

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

• The database to which you plan to connect mod_plsql must be up and running.

• Oracle Application Server version 10g (9.0.4) ships with OWA package version 9.0.4.0.1. It is recommended that the OWA packages installed in the database are at least version 9.0.4.0.0.

2.2 Installing Required Packages

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

Note: Even if a full database export is made with the Export utility you still must reinstall mod_plsql in the new target instance via running the OWALOAD.SQL script as SYS. Objects in SYS are not imported with the Import/Export mechanism, and the PL/SQL toolkit has to be installed in SYS.

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. Oracle Application Server 10g (9.0.4) ships with OWA package version 9.0.4.0.1. Check the version of the OWA packages currently installed by running the following query:

   select owa_util.get_version from dual;
   
   

   • If the query succeeds, but shows a version less than 9.0.4.0.1, it is recommended that you install the newer OWA packages.

   • If the query fails, you either do not have the OWA packages installed, or are running a very old version of OWA packages, and it is recommended that you install, or upgrade to the new OWA packages.

     Note: To detect older OWA packages, see "How do I detect and clean up duplicate OWA packages installed in the database?" in Appendix A, "Frequently Asked Questions".

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

   @owaload.sql log_file  
   

   Table 2-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. Make sure that you have write permissions to create the log file

5. Scan the log file for any errors.

   Note: The owaload script checks the existing version of the OWA packages in the database and installs a new version only if: No OWA package exists or, Older OWA packages were detected. If your database already has the latest OWA packages or has a newer version installed, the owaload script does nothing and reports this in the log file.

6. Do a manual recompile.

   Note: Installing the OWA packages invalidates all dependent objects. These packages automatically recompile on first access, but a manual recompile is recommended after the reinstallation.

   After the install, check the version of the OWA packages by running "Select owa_util.get_version from dual;". Confirm that the version shown is 9.0.4.0.1 or above.

7. Note that public access is now granted to:
   • OWA_CUSTOM

   • OWA

   • HTF

   • HTP

   • OWA_COOKIE

   • OWA_IMAGE

   • OWA_OPT_LOCK

   • OWA_PATTERN

   • OWA_SEC

   • OWA_TEXT

   • OWA_UTIL

   • OWA_CACHE

   • WPG_DOCLOAD

8. Note also that the following public synonyms are created:
   • OWA_CUSTOM for OWA_CUSTOM

   • OWA_GLOBAL for OWA_CUSTOM

   • OWA for OWA

   • HTF for HTF

   • HTP for HTP

   • OWA_COOKIE for OWA_COOKIE

   • OWA_IMAGE for OWA_IMAGE

   • OWA_OPT_LOCK for OWA_OPT_LOCK

   • OWA_PATTERN for OWA_PATTERN

   • OWA_SEC for OWA_SEC

   • OWA_TEXT for OWA_TEXT

   • OWA_UTIL for OWA_UTIL

   • OWA_INIT for OWA_CUSTOM

   • OWA_CACHE for OWA_CACHE

   • WPG_DOCLOAD for WPG_DOCLOAD

2.2.1 Upgrading from Oracle Application Server or WebDB Listener

If you were previously running Oracle 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.

3. Install the new PL/SQL Web Toolkit as per Section 2.2, "Installing Required Packages".

2.3 Accessing the mod_plsql Configuration Page

All monitoring and configuration takes place through the Oracle Enterprise Manager (OEM) tool. The mod_plsql monitoring and configuration is accessible through links within the HTTP Server components and the Portal.

Note: Refer also to the Online Help available through the Oracle Enterprise Manager tool for mod_plsql and DAD Configuration.

To get to the mod_plsql configuration pages navigate to the applicable Application Server through OEM. You can access the pages either through a portal instance or through the link in the HTTP server instance.

2.3.1 Access the DAD Configuration Pages Through Oracle Enterprise Manager

1. Enter the following URL in a Web browser:

   http://<hostname>:<port_number>
   

   Note: 1810 is the default port.

2. Enter the Oracle Application Server administrator username and password. The default username for administrator user is ias_admin. The default password is defined during the installation of Oracle Application Server.

3. Click OK.

4. Select Oracle Application Server instance with the mod_plsql that needs configuring.

5. Select the HTTP Server link or the Portal instance.

6. Select mod_plsql component or link.

7. Scroll down to DAD Status section.

8. Click Create to set up a new DAD, or click the name of the DAD you are interested in.

2.3.2 Access the DAD Configuration Pages Through Portal

1. Log on to Portal.

2. Click the Builder icon.

3. Access the Administer tab.

4. Click Portal Service Monitoring link in the Services portlet.

5. Click mod_plsql Services in the Portal Components section.

6. Scroll down to DAD Status section.

7. Click Create to set up a new DAD, or click the name of the DAD you are interested in.

2.4 Configuring mod_plsql for Use with OracleAS Portal 3.0.9

To run an Oracle Application Server version 9.0.x middle-tier against a OracleAS Portal 3.0.9 repository, you need to perform the following steps:

1. Use Oracle Enterprise Manager to create two Portal-style DADs: one for the 3.0.9 OracleAS Portal repository and another for the 3.0.9 Oracle Application Server Single Sign-On repository.

   Note: In Oracle Application Server version 9.0.x, the DAD name is case-sensitive. You must ensure that the DAD is created with the same case as it exists for OracleAS Portal version 3.0.9. Mostly, the DAD name is all in lowercase. To see a version 3.0.9 DAD name, view the file $OLD_HOME/Apache/modplsql/cfg/wdbsvr.app.

2. For each DAD, edit the DAD configuration and set PlsqlCompatibilityMode to 1. To edit the DAD configuration:
   1. Edit the file ORACLE_HOME/Apache/modplsql/conf/dads.conf.

   2. Locate the DADs used to connect to the version 3.0.9 OracleAS Portal and Oracle Application Server Single Sign-On repositories.

   3. Add the following line in the DADs:

      PlsqlCompatibilityMode  1
      
      

   4. Run the following command:

      ORACLE_HOME/dcm/bin/dcmctl updateConfig -ct ohs
      
      

   5. Run the following command to restart Oracle HTTP Server:

      ORACLE_HOME/opmn/bin/opmnctl restartproc type=ohs
      
      

      Note: If PlsqlCompatibilityMode is not set for a DAD which is used to access an OracleAS Portal version 3.0.9 repository, you will be unable to download documents that have spaces in the document name, and reside in the Portal document table. Access to such documents will result in the error: HTTP 404 - File not found. If PlsqlCompatibilityMode is set for a DAD which is used to access an OracleAS Portal version 9.0.x repository, you will be unable to download documents that have spaces or plus (+) signs in the document name, and reside in the Portal document table. Access to such documents will result in the error: HTTP 404 - File not found.

      Warning: Once the back-end OracleAS Portal repository is upgraded to version 9.0.x, you should remove the PlsqlCompatibilityMode flag from the DAD. See the section "Removing the PlsqlCompatibilityMode Flag from a DAD" for detailed instructions.

Removing the PlsqlCompatibilityMode Flag from a DAD

To remove the PlsqlCompatibilityMode flag from a DAD, after the back-end OracleAS Portal repository is upgraded to version 9.0.x, perform the following steps:

1. Edit the file ORACLE_HOME/Apache/modplsql/conf/dads.conf.

2. Locate the DADs used to connect to the version 9.0.x OracleAS Portal and Oracle Application Server Single Sign-On repositories.

3. Remove the following line in the DAD:

   PlsqlCompatibilityMode  1
   
   

4. Run the following command:

   ORACLE_HOME/dcm/bin/dcmctl updateConfig -ct ohs
   
   

5. Run the following command to restart Oracle HTTP Server:

   ORACLE_HOME/opmn/bin/opmnctl restartproc type=ohs