Skip Headers

Oracle® Application Server mod_plsql User's Guide 10g Release 3 (10.1.3) B25599-01 |
| Home | Solution Area | Contents | Index

Previous | Next |

# A Frequently Asked Questions

- What is mod_plsql?

- What is the PL/SQL Web Toolkit?

- How do I find the version of mod_plsql?

- How do I find the version of the OWA packages?

- How do I install the OWA packages?

- How do I uninstall the OWA packages?

- How do I detect and clean up duplicate OWA packages installed in the database?

- I am getting HTTP error codes while accessing PL/SQL procedures through mod_plsql.

- All my PL/SQL procedures return a "Document contains no data" error in Netscape, or a blank page in Internet Explorer.

- I have a performant PL/SQL procedure, but some of my HTTP requests through mod_plsql take more than 15 seconds.

- Can I use mod_plsql to run applications on my own database?

- How do I configure mod_plsql?

- How do I create a DAD for mod_plsql?

- What authentication modes are available in mod_plsql?

- What is the mod_plsql Cleanup Thread?

- What kind of database connection pooling is present in mod_plsql?

- How does mod_plsql clean up database sessions?

- What happens when pooled database connections exist in mod_plsql and the database is restarted?

- How does mod_plsql clean up cached content in the file system?

- Can I invoke mod_plsql without a "/pls" prefix in the URL?

- How can I improve PL/SQL and mod_plsql performance?

- What kinds of logging facilities are available in mod_plsql?

- What kind of DMS metrics are available for mod_plsql?

- What considerations should I have in mod_plsql for High Availability?

- What considerations should I have in mod_plsql when the database is separated by a firewall?

- How do I assert a different hostname, port, or request_protocol to the PL/SQL application?

- How do I disable access to procedure names that have a specific pattern?

- I see the error "HTTP-503 ORA-12154" in the file ORACLE_HOME/Apache/Apache/conf/error_log. What does this mean?

- Why do URLs of the formats /DAD/package.procedure() or /DAD/package.procedure(123) not work when invoking mod_plsql?

What is mod_plsql?

mod_plsql is an Oracle HTTP Server plug-in that communicates with the database by mapping browser requests into database stored procedure calls over a SQL*Net connection. It is generally indicated by a `/pls` virtual path. The mod_plsql gateway provides support for building and deploying PL/SQL-based applications on the Web. PL/SQL stored procedures can retrieve data from database tables and generate HTTP responses containing formatted data and HTML code to display in a Web browser. See the Oracle Database Application Developer's Guide - Fundamentals for more information.

What is the PL/SQL Web Toolkit?

The PL/SQL Web Toolkit enables you to develop Web applications as PL/SQL procedures stored in an Oracle database server. Packages in the toolkit define procedures, functions, and data types that you can use in your stored procedures. See the Oracle Database Application Developer's Guide - Fundamentals for more information.

How do I find the version of mod_plsql?

You can determine the version of mod_plsql by executing the `oversioncheck` script on the mod_plsql binary.

On UNIX platforms, issue the following command:

    ORACLE_HOME/Apache/Apache/bin/oversioncheck ORACLE_HOME/Apache/modplsql/bin/modplsql.so

On Windows platforms, issue the following command:

    ORACLE_HOME\Apache\Apache\bin\oversioncheck ORACLE_HOME\bin\modplsql.dll

How do I find the version of the OWA packages?

1. Use SQL*Plus and connect as any user to the database.

2. Execute the following command:

       select owa_util.get_version from dual;

   This should show the version of the OWA packages. For example, 10.1.2.0.4.

If this query fails, you are having a very old version of OWA packages that does not have versioning. It is recommended that you upgrade to a newer version.

How do I install the OWA packages?

See Section 1.2, "Installing Required Packages" for more information.

How do I uninstall the OWA packages?

OWA packages can be uninstalled by performing the following tasks:

1. Navigate to the directory from where the OWA packages were installed. For example:

       cd ORACLE_HOME/Apache/modplsql

2. Use SQL*Plus to connect as the owner the OWA packages. This user should be the SYS user, unless you have an old version of the OWA packages.

