DataCraft Paper: Building Your First WebServer Application: A Tutorial

DataCraft -> Publications -> Oracle WebServer 2.0 Tutorial

Building Your First WebServer Application: A Tutorial

February 1997
William L. Pribyl
DataCraft, Inc.

Introduction

True to web culture, this paper was written in a "just-in-time" fashion. While every effort at accuracy has been made, please visit http://www.datacraft.com/ for any corrections or late additions.

After spending months building a moderate-size application with Oracle's WebServer 2.0 and 2.1, it seems to me there are a lot of things Oracle left out of the manuals. Not secrets, really, just omissions that would have made life easier if they'd been in there. The most frustrating part was getting started. Installation was easy enough - as long as you follow Oracle's instructions to the letter, and review all the log files - but after that I was clueless. Yes, I knew PL/SQL, plus I knew a fair amount about HTML and web servers in general, but there was a large gray muddle between the two that the docs didn't wipe away. (And in case you're wondering, I didn't have time to go to class, I had a deadline.) This paper contains the information I wish I had known when I was just starting.

The application on which I worked was almost all PL/SQL - no Java, no custom cartridges, no SSL security, no LiveHTML. If you want to see the finished product, there is a slightly crippled demonstration of it at http://oracle.raid.com/imagebase (or try http://208.206.97.20/imagebase). The web site is only for demonstration, not for download of the application, and without a privileged userid no updates are allowed. At any rate, we used Designer/2000 as the starting point to generate the PL/SQL, which generated some functional (and rather ugly) read-only web pages, to which we added insert/update/delete features later. While others on the team worked on cosmetic issues and wrote some oraperl CGI scripts to handle calls to various Unix utilities and programs, this paper concentrates on PL/SQL, which I believe to be easiest, and probably the most common, WebServer application development technique (and will be until WebForms is widely available). If you just want information on using cartridges or other approaches, you can skip this paper.

At the time of this writing (February 1997), all of my experience has been with WebServer version 2.1 and its predecessors. WebServer 3.0 was in beta, and although Oracle was kind enough to let me fill out their web-based form requesting it, they replied with an automatic rejection saying only that the beta program was closed. I know that version 3.0 adds a type of "transaction" support which may change your approach to updates, but I suspect that the fundamental PL/SQL programming model has not changed.

Acknowledgments

I am grateful to the kind folks at GeneSys LLC, an integrator of high performance computing systems for the print and new media industries, for their assistance in bringing a WebServer solution to reality, as well as for their undying confidence in my Oracle skills, not to mention their willingness to take late-night phone calls. Thanks are also due Thomas Kyte/Oracle Government, who through his many web postings and emails helped me understand what I was doing; and the ORAWEB-L Internet mailing list community.

Before You Start

What Version Should I Use?

I know a lot of people have this question: Why buy WebServer 2.x when 1.0 is free? First off, an (unconfirmed) statement from an Oracles salesperson indicated that Version 2.0 would be bundled free with the Oracle server once Version 3.0 goes into production. By the time you read this, this change may be old news.

Hoping for a definitive statement from Oracle, I asked the WebServer product manager to provide some documentation on the exact differences between the products, as well as a comment on where Oracle was headed with respect to bundling of functionality with the core server. So far I’ve waited a week; no reply yet. Not to be dissuaded, I tried other avenues to get this information.

In 1.0, there is a CGI program for connecting to the database. WebServer 2.x adds support for "cartridges" and includes the Web Request Broker cartridge which an improvement (how much of an improvement is something of a debate) over CGI. Version 2.x also includes SSL, which is essential if you need secure connections; a Java virtual machine for server-side Java programs which can call PL/SQL; and LiveHTML for Server-Side Includes. While Version 2.0 allows you to use Netscape Fasttrack Server as the web server, Version 2.1 has an ability to use more third-party web servers such as Netscape Commerce Server and Microsoft IIS.

You might think that you can start practicing with 1.0 and upgrade to 2.x later. True enough, but each new version also includes a variety of new and enhanced PL/SQL utilities in the toolkit. Also, later versions crash less frequently. Personally, I would hesitate to put a V1 solution into wide production use when V2 is available. It’s fine for demonstrations of the core functionality, though.

According to Barry Johnson, a contributor on the ORAWEB-L Internet mailing list, new features for 3.0 are mainly: Intercartrdige Exchange (ICX); improved LiveHTML, because of ICX; a Perl cartridge; XA-compliant Transaction Management; CORBA-compliant WRB/ORB; and database-based authentication. Maybe next year I’ll be able to write a paper on how those things will help your application.

Skills Assumed

This paper assumes you’ve created at least one PL/SQL package and have created a web page of some sort. Much as I’d like to, I don't have the space to teach these fundamentals here. To be productive in WebServer PL/SQL, you’ll want to know PL/SQL version 2 at least well enough to write packages, procedures, and functions; you should have a thorough knowledge of exception handling; and you would benefit from understanding the PL/SQL "table" data type. You’ll also want to have a bit of HTML knowledge, some of which you can pick up as you go. Something people often ask about is "good books about HTML;" my favorite is O’Reilly & Associates’ HTML: The Definitive Guide by Chuck Musciano & Bill Kennedy, although it is getting a bit dated, and it does not treat CGI programming, both of which are not a big problem if you’re a WebServer neophyte.

The Bare Minimum about Installation and Configuration

If you’ve ever installed Oracle on a Unix box, you know there is a root.sh script that gets generated and which you must run as root after performing the install with orainst. If you’ve installed WebServer along with the Oracle server, then (assuming your WebServer product directory is ows2) root.sh will call the script $ORACLE_HOME/ows2/install/fmods.sh, which in turn will set some file permissions and then attempt to start a special WebServer administrative listener on using a command of the form "wlctl21 start admin $ORACLE_HOME". If you connect with a web browser to the URL prompted by fmods.sh, probably http://myhost.mydomain:port/ows-abin/boot), one of the first things it does is ask for the username/password combination which you supplied to prompts during orainst. If you can’t remember it, just look in the file $ORACLE_HOME/ows2/svadmin.cfg – it’s there in plain, gloriously unprotected text!

