Mod plsql

From Oracle FAQ
Jump to: navigation, search

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[edit]

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[edit]

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[edit]

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

Oracle 9i[edit]

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[edit]

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[edit]

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[edit]

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[edit]

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

External Links[edit]