Mod plsql FAQ

From Oracle FAQ
⧼orafaq-jumptonavigation⧽⧼orafaq-jumptosearch⧽

Oracle mod_plsql FAQ:

How does one start and stop MOD_PLSQL?

By default MOD_PLSQL is started when the Oracle HTTP Server (Apache) is started. There are no special commands to stop and start it.

What is MOD_PLSQL and what can one use it for?

MOD_PLSQL is an Apache (Web Server) extension module that allows one to create dynamic web pages from PL/SQL packages and stored procedures. It is ideal for developing fast and flexible applications that can run on the Internet or an Intranet. MOD_PLSQL was formerly called the Oracle PL/SQL Cartridge and OWA (Oracle Web Agent).

For more information about the Apache server, see the HTTP Server FAQ. More information about PL/SQL can be obtained from the PL/SQL FAQ.

The Mod_PLSQL Architecture: 

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

How does one configure MOD_PLSQL?

Start a Web Browser like Netscape Navigator and 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.

Can I install MOD_PLSQL on a non-Apache server?

No, you cannot. MOD_PL/SQL is an Apache add-on module that was specifically written for Apache. Luckily Apache is available on almost all operating systems including Windows, Linux, etc.

How does one program using MOD_PLSQL?

Standard Oracle PL/SQL programs can be extended to include MOD_PLSQL The Oracle Web Agent extends the Common Gateway Interface (CGI) to Oracle PL/SQL stored procedures. 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/pls/<DAD_NAME>/HelloWorld

How does one use HTTP COOKIEs?

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;

Can a Web page be refreshed/reloaded after a given interval?

Yes, use the OWA_UTIL.REDIRECT_URL function. Alternitively, 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>

How can one page forward and backwards through a table?

Externalize ROWNUM by implementing queries like this: 

SELECT ...
FROM  (SELECT ROWNUM rnum, ... FROM ...)
WHERE rnum BETWEEN :low AND :high
  AND rownum < (:high - :low + 1);

where :low and :high are dynamically generated values depending on which result page the user is viewing. Typically, they are used to show "Next 15 matches", "Previous 15 matches" links at the bottom of each page.

How to do multi-user updates using the Oracle WebServer?

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;

Can one use Oracle Designer to generate Web applications?

Yes, Oracle Designer (CASE) V1.2A and above includes a Web Server Generator that can generate QUERY-ONLY applications. From Designer/2000 1.3W one can generate applications that can do Insert, Update and Delete operations. 1.3W also generates JavaScript (NOT JAVA!) code to do client side validation!!!

Can one use a different Web Server from the one included by Oracle?

With the Oracle Web Server version 1.0 you have a CGI-BIN program called OWA that works with any HTTP webserver.

With version 2.0 you have a CGI-BIN program called OWA that works with any webserver, in addition to a Web Request Broker (WRB) OWA cartridge. The WRB cartridge with 2.0 works with the Oracle Web Server (with 2.0 you can still use the web agent with any server, but you can't use the WRB component with any other server).

With version 2.1 you have the CGI-BIN program... in addition to a Web Request Broker OWA cartridge. The WRB cartridge with 2.1 works with the Oracle Web Server and/or the Netscape Fasttrack Server.

With version 3.0 you will have the CGI-BIN program... the cartridge... which works with Oracle Web Server, Netscape fasttrack/enterprise/commerce server, MSIIS, and perhaps others.

Should one access Oracle via the CGI interface or the 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.

We want to do some more advanced authentication. What are my options?

Mainly, the best we've come up with is:

  • Use Netscape-style cookies in conjunction with some sort of login form. The PL/SQL procedure that processes the form returns a cookie to the browser which the browser will hold on to and include with future requests, until you send it a new cookie, or the browser session ends. This avoids having to have user identifiers in the URL.
  • Look at OWA2, a replacement for Oracle's PL/SQL Cartridge. With OWA2 you can control authentication from a PL/SQL function in your database.

I'm storing userids in a table. How does one encrypt the passwords?

You can use the DBMS_OBFUSCATION package to encrypt and decript content:

  • dbms_obfuscation_toolkit.DESEncrypt (Encrypt)
  • dbms_obfuscation_toolkit.DESDecrypt (Decrypt)

Don't store the password - encrypted or decrypted. Instead store a md5-hashsum of the password, and check a computed md5-value with the stored value

Are there alternatives to keeping authentication information in the configuration 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[/list]

It was written, compiled and tested on a SUN 6000E 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 let us know as well.

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 (see above).

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!!!

Retrieved from "http://www.orafaq.com/wiki/index.php?title=Mod_plsql_FAQ&oldid=15321"