Oracle WebServer

By Thomas Dunbar

What is WebCyS? Using a World Wide Web browser as the front-end client to one's server processes provides the standard minimal presentation layer that has long been needed for effective client-server design. Such development is so different from both static WWW page design and from traditional client-server design that it is convenient to have a separate buzzword for such development; in this article, I will use the term WebCyS to refer to client-server applications which use the Web to connect browser clients with database servers.

This article focuses on using Oracle's WebServer products for WebCyS development. The name "WebServer" has led to a bit of confusion: Oracle WebServer is much more than a WWW server (i.e. more than an http demon) and in fact, one can use WebServer with other http servers.

WWW: the presentation layer for Client-server development

In the past, a widely supported standard for the presentation of information did not exist, forcing one into separate compilation of client applications for specific platforms. What was needed is something like a high-level X Window system whose basic objects were not pixels but:

Window objects

When NCSA Mosaic was first released, people quickly realized that a "good enough" minimum was now available for formatted text and graphic objects. Forms support, with its related widgets, followed and frames have become available and serve as a minimal windowing method, thus completing the essential user-interface tool kit.

As a result, there is now a somewhat stable yet controversial standard for the presentation of information. By developing for this presentation layer rather than specific platforms, one can easily create cross-platform applications that can evolve rapidly. Of course, "easily" is a relative term, but it's certainly true that it is easier to develop for ONE platform than for many. The term WebCyS is a name for this platform, viewed as a presentation layer for which one is developing applications, rather than as a delivery mechanism for hypertext multimedia.

The Browser Landscape

As mentioned above, the "platform" for which we are developing is not precisely specified. Depending on the application, various different specifications might be possible. Until the official formal standards specify all the needed elements (tables, form widgets, frames) adequately, it comes down to deciding what browser(s) one will test applications with: Netscape, NCSA Mosaic, Lynx, etc. Personally, I don't consider a browser to even exist, unless it:

The issue of precisely defining the WebCyS platform is not really relevant here, though. One application might specifically use the features of Oracle's PowerBrowser, whereas another might be designed to work with any browser that is compatible with Netscape 1.1. It is up to you as the developer to decide what feature set is essential and how much specialized coding you want to do.

What Language for WebCyS Development?

Once we've decided to do WebCyS development, what language should we use? While it is true that one might not need a programming language at all, it is also true that one often doesn't even need a computer, either. At any rate, this article is about development that requires significant programming, typically by more than one developer.

The easy answer, of course, is to use what you have and know. When I first started looking around for a WebCyS development language, what I knew best was Perl and C. Neither seemed really suitable, however. It took too long to develop in C and I had little need for the systems programming features that are C's forte. While Perl is great for knocking little tasks off quickly, it doesn't distribute well across multiple developers. A significant feature of WebCyS development is that, given the cooperation needed between html editors, graphic artists, programmers and analysts, good software engineering support is even more essential. In particular, modules/packages and exception handling are basic requirements so I decided that something like Ada but with a good interface to some enterprise level RDBMS would be ideal.

PL/SQL is the best language currently available for database-intensive WebCyS development.

Of course, it is only available for Oracle, but that is not a problem for us, right? The value of Oracle's WebServer products is that they allow Oracle developers to use what they already have, PL/SQL, and at the same time, PL/SQL's excellent software engineering support draws WebCyS developers to Oracle who might not otherwise be Oracle customers.

A Coffee Break

Perhaps you're thinking, "OK, I'll grant you that PL/SQL is a nice language; but what about Java?" Java is an excellent language for extending the client (i.e. running applets in one's WWW browser); however, our primary concern is developing for the standard presentation layer. Tight integration with the database server and good support for team development is what is essential. I don't need to create reusable, extensible objects as much I need to cleanly segment code to manage existing database objects. That said, one should point out that it is very easy to integrate Java applets with PL/SQL to provide, for example, charting support (I've made a little demonstration of such use, together with source code, accessible at http://gserver.grads..vt.edu/cgi-bin/wowtgd/hr.showDeptj?).

