Building Your First WebServer Application:
William L. Pribyl DataCraft, Inc.
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://220.127.116.11/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.
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,
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.
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.
/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:
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
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.
Note, the file-system directory is the physical path to the directory that will
contain your static page.
Let's say your myhello.html file contains the following:
Öthen, assuming itís running on port 80, you can point a browser
to it using: http://myhost.mydomain/myapp/pages/myhello.html
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;
To oversimplify a bit, "htp.print" procedure takes a single argument
and buffers it for transfer to the user's browser. (If you execute this
procedure from SQL*Plus, you will see nothing unless you use the owa_util.showpage
utility-more on that in the "Debugging and Testing" section below).
After you have created this procedure as the Oracle userid specified for the
DCD myapp, you should be able to access the procedure from a browser using the
Oracle provides built-ins that will generate virtually all HTML tags; you can
use these an alternative to using a straight dump of the HTML. Here is
the "builtin-ified" version which will produce identical output to
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; END;
Quick sanity check: to convert the Hello, Web program to use the package above,
it would read:
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. If you want to print an entire
table of employee data, there are at least two ways you can go about it. One
is to generate the HTML yourself in a PL/SQL loop. Keep in mind, as youíre looking
at this code, that the tableOpen, tableData, etc. procedures are referring to
HTML tables, not Oracle tables.
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
It would probably be better to dynamically build and execute the SQL Select
statement, which gets us more flexibility in the long run and also gets us around
the forced use of ugly LIKE '%' in the where clause. (This technique is also
useful if you are giving the user a query form and you need to build the SQL
Select statement on the fly based on those search criteria for which the user
supplies a value.)
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
|| '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;
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,
As an aside, there is an alternative to putting the links in the PL/SQL: you
can code the links into a database view (I saw this suggested by someone on
the ORAWEB-L mailing list; I wish I knew whom to credit for first suggesting
it). The basic approach would be something like this:
CREATE OR REPLACE VIEW emp_view AS SELECT '<A HREF="empDetail?p_empno=' || empno || '">'
|| empno || '</A>' empno ,empno empno_base, ename,
sal FROM emp;
Using the view approach would mean you wouldn't have to change the empPrint
module; it might also mean that in a different circumstance, where you don't
need dynamic SQL, you could use a view to get more mileage out of the owa_util.tableprint
For the detail page, we will put one field per line, and we want to accept the
argument from the empPrint page. You can, of course, get a lot fancier
than this, using HTML tables for alignment and formatting.
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;
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.
If you generate HTML that looks like this:
<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>
... then when the user clicks on the Submit button, text entered in the p_search
field will get passed as the p_search parameter to the PL/SQL module named searchEmp.
This presumes that the current page was generated by the same agent that can
execute searchEmp, since it uses a relative URL.
The PL/SQL fragment to generate the form (skipping the standard header and footer):
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;
The searchEmp module might look like this:
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 :=
,ccolumns => 'ename, empno, sal'
,cclauses => 'where ename like upper('''
|| p_ename || '%'')' ); hutil.hClose; WHEN no_data_found THEN null; -- handle appropriately END; 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
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)
|| ' union select to_number(null), '' '', null from dual order by 2'
...which, although kind of ugly, generates the eminently useful HTML:
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
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.
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 modulemust
perform user validation. While this sounds painful and slow, it turns out
that there are techniques such as client-side "cookies" to facilitate
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
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(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
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
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
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:
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
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
firstname.lastname@example.org 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
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
htp.header(1,'Modify Department'); ELSE
htp.header(1,'Search or Create Department'); END IF; 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.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; END;
Last modified June 26, 2000 11:46 AM. Standard trademark disclaimer text goes here.