3. Invoke the script `owadins.sql` to uninstall the OWA packages.

How do I detect and clean up duplicate OWA packages installed in the database?

The following SQL query can be used to determine the location of the OWA packages:

    SELECT OWNER, OBJECT_TYPE
    FROM   DBA_OBJECTS
    WHERE  OBJECT_NAME = 'OWA'

You will see the following results:

    SQL>

    1  SELECT OWNER, OBJECT_TYPE
    2  FROM DBA_OBJECTS
    3* WHERE OBJECT_NAME = 'OWA'

    OWNER  OBJECT_TYPE
    -----  -----------
    SYS    PACKAGE
    SYS    PACKAGE BODY
    PUBLIC SYNONYM

If you see more lines than shown in the preceding SQL query, it means that older OWA packages exist in other schemas, which may cause issues for mod_plsql users. In such situations, uninstall all versions of the OWA packages from the database, and reinstall the OWA packages that ship with the product.

I am getting HTTP error codes while accessing PL/SQL procedures through mod_plsql.

mod_plsql logs detailed error messages to the Oracle HTTP Server file `ORACLE_HOME``/Apache/Apache/logs/error_log`. Scan this file to understand the problem. For more information on mod_plsql logging, see "What kinds of logging facilities are available in mod_plsql?".

All my PL/SQL procedures return a "Document contains no data" error in Netscape, or a blank page in Internet Explorer.

This problem could occur if you have duplicate OWA Packages installed in the database. See "How do I detect and clean up duplicate OWA packages installed in the database?" for more information.

I have a performant PL/SQL procedure, but some of my HTTP requests through mod_plsql take more than 15 seconds.

The most common reason for this problem is that the middle-tier character set does not match that of the back-end database, and `HTTPKeepAlive` is enabled in Oracle HTTP Server. This kind of misconfiguration causes an invalid Content-Length to be sent back to the browser, causing the browser to detect the end of the response stream only when the `KeepAliveTimeout` interval causes the stream to be closed. To solve the problem, ensure that the `PlsqlNLSLanguage` parameter in the DAD matches that of the database.

If this is not the reason for the problem, there could be performance issues with your PL/SQL application. You can confirm this by:

- Enabling performance logging for mod_plsql. See the section "What kinds of logging facilities are available in mod_plsql?" for more information.

- Running the URL that has performance problems, a few times.

- Scanning the `error_log` for the time reported in "dbProcTime". This time denotes the time taken by the target PL/SQL procedure as viewed from the database. If this is high, you will need to debug your PL/SQL application using standard database profiling techniques.

Can I use mod_plsql to run applications on my own database?

Yes. But before you can run your applications, you need to install the OWA packages into your database. See Section 1.2, "Installing Required Packages".

How do I configure mod_plsql?

Please refer to the Oracle HTTP Server Administrator's Guide.

How do I create a DAD for mod_plsql?

For manual configuration of DADs, refer to the mod_plsql section in the Oracle HTTP Server Administrator's Guide.

What authentication modes are available in mod_plsql?

See Chapter 2, "Securing Application Database Access Through mod_plsql".

What is the mod_plsql Cleanup Thread?

mod_plsql starts a thread in each httpd process. The job of this thread is to clean up idle database sessions and the file system cache. This thread is called the Cleanup Thread.

What kind of database connection pooling is present in mod_plsql?

Refer to Chapter 4, "Optimizing PL/SQL Performance".

How does mod_plsql clean up database sessions?

mod_plsql cleans up unused database sessions based on the configuration setting of `PlsqlIdleSessionCleanupInterval`. Besides this, the configuration directive `PlsqlMaxRequestsPerSession` governs how many requests will be serviced from a pooled database session. Finally, database sessions are closed when HTTPD processes are shut down.

What happens when pooled database connections exist in mod_plsql and the database is restarted?

See Section 3.11.3, "Detecting Dead Database Connections in a Connection Pool" for more information.

How does mod_plsql clean up cached content in the file system?