Working with Oracle WebServer

Oracle's WebServer products grew out of an idea from Magnus Lonnroth, senior product manager, Oracle WebServer, Oracle Corp. and the creator of the original WOW interface. Magnus suggested writing an OCI gateway between Oracle and the http server. This idea is simple, but it is that crucial step which allows one to bring all the power of PL/SQL to bear on WWW development. The source to the original program, wowstub, along with basic pl/sql packages for html formatting, was made freely available on the Internet and drew various people to using Oracle and PL/SQL.

This program grew into the Oracle Web Agent, the crucial component of Oracle Webserver. Oracle Webserver 1.2 is a standard part of Oracle Server 7.3; in addition to being more robust than the original wowstub gateway and supporting multiple-valued parameters (e.g. in select lists), it also comes with significant utility packages, written by Matt Bookman (Matt is a developer with the Internet Products Division at Oracle Corp.), which handle dynamic sql and table formatting. While WebServer 1.2 is free and comes with Oracle 7.3, the recently introduced WebServer version 2.0 is a separate product costing $2495. The major enhancements are to the http server (Oracle Web Listener), SSL encryption for example, and a tight bundling of the web agent with the http server yielding performance benefits over a CGI interface, but the latter is still available for general use.

There are also significant enhancements for the developer: Java support and various PL/SQL utility packages (A quick reference guide, in Adobe PDF format is at http://gserver.grads.vt.edu/ows2/owsqr.pdf). My favorite addition here is Matt Bookman's very nice implementation of regular expression pattern-matching which adapts the algorithms in Kernighan's "Software Tools" to PL/SQL. The syntax used is modeled after Perl's usage and enhances PL/SQL with the pattern matching power that WWW developers used to Perl have come to expect. Note that this package can be used by any pl/sql procedure, not just those in the WebServer Developer's Kit.

While these various products have different performance characteristics, from the point of view of the WebCyS developer, they all work the same: they allow one to use PL/SQL as one's development language for the WWW. For more information on the various products, see: http://www.oracle.com/ products/websystem/html/webSystemOverview.html.

Working with WebServer

At this point, those of you familiar with other Oracle development tools but not with WebServer may be wondering, "What about development costs?" As a little demo, suppose we want a tiny app with two screens: 1.Search: lets user pick one of several tables and also give an arbitrary "where ..." clause. Also give user option of formatting output as a table or as a "form" (the latter being especially useful for fields where we want the text to wrap).

PICTURE HERE

1.Search Screen

Search Results
The package specification is:

 1 create or replace package empt as
 2   procedure search;
 3   procedure dosearch(table_i in varchar2, query in varchar2,
 4		 result_type in varchar2);
 5 end;
If I create a "service" (i.e. a tag to tell the Oracle Web Agent what user to execute the procedure as) and then compile the package under that user, then I can view the search page via:

http://diads.com/es/owa/empt.search
Here, "es" is the service name and is being used in two different ways:

  1. OWA is using it to determine what Oracle user to execute under
  2. The http server is using it as a script alias, i.e. as a directory path alias to the cgi-bin executable, owa.

It doesn't take much code to implement those procedures:

  1 create or replace package body empt as
  2   procedure search is
  3  cursor c is select table_name from user_tables
  4	where table_name in ('EMP','DEPT');
  5 begin
  6 htp.htitle('Search');
  7 htp.formOpen('http://diads.com/es/owa/empt.dosearch');
  8 htp.p('Table: '||htf.formSelectOpen('table_i'));
  9 for r in c loop
 10  htp.formSelectOption(r.table_name);
 11 end loop;
 12 htp.formSelectClose; htp.formSubmit;
 13 htp.p(htf.para||'query: '||htf.formText('query',50,50)||
 14       '
(ex: where deptno > 10 order by deptno ) 
');
 15 htp.p('Format output as: Table '||htf.formRadio('result_type',
 16	'table-view','CHECKED')||' Form '||
 17	htf.formRadio('result_type','form-view'));
 18 htp.formClose;
 19 end;
Note that one is free to mix htp/htf package calls, which hide the actual HTML, with straight html tags. If one needs to have more control over the placement of the form objects, which is usually the case for more complex forms, put the form elements inside a table (or, often, use tables within tables). But remember that you are designing for a different medium; there is no good reason to mimic older client/server design whose standard style developed in response to problems and conditions that may no longer apply.

The coding of the output report is even easier:

 20 procedure dosearch(table_i in varchar2, query in varchar2,
 21		result_type in varchar2) is
 22   ok boolean;
 23 begin
 24   htp.htitle('Search Results');
 25   htp.p('The results of the query: select * from '||table_i
 26	 ||' '||query||' are:'||htf.para);
 27   if result_type='table-view' then
 28    ok:=owa_util.tablePrint(table_i,'BORDER=7',1,'*',query);
 29   else
 30    ok:=tutil.tablePrint(table_i,'',1,'*',query);
 31   end if;
 32 exception
 33   when others then
 34     htp.p('Error: '||sqlcode||': '||sqlerrm);
 35 end dosearch;
 36 end empt;
Report Screen (table-view and form-view)

Here the tutil.tablePrint procedure is an adaptation of the standard tablePrint to give a "form view" layout. I have found that the combination of these two functions, which hide the use of dynamic sql internally and provide basic output layout, can handle most of my reporting needs when combined with clever use of views, etc. (once 7.3 is released and I've had time to clean up the code, I plan to make the tutil code public if something better isn't available). For example, I might define a view emp_view with fields such as: 

'<a href="/es/owa/empt.empinfo?id='||
empno||'">'||ename||'</a>' employee, etc.
This idea can be expanded greatly, of course. One can have a view one of whose fields is an html table with a dozen fields from another table, for example, and then display that view via tablePrint. I might also, rather than selecting all fields, build up the list of fields via a select list that I then use to format the call to tablePrint.

As for other development tools, packages that themselves generate packages of procedures which generate pages or page components can often save time. The next version of Oracle Developer, which was demonstrated recently, will have some support for this sort of thing. In addition, there are certainly adequate facilities within PL/SQL, and it's support packages (e.g. dbms_sql), for serious developers to create their own tools. Furthermore, tools designed for use in other contexts (for example, an html form generation tool) can often be adapted to assist with the tedious aspects of development. However, to be honest, my personal opinion is that if the project is coherently designed and uses native building materials then a text editor with cut and paste between windows, regular expression search/replace, and a powerful macro language is the best development tool.

The other side of development costs is what staffing is needed. First of all, there are the people that any Oracle development project will need:

But also, one will need:

Here, the only position that an existing Oracle development group is liable to need to recruit is the graphic designer, the rest can be learned by existing staff. On the other hand, an existing WWW design shop will have much more serious personnel needs, the higher levels of which might be contracted out.

Dealing with the Unique Organizational Problems presented by WebCyS Development

Once software development involves more than one person, organizing the effort so that everyone can work efficiently becomes a serious concern. This very quickly becomes a special problem when using pages generated on the fly with Oracle. Consider the following scenario:

Susan creates a package, patient_forms, which generates various forms for the application, a medical records system. Patient_forms consists of procedures creating five forms and various support procedures and functions and is really just a small part of the overall package. A typical procedure in the package, call it request_physical, will generate a WWW page consisting of:

It is the last item which differentiates database-driven WWW development and motivates generating the page via PL/SQL rather than just using a plain html page.

PICTURE HERE

This also presents a problem. Suppose some simple change needs to be made in the introductory text. Now it is not a matter of simply editing the text, or changing some html tag. Rather,

  1. Not wanting to bother Susan, who is very busy developing even nicer applications, I ask her to send the file to Tom, a staff member who does simple html editing, etc.
  2. Can't find Susan, so I get the file myself and send to Tom. And think there needs to be a better system.
  3. Tom gets file and looks at the html interspersed with straight PL/SQL and with calls to packages to do the html formatting and throws up his hands. But, calming down, he looks at the file and sees the line: 
      htp.p('Physicals are in room 2-213.');

    So, he goes ahead and changes the room number and then makes the various other changes needed. And thinks there needs to be a better system.
  4. Tom can't find me and forgets how to "submit" the changes so he asks Joe who says "login to sqlplus...." Well, eventually Tom gets the changed file into the system, looks at the page, and sees that it looks ok.
  5. Unfortunately, as Tom was moving through the file, he accidentally inserted a character in a url call to another package. But, since it was inside a string, the error didn't affect compilation.
  6. I get an emergency call that the system isn't working. I find Susan and after explaining the events, she finds and fixes the problem. And thinks there needs to be a better system. Now in this comedy of errors, I was mainly at fault. Still, the problem remains that simple text, html tags, and pl/sql code of varying complexity gets all mixed together. Of course, I could have Susan do all the changes but I'm not paying her to do simple text entry, even if she had the time.

    PL/SQL has excellent support for partitioning development among programmers via its package specification/package body mechanism. We could adopt a practice of putting the html page "chunks" in separate packages from the pl/sql programming logic. But that still leaves us with the problem of having to compile code for even the simplest change in the text of a page.

A Solution: put html in table

A simple solution to this problem, which can certainly be modified and enhanced, is to:

  1. Put html components of a page in a table
  2. Write an easy interface to this table for my text editors and html designers.
  3. Create the pages by looping over a cursor which has the components for the page.

If the development team is large, security and access control would need to be considered but that is easy enough to add later. The components table might look like:

create table components (id number primary key,
page varchar2(16), 	- the page, or frame
item number,    	- order in the page or frame
lang varchar2(4),  	- language of component (code/other)
type varchar2(50), 	- type/name of component 
value varchar2(2000))

Then, the page components could be edited with a page generated via:

procedure modify_component (user in varchar2, 
             pwd in varchar2, id_i in varchar2) is
 r components%rowtype;
begin
select * into r from components where id=id_i;
if authorized (user,pwd) then
  htp.htitle('Modify component '||id_i);
  htp.formOpen('/svca/owa/pages1.do_modify_component');
  htp.formHidden('user',user);
  htp.formHidden('pwd',pwd);
  htp.formHidden('id_i',id_i);
  htp.p('Page Name: '|| htf.formText('page_i',
                 16,16,r.page)||htf.para);
  htp.p('Item Number: '||htf.formText('item_i',
                 4,4,r.item)||htf.para);
  htp.p('Language: '||  htf.formText('lang_i',
                 4,4,'eng',r.lang)||htf.para);
  htp.p('Object Type: '||htf.formText('type_i',
                 4,4,r.type)||htf.para);
  htp.p('Value: '||htf.formTextAreaOpen('value_i',
                 14,90,'left','WRAP=HARD')
     ||r.value||htf.formTextAreaClose||htf.para);
  htp.formSubmit;
  htp.formReset;
  htp.formClose;
else
 htp.p('Permission denied');
end if;
end;

Now, the pl/sql page design might look something like:

procedure request_physical(patient in varchar2) is
 val varcha2(2000);
 cursor c is select value from components 
        where page='physical' and item>2
        order by item;
-more variables
begin
 select value into val from components 
   where page='physical' and item=1;
 htp.p(val);
-pl/sql for the part of the page that is not straight html
-ie that depends on 'patient' and then finish up.
 for r in c loop
  htp.p(r.value);
 end loop;
end;

Of course, for complex pages it would be even nicer to do "if the component is a pl/sql procedure, then execute it rather than printing the component." That is easy too, once one realizes, as was kindly pointed out to me by Steven Feuerstein (Steven is the author of the best PL/SQL reference: Oracle PL/SQL Programming, O'Reilly Press), that one needs to embed the call within BEGIN...END; and use dynamic sql. In particular, one can use:

procedure doproc (val in varchar2) is
 cur integer;
 ret integer;
 stmt varchar2(8000);
begin
cur:=dbms_sql.open_cursor;
stmt:='begin '||val||'; end;';
dbms_sql.parse(cur,stmt,1);
ret:=dbms_sql.execute(cur);
dbms_sql.close_cursor(cur);
end;

And now we can make a procedure that does the middle part of the physical page and generate the page itself via: 
procedure request_physical(patient in varchar2) is
 cursor c is select value from components 
                where page='physical'
                order by item;
begin
 for r in c loop
   if r.lang='code' then
     doproc(r.value||'('||patient||')');
   else
     htp.p(r.value);
   end if;
  end loop;
exception
when others then htp.p('Oops: '||sqlcode||': '||sqlerrm);
end;

Once we have this in place, changes in the text and html tags (perhaps very extensive changes) can be made without having to recompile the package containing the procedure which generates the page. It is certainly true that this strategy has its own set of problems; however, those problems are far outweighed by the benefits.

Securing Table-driven Pages

By storing the html objects in a table, I am storing part of the application's code in a table; thus, the application can be broken by simply deleting data from the table! Clearly, using this strategy calls for good security and backup.

Restrict modify access to table - Certainly one will want to restrict access the html object table for updates, etc. One should also change the access routines so that the html developers can not change the elements of type "code" or can update but not insert or delete, or require a special password for that. One might also revoke all privileges except for "SELECT" on the production system.

Dump the html objects regularly - In addition to one's normal backup routines, one may well want to frequently do a dump of the html objects table. This allows one to not only back out of mistakes but also provides a record of the application at a given point in time (providing one is equally careful in preserving versions of the pl/sql code:).

Of course, security, including backups, is an major concern for any extensive development project, whether WWW-based or not. But given adequate special planning for these security concerns, the strategy outlined above not only speeds development but also greatly reduces maintenance costs.

The WebCyS Development Climate

Oracle's WebServer Option, etc. has created a new group of potential Oracle clients. It is common nowadays for companies to have a significant investment in WWW related services, whether an in-house application for tracking company resources or an extensive advertising component directed to the Internet at large. In either case, the ease of cross-platform distribution via the web is compelling. As a result, people within the company, especially those concerned with the WWW development start asking, "Wouldn't it be nice if we could access ... data. Or even get customer information straight from them into our data systems." The push to develop www-based client\server systems is more apt to come from the people doing web pages than from the information systems development staff.

For example, a Fortune 500 company might have a thirty person advertising staff that is very skilled in graphic design, public relations and html authoring. As soon as they start to use pl/sql to generate the pages, making use of company data stored in Oracle tables, all the page changes have to get funneled through the pl/sql compiler. Now, this staff got sold on WebServer based web pages from demos created by PL/SQL developers. But they want to get on with their work, not become database developers. And likewise, analysts who love to hack code don't want to get bogged down in changing page contents. The strategy outlined above allows each group to get on with their work and lessens the opportunities for stumbling over each other in the rush to meet deadlines.

Under Construction

In summary, WWW-based client/server development with Oracle is very new. I hope I've encouraged you to join in the effort to create adequate design tools and development strategies which, like everything else connected with the Web, are still under construction. Nevertheless, it holds forth the promise of a framework for the easy development of cross-platform applications that can rapidly evolve.

About the Author

The author, Thomas Dunbar, works at Virginia Tech. where his desktop computer is http://gserver.grads.vt.edu. Thomas also owns DIADS, which specializes in Oracle WebServer-based development, at http://diads.com and on customers' sites.


This is a copy of an article published @ http://www.ioug.org/