Then you will confront a series of screens that lets you configure your first listener and PL/SQL agent (previously known as the "web agent" or "Oracle web agent" and abbreviated OWA). The listener consists of one or more background processes that respond to HTTP requests; the PL/SQL agent is the part of WebServer that connects to the Oracle RDBMS using the username and password that you designate. You can decide whether the PL/SQL agent connects via CGI, which I think largely exists for backward compatibility, or as a cartridge, in this case called the OWA cartridge, for the Web Request Broker. Although the details of the architecture are outside this paper’s scope, suffice it to say that the WRB/cartridge approach is the more efficient alternative and has been highly touted by Oracle.

Creating a PL/SQL Agent to and Configuring it to Use the Web Request Broker

If you, like me, are not too keen on using Oracle’s defaults, one of the first things you will want to do is set up your own Database Connection Descriptor (DCD) for you app rather than using those created during the installation. The DCD is a set of information that tells Oracle connection specifics such as username, password, connect string (TNS service name), and log file. This information, like almost all WebServer configuration data, is stored in an ASCII file, but it is modifiable from a web browser through the WebServer Manager screens. The agent connect data can be found in $ORACLE_HOME/ows2/admin/owa.cfg,

There are at least two steps here: first, setting up the Oracle user and connection description (DCD); and second, telling the WRB about it.

How to do it. First, choose a name for the Oracle user and for the corresponding DCD. In our application, we chose to make the Oracle userid invisible to the end user, so the choice of userid was pretty arbitrary. I suggest making it a short name, though, because you may be typing it a lot during application development. The DCD name will show up in URLs, so you may want to choose a concise name that your users can remember and/or type when needed. For this paper, we’ll assume you are want a DCD name of "myapp" and that the Oracle userid will be mywebuser.

We’ll also assume that you want to go through the OWA cartridge rather than CGI, that the administrative listener is running on its default port (8888), and that you want to be able to connect to your application via ports 80 and 8888. I’m including a lot of detail in the steps below because this is one of the areas I got massively confused on. Now, thankfully, you won’t have to end up like me.

Creating the DCD for the PL/SQL Agent

1. From a web browser, connect to http://myhost.mydomain:8888/. You should see the Oracle WebServer starting page. If you don’t, restart the administrative listener by logging in as oracle and executing the command "wlctl21 start admin" (or substitute your version of wlctl).

2. Click on WebServer manager.

