mod_plsql

 

Overview

MOD_PLSQL is an Apache module extension written by Oracle Corporation to render dynamic web pages from standard Oracle PL/SQL code. MOD_PLSQL was formerly called the Oracle PL/SQL Cartridge and OWA (Oracle Web Agent). 

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

By default MOD_PLSQL is started when the Oracle HTTP Server is started.

Configuration parameters and log files are stored in under $ORACLE_HOME/Apache/modplsql

Here is a sample report

 

Configuring MOD_PLSQL

  1. Set your environment. For example:

    ORACLE_SID=devl
    ORACLE_BASE=/oracle/admin
    ORACLE_HOME=/oracle/product/9.2.0.1
    LD_LIBRARY_PATH=/oracle/product/9.2.0.1/lib
    TNS_ADMIN=/oracle/product/9.2.0.1/network/admin
    CLASSPATH=/oracle/product/9.2.0.1/jdbc/lib/classes12.jar
    PATH=/usr/sbin:/usr/bin:/usr/local/bin:/oracle/product/9.2.0.1/bin

  2. Start Oracle-Apache server:

    cd $ORACLE_HOME/Apache/Apache
    bin/apachectl start

  3. Start a browser and navigate to the MOD_PLSQL Gateway Configuration Menu at http://hostname:port.

    The port number, and other configuration information, is set in:

    $ORACLE_HOME/Apache/Apache/conf/httpd.conf"

  4. Click on "Gateway Database Access Descriptor Settings".

  5. Select one of the options to add a new 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)

 

Programming

Standard Oracle PL/SQL programs can be extended to include MOD_PLSQL. The following set of packaged procedures are available:

Package Description
HTP Hypertext Procedures
HTF Hypertext Functions
OWA_UTIL Oracle Web Agent Utilities
OWA_COOKIE Send/retrieve Web Browser Cookies

Here is an example program which displays the contents of a table: 

create or replace PROCEDURE show_table (pis_table_name in varchar2)  AS 
 BEGIN NULL;