The cleanup thread scans the file system cache based on the configuration of `PlsqlCacheCleanupTime`. The default cleanup time is everyday at 11 P.M. local time.

Can I invoke mod_plsql without a "/pls" prefix in the URL?

Yes. Since mod_plsql uses the Oracle HTTP Server's `Location` directive, you can configure any virtual path to be serviced by mod_plsql.

How can I improve PL/SQL and mod_plsql performance?

Refer to Chapter 4, "Optimizing PL/SQL Performance".

What kinds of logging facilities are available in mod_plsql?

- By default, mod_plsql logs alerts, warnings, and errors to the Oracle HTTP Server `error_log` file `ORACLE_HOME``/Apache/Apache/logs/error_log`. The amount of information logged by mod_plsql is controlled by the setting of Oracle HTTP Server' `LogLevel` parameter in `httpd.conf`. By default, this is configured to `warn`.

- You can also enable performance logging for mod_plsql on a per-request basis as follows:

  1. Edit `ORACLE_HOME``/Apache/Apache/conf/httpd.conf` and set `LogLevel` to `info` (default is `warn`).

  2. Restart Oracle HTTP Server using the following command:

         ORACLE_HOME/opmn/bin/opmnctl restartproc type=ohs

  3. Issue some URLs to mod_plsql and verify that the file `ORACLE_HOME``/Apache/Apache/logs/error_log` starts showing entries as follows:

         [Tue Apr 01 14:54:49 2003] [info] mod_plsql: [perf] 130.35.92.145 /pls/app/htp.p status=200 user=scott reqTime=21ms connSU=(null),0ms connRO=(null),0ms connNSSO=HIT,1ms procTime=17ms sessionTidyTime=0ms cache=(null) cookie=(null),0ms pageCalls=0,0ms bytes=5 describe=No,0ms streamTime=0ms pid=175 sessFile=(null) userFile=834\0855 sysFile=470\5949 cacheLevel=(null) cacheTime=0ms dbProcTime=15ms id=1049237685:130.35.92.145:373:1 spid=(null) qs=(null) requestTrace=(null) cookieLen=0 cookieValue=(null) reqUserTime=16ms assertUser=(null) subid=(null) authLevel=(null) oraError=0

- If you are an OracleAS Portal customer, you can additionally choose to enable Portal session cookie logging by adding the following configuration parameter to your Portal DAD:

      PlsqlInfoLogging InfoDebug

  This is in addition to the previous setting of `LogLevel` to `info` in `httpd.conf`.

- Finally, you can enable debug logging in mod_plsql. This is the highest level of logging and is not recommended for active sites.

  Caution: This mode of logging should be enabled only at the request of Oracle Support.

  In this mode, debug messages are logged to Oracle HTTP Server's `error_log` file and additional mod_plsql specific logs are created under `ORACLE_HOME``/Apache/modplsql/logs`. Log location is configurable using the `PlsqlLogDirectory` directive in `ORACLE_HOME``/Apache/modplsql/conf/plsql.conf`. To enable debug level logging:

  1. Edit `ORACLE_HOME``/Apache/modplsql/conf/plsql.conf` and set `PlsqlLogEnable` to On (default is Off).

  2. Restart Oracle HTTP Server using the following command:

         ORACLE_HOME/opmn/bin/opmnctl restartproc type=ohs

See Also: Section 1.5, "Diagnostic Output of mod_plsql"

What kind of DMS metrics are available for mod_plsql?

For information on Dynamic Monitoring Service, refer to the appendix on performance metrics in the Oracle Application Server Performance Guide.

What considerations should I have in mod_plsql for High Availability?

For high availability, mod_plsql based applications should be aware of the following things:

- The mod_plsql configuration parameter `PlsqlDatabaseConnectString` should use a connect string format of `NetServiceNameFormat` so that name resolution happens through an LDAP lookup of Oracle Internet Directory. This enables you to configure the database `host:port:service_name` information in a central repository, which makes it easier to add or remove RAC nodes when required.

- mod_plsql automatically detects dead database connections. See Section 3.11.3, "Detecting Dead Database Connections in a Connection Pool" for more information.