3. Click on PL/SQL Agent. (This address is http://myhost.mydomain:8888/ows-abin/agadmin)

4. Click on Create New DCD.

5. Enter the following:

DCD Name: myapp

PL/SQL Agent Database User: mywebuser

Identified by Password

ORACLE_HOME should already be filled in

ORACLE_SID: your SID

Authorized ports: 80, 8888

Log file directory: whatever, or use default

Check "Install WebServer Developer’s Toolkit PL/SQL packages"

6. Click on "Submit New Service." Eventually – and this should take a few minutes – you will see a Success or Failure message. This takes a while because it is creating the built-in WebServer packages for the given user, mywebuser. Even if it says success, check the log file indicated in the message, and ensure that you didn’t get any errors like "out of rollback segment space."

Configuring the Agent to use the WRB

7. Navigate back to the WebServer Manager page, http://myhost.mydomain:8888/ows-adoc/Intro.html

8. Click on Web Request Broker

9. Click on Modify

10. Scroll down to Applications and Directories

11. Add the following. This will provide the physical-to-logical mapping you want. The "/owa" is optional in the virtual path, but I like to put it there so that if you have another category of pages you can create another virtual subdirectory, for example, /myapp/image.

Virtual Path	/myapp/owa
App.	OWA
Physical Path	/u01/app/oracle/product/7.3.2/ows2/bin (or your physical path to this exact directory)

12. Click on Modify Web Configuration

13. Now you have to restart the listener. Go to the Listener Administration page at http://myhost.mydomain:8888/ows-abin/wladmin.

14. Click on STOP

15. Click on START

That ought to do it. To confirm that everything worked, you will want to write a quick PL/SQL program to display something from the database. See below for instructions.
About the Predefined Oracle packages

In step 6 above, you caused a script to run which built a number of packages needed by the PL/SQL agent. This script was probably in the file $ORACLE_HOME/ows2/admin/owains.sql. If you have a number of PL/SQL agents, you will probably want to install only a single set of these packages and sharing access to them by granting execute privilege to them and creating synonyms. I think this approach is described somewhere in Oracle’s online documentation, but if not, just do a select object_name from user_objects where object_type = 'PACKAGE' while logged on as the first agent, and run the grants and synonyms against this list.

These packages are extended and improved from one release of the WebServer to the next. I have had success in back-porting them from 2.1 to 2.0 but I doubt whether doing so is supported by Oracle.

The Typical URL

You are going to be setting up applications that will be run by pointing a web browser to a specified URL. It’s important to understand the Oracle WebServer-specific components of the URL that follow the slash after the hostname. Look at an example:

http://www.host.com/media/owa/search..listall?searchfor=blue+meanies&show=20

This URL will cause the listener to look for a virtual mapping of media/owa which includes the DCD name, media, and the virtual path, owa, which could be associated with either the CGI or the cartridge OWA implementation. Here, search is the PL/SQL package name, and listall is a procedure. This URL will pass two arguments to the procedure, searchfor, with a value of "blue meanies" (in an URL, the + indicates a space), and show, with a value of 20.

While some web server configurations allow the user to browse available files in a directory, there is no way that I know of to discover the names of existing packages and procedures using variations on this URL.

Your First Web Page: "Hello, Web!"

Via Static HTML files

Although not a horribly interesting feature, WebServer does allow you to display static text via conventional ".html" text files. As with other web servers such as Apache or Netscape, you have to map the actual on-disk directory path to a "virtual" path that will be part of the URL after the host name. To set up a path for a static HTML file, from the WebServer home page, go to WebServer Manager, then Oracle Web Listener, and click on Configure for the currently running listener. Navigate your way down to the "Directory Mappings" section and put in something like this.

File-system directory:	/u01/app/myapp/www/
Flag:	NR
Virtual Directory:	/myapp/pages/

<HTML>

<HEAD>

<TITLE>My First Page</TITLE>

</HEAD>

<BODY>

<H1>Hello, Web!</H1>

</BODY>

</HTML>

…then, assuming it’s running on port 80, you can point a browser to it using: http://myhost.mydomain/myapp/pages/myhello.html

Via PL/SQL

To get WebServer to generate and display this page via PL/SQL, you will create a simple PL/SQL program. It’s easiest to create this as the Oracle user who is associated with the DCD. (It could be created as another user who has privileges to execute the htp package, as long as the DCD user has been granted execute privilege.)

First we’ll look at an "unpackaged" version of this procedure which uses only one of the built-ins.

CREATE OR REPLACE PROCEDURE myhello AS

BEGIN

htp.print('<HTML>

<HEAD>

<TITLE>My First Page</TITLE>

</HEAD>

<BODY BGCOLOR="white">

<H1>Hello, Web!</H1>

</BODY>

</HTML>');

END;

CREATE OR REPLACE PROCEDURE myhello AS

BEGIN

htp.htmlOpen;

htp.headOpen;

htp.title('My First Page');

htp.headClose;

htp.bodyOpen( cattributes => 'BGCOLOR="white"');

htp.header(1, 'Hello, Web!');

htp.bodyClose;

htp.htmlClose;

END;

I don’t want to try to document the built-ins in this paper; most of the documentation you need is online: follow the link from your installation’s WebServer home page to the "Online Docs," then follow the link to "Application Development," then click on "PL/SQL Web Toolkit Reference." Or just type in the URL: http://myhost.mydomain:port/ows-adoc/psqlwtlk.htm.

There are advantages and disadvantages to using the built-ins. I personally don’t agree with Oracle’s assertion that using the built-ins will have to save you from learning HTML. The reality is that you will want some HTML expertise to know which built-ins to call, and with what arguments. Also, while the built-ins might insulate your programs from changes in the HTML standard as Oracle claims, I do not consider this a huge benefit. The benefit I see from the built-ins is that they can allow for more compact, readable code. It’s also easy to write your own built-ins to meet this objective.

Consider the PL/SQL example above. You can see how you could get tired of coding all the open/close calls for the typical page, so you might want to come up with standard PL/SQL modules that print a typical header and footer. While we’re at it, let’s go ahead and put it in a package:

CREATE OR REPLACE PACKAGE hutil AS

PROCEDURE hOpen( p_title IN VARCHAR2 DEFAULT NULL

,p_bgcolor IN VARCHAR2 DEFAULT '"white"' );

PROCEDURE hClose;

END;

CREATE OR REPLACE PACKAGE BODY hutil AS

PROCEDURE hOpen( p_title in varchar2

,p_bgcolor in varchar2 ) IS

BEGIN

htp.htmlOpen;

htp.headOpen;

htp.title( p_title );

htp.headClose;

htp.bodyOpen( cattributes=> 'BGCOLOR=' || p_bgcolor );

END;

PROCEDURE hClose IS

BEGIN

htp.bodyClose;

htp.htmlClose;

END;

CREATE OR REPLACE PROCEDURE myhello AS

BEGIN

hutil.hOpen('My First Page');

htp.header(1, 'Hello, Web!');

hutil.hClose;

END;

You can see how bundling these calls into packages can save a lot of code-writing. Using this technique, you could easily put, say, a standard footer with a navigation bar on all your pages.

As a usage note, there are two versions of most of the modules: a procedure and a function. For example, there is an htp.bold procedure and an htf.bold function which achieve the same effect; the difference is that the function generates the HTML as its return value, whereas the procedure sends it on through to the web browser.

Displaying Data from the Database

Printing a single value. It requires no great leap of imagination to extend the PL/SQL above to display data from a table. To display some simple scalar data such as the maximum salary from the archetypal EMP table, for example, you could use the following program:

CREATE OR REPLACE PROCEDURE show_max_sal as

maxSal scott.emp.sal%TYPE;

cursor salCur IS SELECT MAX(sal)

FROM scott.emp;

noEmp exception;

BEGIN

hutil.hOpen( 'Salary Max');

OPEN salCur;

FETCH salCur INTO maxSal;

if salCur%NOTFOUND then

raise noEmp;

end if;

CLOSE salCur;

htp.print('The highest salary is: ' || maxSal);

hutil.hClose;

EXCEPTION

WHEN noEmp THEN

htp.print('Warning: There are no employees.');

hutil.hClose;

END;

Printing a table.

CREATE OR REPLACE PROCEDURE empPrint AS

BEGIN

hutil.hOpen('Employees');

htp.tableOpen('BORDER'); -- <TABLE BORDER>

htp.tableRowOpen; -- <TR>

htp.tableHeader('Emp#'); -- <TH>Emp#</TH>

htp.tableHeader('Name'); -- <TH>Name</TH>

htp.tableHeader('Sal'); -- <TH>Sal</TH>

htp.tableRowClose; -- </TR>

FOR theEmp IN (SELECT empno, ename, sal

FROM scott.emp ORDER BY empno) LOOP

htp.tableRowOpen; -- <TR>

htp.tableData( theEmp.empno ); -- <TD>(employee # from table)</TD>

htp.tableData( theEmp.ename ); -- ditto for ename

htp.tableData( theEmp.sal ); -- ditto for sal

htp.tableRowClose; -- </TR>

END LOOP;

htp.tableClose; -- </TABLE>

hutil.hClose;

END;

Another method for printing an Oracle table in a HTML table is use the built-in owa_util.tableprint utility. Note: WebServer authority Thomas Kyte, a frequent poster to comp.databases.oracle.* newsgroups, does not recommend using tableprint, although I haven’t uncovered the details of why. He does mention that it won’t work properly if you have a single set of WebServer developer’s packages shared among the PL/SQL agents. Although I have not used tableprint much, I can say that you lose some formatting control with it, but it’s OK for quick-and-dirty prototypes.

Using a Dynamic Where-Clause to Filter Data...

Let’s add the ability to display only one department at a time. A convenient way of passing in a variable for use in the where clause is via the URL. We will create a page that shows all departments’ employees if there is no department number specified in the URL, or only those employees for a specified department. That is, http://myhost.mydomain/first/empPrint will show everyone, and http://myhost.mydomain/first/empPrint?p_deptno=20 will show employees from department 20.

Critical note: WebServer passes PL/SQL arguments by name, so the argument in the URL above, p_deptno, must exactly match the name of formal argument to the procedure empPrint, shown below. In addition, the formal argument to the procedure must be defaulted or you will get an error if you fail to supply it in the URL. This is critical to understand. Get in the habit early: default all parameters! Even if your routine is meaningless without a particular set of arguments, you should write error-checking code to handle missing parameters more gracefully than WebServer’s standard error-handling.

...With Static SQL

CREATE OR REPLACE PROCEDURE empPrint ( p_deptno IN VARCHAR2 DEFAULT '%' ) AS

BEGIN

hutil.hOpen('Employees');

htp.tableOpen('BORDER');

-- the table header stuff goes here…

FOR theEmp IN (SELECT empno, ename, sal FROM scott.emp

WHERE deptno like p_deptno

ORDER BY empno ) LOOP

htp.tableRowOpen;

htp.tableData( theEmp.empno );

htp.tableData(theEmp.ename );

htp.tableData(theEmp.sal );

htp.tableRowClose;

END LOOP;

htp.tableClose;

hutil.hClose;

END;

...With Dynamic SQL

CREATE OR REPLACE PROCEDURE empPrint ( p_deptno IN VARCHAR2 default NULL) AS

l_dcurs INTEGER; -- cursor id for dynamic SQL

l_theQuery VARCHAR2(512); -- variable to hold the query

l_undefined INTEGER; -- execute will return undefined value

l_rows_fetched INTEGER; -- result of fetch_rows function

l_empno NUMBER; -- local variable to hold table data

l_ename VARCHAR2(10);

l_sal NUMBER;

BEGIN

IF p_deptno IS NULL THEN

hutil.hOpen('All Employees');

htp.header(1, 'All Employees');

ELSE

hutil.hOpen('Employees in Department ' || p_deptno );

htp.header(1, 'Employees in Department ' || p_deptno);

END IF;

htp.tableOpen('BORDER');

-- the table header stuff goes here…

IF p_deptno IS NULL THEN

l_theQuery := 'select empno, ename, sal from scott.emp order by empno';

ELSE

l_theQuery := 'select empno, ename, sal from scott.emp '

|| 'where deptno=' || p_deptno

|| ' order by empno';

END IF;

-- These calls are standard fare for dynamic SQL. For more info,

-- look at $ORACLE_HOME/rdbms/admin/dbmssql.sql

l_dcurs := dbms_sql.open_cursor;

dbms_sql.parse( l_dcurs, l_theQuery, dbms_sql.v7);

dbms_sql.define_column( l_dcurs, 1, l_empno );

dbms_sql.define_column( l_dcurs, 2, l_ename, 10 );

dbms_sql.define_column( l_dcurs, 3, l_sal );

l_undefined := dbms_sql.execute( l_dcurs );

l_rows_fetched := dbms_sql.fetch_rows ( l_dcurs );

WHILE l_rows_fetched != 0

LOOP

dbms_sql.column_value( l_dcurs, 1, l_empno);

dbms_sql.column_value( l_dcurs, 2, l_ename);

dbms_sql.column_value( l_dcurs, 3, l_sal);

htp.tableRowOpen;

htp.tableData( l_empno );

htp.tableData( l_ename );

htp.tableData( l_sal );

htp.tableRowClose;

l_rows_fetched := dbms_sql.fetch_rows ( l_dcurs );

END LOOP;

dbms_sql.close_cursor ( l_dcurs );

htp.tableClose;

hutil.hClose;

END;

Adding Links

So far, this is pretty tame stuff for your average web surfer. Even if you add some means of allowing the user to easily modify the query with all kinds of variations in the where clause, this technique is useful perhaps for web-based reports but not much else. Let’s say that we want to add to our employee list the ability to click on an employee and bring up a detail page for that person. We’ll need two additions: the detail page, and the HTML anchor tag to call it. First let’s look at the needed changes to the empPrint page. In the interest of conserving space, look at only the changed code:

-- as above, but replace the line "htp.tableData( l_empno );"

-- with the following:

htp.tableData( htf.anchor( 'empDetail?p_empno=' || l_empno, l_empno ));

<TD><A HREF="empDetail?p_empno=7902">7902</A></TD>

CREATE OR REPLACE VIEW emp_view AS

SELECT '<A HREF="empDetail?p_empno=' || empno || '">' || empno || '</A>' empno

,empno empno_base, ename, sal

FROM emp;

CREATE OR REPLACE PROCEDURE empDetail ( p_empno IN VARCHAR2 DEFAULT null ) IS

CURSOR empCur IS SELECT * FROM scott.emp WHERE empno = p_empno;

empRec empCur%ROWTYPE;

no_args EXCEPTION;

no_emp EXCEPTION;

BEGIN

hUtil.hOpen('Employee Detail for ' || p_empno);

IF p_empno IS NULL then

RAISE no_args;

END IF;

OPEN empCur;

FETCH empCur INTO empRec;

IF empCur%NOTFOUND THEN

close empCur;

raise no_emp;

END IF;

CLOSE empCur;

htp.header(1, 'Employee ' || p_empNo );

htp.para;

htp.print( htf.bold( 'Name:' ) || empRec.ename || htf.br);

htp.print( htf.bold( 'Dept:' ) || empRec.deptno || htf.br);

--...etc. for the rest of the columns

hUtil.hClose;

EXCEPTION

WHEN no_args THEN

htp.print('Error: empDetail called with no arguments.');

hUtil.hClose;

WHEN no_emp THEN

htp.print('Error: Detail requested for unknown employee number.');

hUtil.hClose;

END;

HTML Forms

A Simple HTML Form to Search the Database

The basic idea of using an HTML form is that the values supplied by the user will get passed as parameters to the module specified in the ACTION attribute of the FORM tag. POST is the default method and is recommended for long argument lists.

<FORM ACTION="searchEmp" METHOD="POST">

Please enter the first few letters of the person's name:

<INPUT TYPE="TEXT" NAME="P_SEARCH" SIZE="20">

<INPUT TYPE="SUBMIT">

</FORM>

htp.formOpen('searchEmp');

htp.print('Please enter the first few letters of the person''s name:');

htp.formText('p_ename',20);

htp.formSubmit;

htp.formClose;

CREATE OR REPLACE PROCEDURE searchEmp ( p_ename IN VARCHAR2 DEFAULT NULL ) IS

l_empno scott.emp.empno%TYPE;

l_rows_unshown boolean;

BEGIN

-- handling a possibly null input parameter is important but is not

-- shown here (see earlier code for technique)

-- if only one matches, show the detail page, otherwise show a list

BEGIN

-- normally don't like implicit cursors but makes sense here because

-- we want to check for multiple records matching criteria

SELECT empno INTO l_empno

FROM scott.emp WHERE ename LIKE upper(p_ename) || '%';

empDetail ( l_empno );

EXCEPTION

WHEN too_many_rows THEN

hutil.hOpen('Employees Matching ' || p_ename);

-- because this tablePrint is based on the view defined earlier,

-- it provides a clickable means of clicking and drilling down

-- to the employee details

l_rows_unshown :=

owa_util.tablePrint( 'scott.emp_view'

,ccolumns => 'ename, empno, sal'

,cclauses => 'where ename like upper('''

|| p_ename || '%'')' );

hutil.hClose;

WHEN no_data_found THEN

null; -- handle appropriately

END;

A few things to know about passing parameters. First, all HTML form variables get passed as varchar2. Don’t try to use anything else in you PL/SQL formal argument lists, or you’ll get an error. Second, in the event that the HTML form contains multiple objects with the same name but different values (for example, a series of checkboxes, the total number of which cannot be pre-determined), the parameter will be passed to the module specified in the ACTION attribute as a PL/SQL table of type owa_util.ident_arr. I wanted to give an example of this feature, which turns out to be enormously useful, but space and time constraints require me to merely point you to an example in Oracle’s online documentation: http://myhost.mydomain:port/ows-adoc/plaex5.htm.

Drop-Down Lists

One of the features you will very quickly want to add to your fill-out forms is the ability to populate a drop-down list from data in a table. This is a definite area where using the built-in utility owa_util.listprint will save time & effort. Let’s say you want to generate a list of departments from which the user may choose a name, and that the processing module expects the department number as the value of the field. We want to default the drop-down to department 20, and also want a null/blank option.

owa_util.listprint( 'select deptno, dname,'

|| ' decode(deptno,20,''SELECTED'',null) from scott.dept'

|| ' union select to_number(null), '' '', null from dual order by 2'

,'P_DEPTNO',1,FALSE);

<SELECT NAME="P_DEPTNO" SIZE="1">

<OPTION value="">

<OPTION value="10">ACCOUNTING

<OPTION value="40">OPERATIONS

<OPTION SELECTED value="20">RESEARCH

<OPTION value="30">SALES

</SELECT>

The "Big Leap" into Read/Write

It’s not all that big a leap, really. You do have to keep in mind that there is no concept of a transaction in WebServer1.0, 2.0, or 2.1. Every database change is automatically committed unless you roll it back in the same "call" that made the change. Neither package nor transaction state is not preserved between page loads.

In the application that I helped build, which involved on-line ordering, we simply invented a concept of "order approval." The users can browse items for order at their leisure, make interim selections, modify and remove order selections to their hearts’ content. They can even shut down their browser and return the next day to resume putting items into their order. All the changes are committed to the database as soon as they make them, but until they press the "Process order" button, the order is not approved by the user, and it sits in a sort of pending queue. (Oracle’s white paper on transactions in WebServer 3.0, which also describes the shopping cart problem, interestingly does not acknowledge that a user might want to save their interim order and continue working on it later.)

When I started developing read/write forms, my first attempt treated creation of records as something separate from query. Another way to look at it, similar to the Oracle Forms 2.x - 4.x model, is to use the same screen for insert and for query. My final code employed the latter model, showing a screen with a number of fields and two buttons: Create and Query. The user can put in a new record or query an existing one. Pseudo code might look like this:

Show details screen with blank fields and two buttons: Create and Query

User fills out one or more fields

If user presses query, then

pass the field values to the show details page

Check in table to see if there is only one record; if so, then

display that record's data in the details screen

...Otherwise there are multiple records, so

display the records in a clickable list

If the user clicks on one, send the value of the primary key as a parameter to the details page

...Otherwise the user has pressed Create, so

Call the module that validates and insert the record with the data provided by the user

Display a confirmation page with two links: retrieve the record, or enter a new query

If the user clicks to retrieve the newly-created record, then

Bring up the details page, which has a Save button and a Delete button.

If the user presses the Save button, validate and apply the database changes;

...Otherwise the user pressed delete, so delete the record and display a confirmation page

...Otherwise the user wants a new query, so return to the top (Show entry/query screen)

I’m not entirely satisfied with this approach because it’s a bit screen-happy. A more sedate user interface (my next project) might employ frames; for example, a query that retrieved multiple rows would show the list in one frame, and clicking on one would call up details in another frame. Anyway, give some thought to how you want your interface to work, and experiment until you are happy.

I’ve included some modified code fragments as an appendix. These started their life as output from the WebServer generator in Designer/2000 version 1.2, which produces read-only WebServer pages.

Overcoming Statelessness

It won’t be long before you want to give your users the ability to "log on" and give different users different privileges. As has been widely discussed in the press and on the web, there is very little intrinsic ability of web applications to preserve the state of a given user session. While it is possible to set up WebServer-authenticated accounts, we chose in our application to invent an application-enforced account concept, because of the desire by the customer to allow anyone to set up their own account. Somewhat simplified, we created a users table and a privileges table, and in the opening screen, the user is required to put in a username and password which the application validates against the users table.

But that is nowhere near sufficient. Once "inside" the application, a user could merely bookmark a page and subvert such a simpleminded scheme: in other words, the next day they could jump into a screen behind the login screen. We adopted a rule that virtually every PL/SQL module must perform user validation. While this sounds painful and slow, it turns out that there are techniques such as client-side "cookies" to facilitate this approach.

By the way, don’t think you can just store the user’s authentication status in a package variable. Every time the user invokes a new PL/SQL-generated page, the previous package state is unavailable; it is discarded immediately after use.

Browser "cookies" are not much more than client-side variables that will persist until a programmer-defined expiration date, invented specifically to help overcome the basic problem of HTTP statelessness. By default, they exist for the current "session," that is, until the user shuts down their browser (or until the browser or the operating system crashes), but they can be set for an infinite expiration date if desired. Cookies are stored in a plain ASCII text file under Windows NT and Windows 95, which I discovered cannot be edited by hand between sessions, because it will then get completely erased when Netscape starts again later. But all the values that you save with a non-session expiration date can be read by anyone with access to the machine, so unencrypted passwords are out of the question.

First you look at this module that either reads or creates a cookie that will hold a "session id." This session id will be stored in a table that correlates the id to a user. Although these modules are somewhat abbreviated from their production versions, the basic idea is the same: a temporary session id gets associated with a given user. It would be better to randomly generate the session id rather than using an Oracle sequence, but I didn’t have time to come up with a PL/SQL random number generator.

CREATE OR REPLACE FUNCTION get_session_id RETURN NUMBER IS

l_session_id number;

l_session_id_cookie owa_cookie.cookie;

BEGIN

l_session_id_cookie := owa_cookie.get('SESSION_ID');

owa_util.mime_header('text/html', FALSE);

IF l_session_id_cookie.num_vals > 0 THEN

owa_util.http_header_close;

return l_session_id_cookie.vals(1);

ELSE

select session_seq.nextval into l_session_id from dual;

owa_cookie.send('SESSION_ID', to_char(l_session_id));

owa_util.http_header_close;

RETURN l_session_id;

END IF;

END;

Note that this must be the first function called for a given web page, because the cookie information can only be sent and received based on tags in the header. To see the value of the cookie you could write a simple calling module. Note the placement of the hutil.hOpen.

CREATE OR REPLACE PROCEDURE show_cookie AS

l_value number;

BEGIN

l_value := get_session_id;

hutil.hOpen('Cookie Demo');

htp.print('The value is: ' || l_value);

hutil.hclose;

END;

Here is some code that will accept a name and password and then call a password validation module. This code merely creates a form that is formatted within an HTML table.

CREATE OR REPLACE PROCEDURE login IS

BEGIN

hutil.hOpen('Login');

htp.header(1,'Please Enter Your Login Name and Password');

htp.formOpen('validate_login');

htp.tableOpen;

htp.tableRowOpen;

htp.tableData(htf.bold('Login Name:'),'right');

htp.tableData(htf.formText('p_name',18,18));

htp.tableRowClose;

htp.tableRowOpen;

htp.tableData(htf.bold('Password:'),'right');

htp.tableData(htf.formPassword('p_pw',18,18));

htp.tableRowClose;

htp.tableRowOpen;

htp.tableData;

htp.tableData(htf.para || htf.formSubmit(null,'Login'));

htp.tableRowClose;

htp.tableClose;

hutil.hClose;

END;

The password validation module appears below: This module uses the get_session_id function described above, and assumes that the list of users exists in a table, and that there is a table that correlates user ids to session ids. The tables might look like this:

SQL> desc t_users

Name Null? Type

------------------------------- -------- ----

INDIVIDUAL_ID NOT NULL NUMBER

WEB_USERNAME NOT Null VARCHAR2(18)

WEB_PASSWORD NOT NULL VARCHAR2(18)

SURNAME VARCHAR2(35)

GIVEN_NAME VARCHAR2(25)

MIDDLE_INITIAL VARCHAR2(1)

SQL> desc t_sessions

Name Null? Type

------------------------------- -------- ----

INDIVIDUAL_ID NOT NULL NUMBER

SESSION_ID NOT NULL NUMBER

CREATE OR REPLACE PROCEDURE validate_login( p_name in varchar2

,p_pw IN VARCHAR2) IS

l_individual_id NUMBER;

l_session_id NUMBER;

l_web_password t_users.web_password%TYPE;

CURSOR indCur IS SELECT individual_id, web_password FROM t_users

WHERE web_username = UPPER(p_name);

BEGIN

OPEN indCur;

FETCH indCur INTO l_individual_id, l_web_password;

IF indCur%FOUND THEN

CLOSE indCur;

IF l_web_password = upper(p_pw) THEN

l_session_id := get_session_id;

-- next call deletes any old session records

-- and inserts a record for the individual

t_user.create_user_session( l_individual_id, l_session_id );

-- this procedure allows the user into the opening page

home( l_individual_id );

ELSE

login;

RETURN;

END IF;

ELSE

CLOSE indCur;

login;

RETURN;

END IF;

END;

Your modules will each begin with a call to of the form l_individual_id := get_individual_id, the latter of which is a function that does a quick table lookup to see if the user's session id, as returned by the cookie, is valid.

FUNCTION get_individual_id RETURN NUMBER IS

l_session_id NUMBER;

l_individual_id NUMBER;

BEGIN

l_session_id := get_session_id;

DECLARE

CURSOR indCur IS SELECT individual_id

FROM t_sessions

WHERE session_id = l_session_id;

BEGIN

OPEN indCur;

FETCH indCur INTO l_individual_id;

IF indCur%NOTFOUND THEN

CLOSE indCur;

LOGIN;

ELSE

CLOSE indCur;

END IF;

END;

RETURN l_individual_id;

END;

I should mention an alternative to cookies: passing the username/password in the URL or in "hidden" form fields, is even less desirable because of their lack of security. This approach is (I think) what is demonstrated in the WebServer 1.0 "Take the Train" demo code, which is unfortunate because people may reuse it thinking it is safe, but on the other hand I find the code a bit hard to follow, so hopefully people will do their own thinking before using it!

Debugging and Testing

One of the first error screens you will encounter will say: "Request Failed. We were unable to process your request at this time. Please try again later." This typically appears as a result of an invalid PL/SQL procedure call. When you see this message, you’ll want to look in the error log file, which by default will probably be $ORACLE_HOME/ows2/log/myapp.err, where myapp is the DCD name. Write a simple script that tails that file, for example:

#!/bin/sh

tail -40 $ORACLE_HOME/ows2/log/myapp.err

And run this immediately after you get Request Failed. There’s nothing magic about the choice of the last 40 lines, by the way. (Why Oracle couldn’t send this error message out through the HTTP server so it shows up in the browser is beyond me. It would make life on developers way easier...but is "easy development" an oxymoron?)

Another gray screen you’ll get familiar with says, "The requested URL was not found." This is an indication that either you have mistyped the URL or there is a setup or configuration error. Unfortunately it won’t put anything useful in the log files; solving the problem may require some head-scratching.

A tool you will find useful during debugging is owa_util.showpage. This allows you to see the HTML generated by a PL/SQL package without using a web browser. Write a simple sp.sql script that does this:

rem sp.sql: show queued WebServer output

rem next line is unnecessary if serveroutput already on

set serveroutput on size 100000

execute owa_util.showpage

Here is a sample usage from SQL*Plus:

SQL> execute empdetail(7788)

PL/SQL procedure successfully completed.

SQL> @sp

<HTML>

<HEAD>

<TITLE>Employee Detail for 7788</TITLE>

</HEAD>

<BODY BGCOLOR="white">

<H1>Employee 7788</H1>

<P>

<B>Name:</B>SCOTT<BR>

<B>Dept:</B>20<BR>

</BODY>

</HTML>

PL/SQL procedure successfully completed.

Further Resources

In addition to the online documentation and the well-known Oracle newsgroups, I suggest having a look at the downloadable utilities at http://govt.us.oracle.com, which is where WebServer maven Thomas Kyte points on his newsgroup postings. You might want to subscribe to the ORAWEB-L mailing list; send a message to majordomo@labyrinth.net.au with the following command in the body of the message: SUBSCRIBE oraweb-l MY_EMAIL_ADDRESS. You can also visit http://www.datacraft.com, where I intend to post more useful code, and where I also have some LiveWire samples of web/database integration for the PL/SQL-impaired.

Appendix: Code Fragments for Read/Write Screen

This code is briefly described above in the section above on "The Big Leap." It began as Designer/2000 output, then was modified for insert/update/delete. For this paper I started with the production code and made some changes such as global-search-and-replace of the actual table/column names; unfortunately I did not have a chance to test it thoroughly before going to press. There are a couple of calls to some utility routines that are either described elsewhere in the text, or are commented below.

CREATE OR REPLACE PACKAGE ADMDEPT IS

-- FormEnter is a module that serves multiple purposes:

-- 1) query screen; 2) Create-new screen; 3) update

