Mod plsql

MOD_PLSQL is an Oracle HTTP Server (Apache) extension module that allows developers to create dynamic web pages from PL/SQL packages and stored procedures within the database. It is ideal for developing fast and flexible applications that can run on the Internet or an Intranet.

History

MOD_PLSQL was formerly called the Oracle PL/SQL Cartridge and OWA (Oracle Web Agent).

Starting with Oracle 10g, DAD's must be created by editing configuration files with a text editor. The Web Browser interface is no longer available.

The Mod_PLSQL Architecture

MOD_PLSQL uses a 2-tier architecture where clients (with Web Browsers) interact directly with the databases.

+-CLIENT-+                +-----------------S E R V E R---------------+
|        |                |                                           |
|   Web  | <--Internet--> | Oracle HTTP <--> mod_plsql <-->  Oracle   |
| Browser|      HTTP      |   Server                        Database  |
|        |                |                                           |
+--------+                +-------------------------------------------+

Configuring a DAD

A DAD or Database Access Descriptor is used to specify configuration parameters for an application.

Oracle 9i

Start a Web Browser like Firefox or Internet Explorer and navigate to the MOD_PLSQL Gateway Configuration Menu (http://your.host.name:7777 and click on "mod_plsql"). Click on "Gateway Database Access Descriptor Settings". Select one of the options to add a new DAD (Database Access Descriptor). Enter at least the following fields:

• Database Access Descriptor (DAD) Name
• Schema Name
• Oracle User Name
• Oracle Password
• Oracle Connect String (if not the local DB)

Click on "Apply" to make your changes permanent.

Configuration parameters and log files are stored in the $ORACLE_HOME/Apache/modplsql subdirectory.

Oracle 10g

Change directory to $ORACLE_HOME/Apache/modplsql/conf, edit file dads.conf and include the required configuration section. For example:

<Location /pls/DAD_NAME>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride None
  PlsqlDatabaseUsername         scott
  PlsqlDatabasePassword         tiger
  PlsqlDatabaseConnectString    orcl
  PlsqlAuthenticationMode       Basic
</Location>

When done, restart the HTTP_Server for the configuration to take effect:

opmnctl stopall
opmnctl startall

DAD Migration

Run Oracle's DAD Migration and Obfuscation Script, called dadTool.pl to migrate older style DAD's stored in wdbsvr.app (9i) to dads.conf (10g).

To get you started, run:

/usr/bin/perl dadTool.pl -h

Programming

Standard Oracle PL/SQL programs can be extended to MOD_PLSQL programs. Programming is done in PL/SQL using the following set of packaged procedures:

• HTP - Hypertext Procedures
• HTF - Hypertext Functions
• OWA_UTIL - Oracle Web Agent Utilities
• OWA_COOKIE - Send end retrieve Web Browser Cookies
• Etc.

Example PL/SQL procedure:

CREATE OR REPLACE PROCEDURE HelloWorld AS
BEGIN
	htp.htitle('My first dynamic Web page');
	htp.print('Hello world');
	htp.line;
END HelloWorld;
/

To run this example you would typically provide an URL like this to your Web Browser:

http://your.host.name:port/pls/DAD_NAME/HelloWorld

In the above example, DAD_NAME is your configured DAD name and HelloWorld is the procedure's name.

Also see

• Mod_plsql FAQ - Frequently asked questions
• PSP - PL/SQL Server Pages
• APEX - Oracle Application Express

External Links

• Download mod_owa - Open source Apache PL/SQL Gateway Module
• The Unofficial Oracle Web/Application Server Site