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.

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.

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.

The most common reason for this problem is that the middle-tier character set does not match that of the back-end database, and HTTP KeepAlive 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.

What kind of database connection pooling is present in mod_plsql?

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?

When the database connection is severed, the first request that attempts to execute a PL/SQL procedure using the severed connection will fail. Subsequent requests will reestablish a database session and start functioning normally. The number of failures will be directly proportional to the number of pooled database sessions. Future versions of mod_plsql will detect dead connections automatically.

Note:

If the database is not restarted within the time interval of PlsqlIdleSessionCleanupInterval, then the cleanup thread will clean up the severed sessions, and no errors will be seen by end-users.

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 OHS' Location directive, you can configure any virtual path to be serviced by mod_plsql.

How can I improve PL/SQL and mod_plsql performance?

What kinds of logging facilities are available in mod_plsql?

By default, mod_plsql logs alerts/warnings/errors to the OHS error_log file ORACLE_HOME/Apache/Apache/logs/error_log. The amount of information logged by mod_plsql is controlled by the setting of OHS' 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:

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

Restart OHS using the following command:

ORACLE_HOME/opmn/bin/opmnctl restartproc type=ohs

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

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:

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

Restart OHS using the following command:

ORACLE_HOME/opmn/bin/opmnctl restartproc type=ohs

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.

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 OHS 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 OHS 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 OHS 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 OHS' ServerName directive in httpd.conf instead.

PlsqlCGIEnvironmentList SERVER_PORT=9999

Consider using OHS' 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?

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:

The database is up and running.

The username and password information in the DAD is correct.

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.101/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.