-- The available buttons vary based on the mode.

PROCEDURE FormEnter ( p_deptno IN NUMBER DEFAULT NULL

,p_skip_header IN VARCHAR2 DEFAULT NULL);

-- "process" takes the arguments passed from the FormEnter

-- screen and either displays search results or applies the

-- DML requested by the user

PROCEDURE process(

P_DEPTNO IN VARCHAR2 DEFAULT NULL,

P_DNAME IN VARCHAR2 DEFAULT NULL,

Z_START IN VARCHAR2 DEFAULT NULL,

Z_ACTION IN VARCHAR2 DEFAULT NULL);

-- ShowError is the standard Des2K-generated module

PROCEDURE ShowError(

P_ERRNO IN VARCHAR2,

P_ERRM IN VARCHAR2,

P_CONTEXT IN VARCHAR2 DEFAULT NULL,

P_ACTION IN VARCHAR2 DEFAULT NULL);

end;

/

--------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY ADMDEPT IS

-- these are the button captions to which multiple modules may

-- refer. Out here in global-land so they can be changed easily.

QUERY_BUT_CAPTION constant varchar2(100) := 'Query';

SAVE_BUT_CAPTION constant varchar2(100) := 'Save';

CREATE_BUT_CAPTION constant varchar2(100) := 'Create';