What considerations should I have in mod_plsql when the database is separated by a firewall?

If a firewall exists between the middle tier running mod_plsql, and the back end database, the idle session cleanup interval in mod_plsql should be configured lower than the idle session cleanup interval of the firewall. This ensures that the firewall never closes a connection established by mod_plsql.

Note: mod_plsql idle session cleanup interval can be configured using the parameter `PlsqlIdleSessionCleanupInterval` in `ORACLE_HOME``/Apache/modplsql/conf/plsql.conf`. The default value is 15 minutes.

How do I assert a different hostname, port, or request_protocol to the PL/SQL application?

- In situations where your Oracle HTTP Server instance is front-ended by Web Cache or a Load Balancing Router, there is a need to assert the hostname and port for the site to be that of the Web Cache or the LBR. In such situations, it is recommended that you use the Oracle HTTP Server configuration directives `ServerName` and `Port` to do the assertion. If for some reason, you do not wish to assert the hostname and port at the Oracle HTTP Server level, you can use the mod_plsql configuration directive `PlsqlCGIEnvironmentList` to assert a different hostname and port to only the PL/SQL applications running under mod_plsql. For example:

  - `PlsqlCGIEnvironmentList SERVER_NAME=lbr.us.oracle.com`

    Consider using Oracle HTTP Server's `ServerName` directive in `httpd.conf` instead.

  - `PlsqlCGIEnvironmentList SERVER_PORT=9999`

    Consider using Oracle HTTP Server's `Port` directive in `httpd.conf` instead.

  - `PlsqlCGIEnvironmentList HTTP_HOST=myservername.us.oracle.com:9999`

    Combination of `SERVER_NAME:SERVER_PORT`.

- Similarly, in cases where your site is accessed externally as an SSL, but is internally running in non-SSL mode (with an SSL accelerator in between), you might want to assert the REQUEST_PROTOCOL as HTTPS so that the PL/SQL application generates SSL links instead of non-SSL links. For example:

      PlsqlCGIEnvironmentList  REQUEST_PROTOCOL=https

How do I disable access to procedure names that have a specific pattern?

Refer to Section 2.1.2, "Adding More Rules to the PlsqlExclusionList Directive in mod_plsql" for more information.

I see the error "HTTP-503 ORA-12154" in the file ORACLE_HOME/Apache/Apache/conf/error_log. What does this mean?

This error means that mod_plsql is unable to connect to the database.

Ensure that:

1. The database is up and running.

2. The username and password information in the DAD is correct.

3. The middle tier is able to connect to the database using the `PlsqlDatabaseConnectString` parameter in the DAD.

In most situations, the problem occurs because SQL*Net is not able to resolve the connect string parameter using the configuration information under `ORACLE_HOME``/network/admin`.

- For entries configured with `TNSFormat` or `NetServiceNameFormat`, validate the connect string information by using `tnsping dad_connect_string`. For example:

      tnsping "cn=iasdb,cn=oraclecontext"

  or

      tnsping iasdb.us.oracle.com

- For entries configured with `SIDFormat` and `ServiceNameFormat`, ensure that the hostname, port, and SID/service_name information match for the database listener. After verifying this, confirm that SQL*Plus can connect to the database using the DAD username, password, and connect string.

If this does not work, refer to the Oracle SQL*Net documentation on how to troubleshoot this further.

Note: The default connect string parameter in the DAD is configured to get resolved through an LDAP lookup in Oracle Internet Directory. If you make changes to `ldap.ora`, you will need to restart Oracle HTTP Server for the changes to be accessible to mod_plsql.

Why do URLs of the formats /DAD/package.procedure() or /DAD/package.procedure(123) not work when invoking mod_plsql?

mod_plsql does not support URLs of the following formats:

- `/DAD/package.procedure()`

- `/DAD/package.procedure(123)`

For example:

`http://www.acme.com:9000/pls/mydad/mypackage.myproc()` or `http://www.acme.com:9000/pls/mydad/mypackage.myproc(123)`

For details about the supported URL format, refer to Section 3.3, "Invoking mod_plsql".