htp.prn('
');
htp.prn('
<HTML><title>Show Contents of table (Complete Dump)</title></HEAD>
<BODY>
');

  declare
    dummy boolean;
  begin
    dummy := owa_util.tableprint(pis_table_name,'border');
  end;

htp.prn('
</BODY>
</HTML>      
');
 END;
/

Then pull up the table using the URL:

http://hostname/pls/SID/show_table?pis_table_name=TABLENAME

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/pls/<DAD_NAME>/HelloWorld

 

Page Forward and Backwards

The following procedure will allow a user to page through a table: 

set escape on;
set scan off;

CREATE OR REPLACE PROCEDURE tprint (
   xtable IN VARCHAR2,
   i_page_num IN VARCHAR2 DEFAULT '1'
   )
IS
   
   more_rows BOOLEAN;

   cur_page NUMBER := TO_NUMBER (i_page_num);
   
   min_row NUMBER;
   max_row NUMBER;
   
   i_num_rows CONSTANT NUMBER := 20;

BEGIN
   min_row :=  (cur_page - 1) * i_num_rows + 1;
   max_row := min_row + i_num_rows - 1;

   more_rows :=
      OWA_UTIL.tableprint (
         ctable       => 'invoice',
         cattributes  => 'border=1',
         ntable_type  => OWA_UTIL.html_table,
         nrow_min     => min_row,
         nrow_max     => max_row
      );

   
   IF cur_page > 1
   THEN
      HTP.anchor (
         'tprint?xtable=' || xtable || '&i_page_num=' ||  (cur_page - 1),
         'Previous'
      );
   END IF;

  
   IF more_rows
   THEN
      HTP.anchor (
         'tprint?xtable=' || xtable || '&i_page_num=' ||  (cur_page + 1),
         'Next'
      );
   END IF;

END;
/
commit;

exit;

 

HTTP COOKIE

Cookies allow any site to store information on a WEB Browser's hard disk (cookie.txt file). This information is sent back to the originating site whenever you access it again.

Look at this code example: 

owa_util.mime_header ('text/html', FALSE);
owa_cookie.send (cuid, xsession_id, sysdate+1);
owa_util.http_header_close;

 

Reloading

Yes, use the OWA_UTIL.REDIRECT_URL function. Alternitavely, look at the code example below: 

<HTML>
<HEAD>
<META HTTP-EQUIV="REFRESH" CONTENT="3; URL=any_valid_url">
<TITLE>Stay tuned!  3 Seconds until relocation</TITLE>
...
</HTML>

 

Multi-User Updates

Because the Web is stateless, there is no way to lock data between a SELECT and an UPDATE initiated from a Web Browser. One workaround is to let the procedure that display the data for updating also store the data values in hidden fields. Eg: 

   
for c1 in (select rowid, a.* from emp a) loop
    htp.FormHidden('the_rowid', c1.rowid);
    htp.Print('Enter new Employee Name:');
    htp.FormHidden('old_ename', c1.ename);
    htp.FormText('new_ename', c1.ename);
end loop;
The update procedure can now compare the hidden values in the form with the current table values before allowing the update to continue. Eg: 

   
UPDATE emp SET ename = new_ename
WHERE rowid = the_rowid
AND ename = old_ename;

if (SQL%ROWCOUNT = 0) then
  htp.print('Someone else changed this row, please re-query before updating.');
else
  htp.print('1 row updated.');
end if;

 

CGI vs. WRB

The Oracle Web Request Broker (WRB) is faster and more scalable than the CGI-BIN program OWA, but can only be used with certain Web servers. CGI programs are spawned off each time a HTTP request is made to it while the WRB will only start new brokers if the workload increase.

Note that the OWS-BIN OWA program's configuration parameters are stored in the SV*.CFG file while the WRB OWA is configured from SV*.APP.

 

Authentication File

One can keep authentication information in a file separate from the sv<server>.cfg file. In this case the following information in the sv<server>.cfg file... 

************
[Security]
Basic {
(Users)
users: passwords
(Groups)
groups: users  <-----   Single group should not exceed 200 users
(Realms)
realms: groups <-----   Be sure to include all groups
}
;
[Protection]
/secret-dir/         Basic(Realm)
************

could be replaced with.... 

************
[Security]
Basic @/path/to/user/authentication/file
;
[Protection]
/secret-dir/         Basic(Realm)
************

    The file referred to above should contain the following information...

************
(Users)
users: passwords
(Groups)
groups: users  <-----   Single group should not exceed 200 users
(Realms)
realms: groups  <-----   Be sure to include all groups
************

 

How does one program using the WRB API?

Download and look at the following working source code example:

OWA2 is a replacement for the Oracle PL/SQL Web Request Broker Cartridge. Other than Oracle's PL/SQL Cartridge, OWA2 :

  • Can call a user definable PL/SQL function to do authentication
  • Can stream multiple IMAGES concurrently from a database table to Web Browsers
  • Send simple mail messages
  • Add parameters to enable/disable timing, logging, database tracing, etc.
  • Keep database sessions open for performance reasons
It was written, compiled and tested on a SUN6000E running Solaris 2.5, Oracle 7.3 and the Oracle WebServer V2.0.3. If you make any changes to OWA2 or port it to a different environment, please mail the source code back to us.

If you find OWA2 useful, PLEASE tell me!!!

 

Can one store and retrieve images from an Oracle table?

Sure you can, consider the following:

  • Oracle freely distributes a few unsupported CGI utilities that can do just that. Ask them for their IMGLOAD/ OWAI/ OWAUP utilities.

  • If you want to stream multiple IMAGES concurrently from a database table to Web Browsers look at OWA2.

 

I've lost the Web Server Administrator's password. What can one do?

The Oracle WebServer Administrator's userid and password can be found in your $ORACLE_HOME/ows21/admin/svadmin.cfg file. The password is not encrypted!!!

 

Why does one get "Requested URL was not found on this server"?

Symptom:
When attempting to access URLs such as:

    http://host:port/ows-bin/service/owa/proc
the server responds with "Requested URL was not found on this server".

Causes:

  • This is due to there not being a mapping in the service listener's Virtual Directory Mappings configuration, of the form: /home/oracle/ows2/bin/ CN /ows-bin/service/ so the "owa" program isn't found.

  • You're accessing a directory, /xxxx/, and the directory isn't readable by the Web Server process.

  • The Web Server is configured to not generate directory listings automatically, and there isn't an index.html file (or whatever you've picked as your index file name).

Fix:

  • Create the virtual directory mapping, halt the listener, and restart it.

  • Check and fix the directory permissions. Note also the directory rescanning issue below.

  • Make sure that either the Web Server will create indices itself, or that there is a readable index file under the name you've specified. If altering the server configuration, halt and restart the server. If changing file ownership or permissions, note the directory rescanning issue below.

 

Why does one get "Request failed. We were unable to process your request at this time. Please try again later"?

Symptom:

When attempting to access URLs such as:

http://host:port/ows-bin/service/owa/proc

The server responds with "Request failed. We were unable to process your request at this time. Please try again later."

Cause:

  • The listener can't read the configuration file owa.cfg.
  • The listener is attempting to invoke the PL/SQL procedure named, but can't, for some reason.

Fix:

  • Make sure that the file /home/oracle/owa2/admin/owa.cfg (or whatever is the moral equivalent on your system) is readable by the user running the Web Server.

    • Check that the user for the listener has the permissions to invoke such functions: do "exec tw.ping" in sqlplus. There won't be any output, but it should say that it ran ok. If necessary, try reloading the stored procedures into the database.

    • Make sure that the names of the input fields in the form match up exactly with the names of the parameters in the script. There must be a one-to-one mapping.

    • If you've got multiple-value fields, such as SELECTs, make sure they're of the right type (defined in the OWA_UTIL package), and that there's an extra value supplied by a hidden field to force at least one value to be selected.

  • If all else fails, the procedure is probably raising an exception. Put OTHERS exception handlers around everything and call barf(errstr('procedure name')), or the moral equivalent on your system.

 

I've change the permissions of my script to make it readable and executable, but the webserver hasn't noticed. Why is this?

Symptom: webserver doesn't notice file permissions have changed.

The webserver caches the modification time of the directory, and of all the files within it. If a URL is not available because of permissions problems, and you fix that, the webserver will continue to say that the URL is not available, because the directory modification date hasn't changed, so it doesn't bother to check the file itself. Renaming the file to something and back again will make the webserver see the change. This is configurable, however. On the Web Listener configuration page, there is a parameter which determines how long the Web Server will go without re-scanning a directory that appears to have not changed yet, including the ability to never rescan unless the modification date changes.

 

Links

Download mod_owa Open source Apache PL/SQL Gateway Module
Unofficial Oracle Web/Application Server Site
OWSKiller Replace OWS with Java servlets for free
MOWI WWW to Oracle Interface
SWOOP WWW/ORACLE Connectivity Software
Oracle XML integration
WebAlchemy Tool for translating HTML to PL/SQL

 

Config Files

 

apachectl

The apachectl script is used to start and stop Oracle HTTP Server on Solaris. It is located at:

$ORACLE_HOME/Apache/Apache/bin/apachectl

Inside this file, there are three parameters that affect the PL/SQL Gateway:

ORACLE_HOME the Oracle Home in which the PL/SQL Gateway runs. Default: $ORACLE_HOME/
LD_LIBRARY_PATH the Oracle libraries needed by the PL/SQL Gateway. This should point to an Oracle 8.1.7 installation. This parameter is for Solaris only.
Default: $ORACLE_HOME/lib
WV_GATEWAY_CFG the PL/SQL Gateway configuration file.
Default on Solaris: $ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app
Default on Windows NT <APACHE_HOME>/modplsql/cfg/wdbsvr.app

 

httpd.conf

This configuration file defines the behavior of Oracle HTTP Server (powered by Apache). You can set your port number as well as other server settings. It is located at:

$ORACLE_HOME/Apache/Apache/conf/httpd.conf"

 

plsql.conf

This configuration file describes settings for the the PL/SQL Gateway module. It is located at:

$ORACLE_HOME/Apache/modplsql/cfg/plsql.conf
There settings are configurable:

LoadModule plsql_module MOD_PATH Location of the PL/SQL Gateway module. Default is:

$ORACLE_HOME/Apache/modplsql/bin/modplsql.so
Location MOUNT_PATH Prefix in the URL for which the PL/SQL Gateway is invoked. Default is: /pls

 

wdbsvr.app

This configuration file describes settings for the the PL/SQL Gateway module. It is located at:

$ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app
This is the main PL/SQL Gateway configuration file. It contains all the DAD information. Please do not edit this file directly. Use the PL/SQL Gateway configuration page, which you can access through your browser as shown below.

 

Accessing the PL/SQL Gateway configuration page

To access to the PL/SQL Gateway configuration page, enter the following URL in your Web browser: 

http://hostname:port/pls/DAD/admin_path/gateway.htm

Configuration settings are protected by the administration security settings. The web administration page can only be invoked by those users whose user names appear in the Administrators setting of the configuration file. See "Configuring the PL/SQL Gateway" for more information.

 

plsql.conf configuration file

The Oracle HTTP Listener configuration file includes the modplsql configuration file plsql.conf. The contents of plsql.conf are: 
# 
# Directives added for the PL/SQL Gateway 
# 
LoadModule plsql_module %APACHE_HOME%/modplsql/bin/modplsql.so 

# 
# Enable handling of all virtual paths beginning with "/pls" by mod-plsql 
# 
<Location /pls> 
  SetHandler pls_handler 
  Order deny,allow 
  Allow from all 
</Location>

 

Starting and stopping the Oracle HTTP Server Listener

To start the Apache listener, type: 
    $ORACLE_HOME/Apache/Apache/bin/httpdsctl start
To start the Apache listener with SSL. support, type: 
    $ORACLE_HOME/Apache/Apache/bin/httpdsctl startssl
To stop the Apache listener, type: 
    $ORACLE_HOME/Apache/Apache/bin/httpdsctl stop

 

Home