NEW_BUT_CAPTION constant varchar2(100) := 'New Search';

DELETE_BUT_CAPTION constant varchar2(100) := 'Delete Dept';

NEXT_BUT_CAPTION constant varchar2(100) := 'Next';

PREV_BUT_CAPTION constant varchar2(100) := 'Previous';

RECORD_SET_SIZE constant number(4) := 10;

PROCEDURE FormEnter( p_deptno IN NUMBER, p_skip_header IN VARCHAR2) is

-- p_deptno: department number to display, if any

-- p_skip_header: indicates whether we need to skip printing of page header

l_individual_id NUMBER;

skipit EXCEPTION;

CURSOR dptCur is SELECT deptno, dname

FROM dept WHERE deptno = p_deptno;

dptRec dptCur%ROWTYPE;

BEGIN

-- always begin by validating the user

l_individual_id := t_util.get_individual_id;

IF l_individual_id IS NULL THEN

RAISE skipit;

END IF;

IF p_skip_header IS NULL THEN

IF p_deptno IS NOT NULL THEN

hutil.hOpen('Modify Department');

htp.header(1,'Modify Department');

ELSE

hutil.hOpen;

htp.header(1,'Search or Create Department');

END IF;

htp.formOpen('ADMDEPT.process');

-- these WSGL routines are supplied with Designer/2000. They are

-- not magic, just shortcuts to getting tables to lay out

-- according to some rules.

WSGL.LayoutOpen(WSGL.LAYOUT_TABLE);

WSGL.LayoutRowStart;

WSGL.LayoutHeader(5, 'LEFT', NULL);

WSGL.LayoutHeader(40, 'LEFT', NULL);

WSGL.LayoutRowEnd;

-- if the module has been called with a deptno argument, we

-- can assume they want to modify or delete it

IF p_deptno IS NOT NULL THEN

OPEN dptCur;

FETCH dptCur INTO dptRec;

CLOSE dptCur;

WSGL.LayoutRowStart('TOP');

WSGL.LayoutData(htf.bold('ID:'));

WSGL.LayoutData(htf.bold(dptRec.deptno));

WSGL.LayoutRowEnd;

END IF;

WSGL.LayoutRowStart('TOP');

WSGL.LayoutData(htf.bold('Department Name:'));

-- note that we can always refer to dptRec.dname; if p_deptno

-- is null, dptRec.dname will be null

WSGL.LayoutData(htf.formText('P_DNAME', '40', '40', dptRec.dname));

WSGL.LayoutRowEnd;

WSGL.LayoutClose;

IF p_deptno IS NULL THEN

htp.formSubmit('Z_ACTION',htf.escape_sc(QUERY_BUT_CAPTION));

htp.formSubmit('Z_ACTION',htf.escape_sc(CREATE_BUT_CAPTION));

ELSE

htp.formHidden('P_DEPTNO',p_deptno);

htp.formSubmit('Z_ACTION',htf.escape_sc(SAVE_BUT_CAPTION));

htp.para;

htp.formSubmit('Z_ACTION',htf.escape_sc(DELETE_BUT_CAPTION));

htp.formSubmit('Z_ACTION',htf.escape_sc(NEW_BUT_CAPTION));

END IF;

htp.formClose;

hutil.hClose;

EXCEPTION

WHEN skipit THEN NULL; -- failed validation

WHEN OTHERS THEN

ShowError(SQLCODE, SQLERRM, 'ADMDEPT.FormEnter:');

hutil.hClose;

END;

--------------------------------------------------------------------------------

-- P_DEPTNO - Department number

-- P_DNAME - Department Name

-- Z_START - First record to display

-- Z_ACTION - what to do? this will match the strings in the button captions

PROCEDURE process(

P_DEPTNO IN VARCHAR2,

P_DNAME IN VARCHAR2,

Z_START IN VARCHAR2,

Z_ACTION IN VARCHAR2) IS

L_DEPTNO DEPT.DEPTNO%TYPE;

L_DNAME DEPT.DNAME%TYPE;

l_individual_id NUMBER;

skipit EXCEPTION;

done EXCEPTION;

BEGIN

l_individual_id := t_util.get_individual_id;

IF l_individual_id IS NULL THEN

RAISE skipit;

END IF;

------------- UPDATE -------------

IF z_action = SAVE_BUT_CAPTION then

hutil.hOpen('Result of Department Modification');

htp.header(1,'Result of Department Modification');

-- update_dept is just a routine that issues the update statement

t_dept.update_dept( p_deptno, p_dname );

-- if no exception raised, we succeeded

htp.p('Department ' || htf.anchor( 'ADMDEPT.formenter?p_deptno=' || p_deptno

,p_dname ) || ' has been modified.');

htp.formOpen('ADMDEPT.process');

htp.formSubmit('Z_ACTION',htf.escape_sc(NEW_BUT_CAPTION));

htp.formClose;

hutil.hClose;

------------- INSERT -------------

ELSIF z_action = CREATE_BUT_CAPTION THEN

hutil.hOpen('Result of Department Creation');

htp.header(1,'Result of Department Creation');

-- call a routine that creates the department

l_deptno := t_dept.create_dept( p_dname );

-- if no exception raised, we succeeded

htp.p('Department '

|| htf.anchor( 'ADMDEPT.formenter?p_deptno=' || l_deptno

,p_dname ) || ' has been created.');

htp.formOpen('ADMDEPT.process');

htp.formSubmit('Z_ACTION',htf.escape_sc(NEW_BUT_CAPTION));

htp.formClose;

------------- NEW QUERY/CREATE -------------

ELSIF z_action = NEW_BUT_CAPTION THEN

formenter;

------------- DELETE -------------

ELSIF z_action = DELETE_BUT_CAPTION THEN

hutil.hOpen('Result of Department Deletion');

htp.header(1,'Result of Department Deletion');

l_dname := t_dept.delete_dept(p_deptno);

htp.p('Department ' || l_dname || ' has been deleted.');

htp.formOpen('ADMDEPT.process');

htp.formSubmit('Z_ACTION',htf.escape_sc(NEW_BUT_CAPTION));

htp.formClose;

hutil.hClose;

------------- QUERY -------------

else -- QUERY_BUT_CAPTION

-- this section of the code omitted for space

-- reasons. The code builds a where-clause dynamically

-- and displays the matching data.

-- Visit http://www.datacraft.com for more details.

NULL;

END IF;

EXCEPTION

WHEN skipit OR done THEN NULL;

WHEN OTHERS THEN

ShowError(SQLCODE, SQLERRM, 'ADMDEPT.process:');

htuil.hClose;

END;

--------------------------------------------------------------------------------

-- P_ERRNO - The error number

-- P_ERRM - The error message

-- P_CONTEXT - The context of the error

-- P_ACTION - The action to take (if known)

PROCEDURE ShowError(

P_ERRNO IN VARCHAR2,

P_ERRM IN VARCHAR2,

P_CONTEXT IN VARCHAR2,

P_ACTION IN VARCHAR2) IS

BEGIN

hutil.hOpen('Error');

htp.para;

IF P_CONTEXT IS NOT NULL THEN

htp.p(P_CONTEXT);

htp.para;

END IF;

htp.p(P_ERRM);

IF P_ACTION IS NOT NULL THEN

htp.para;

htp.p(P_ACTION);

END IF;

hutil.hClose;

END;

Last modified September 27, 2002 5:24 PM .