 | Chapter 7 The PL/SQL Toolkit |  |
The inability to save information, or state, throughout a session is one of HTML's major limitations. HTML has no client/server type variables that remember things as the user moves from page to page. For example, if we want to use a piece of information entered by a user on the first page of a web system, we must save it somehow and be able to recall it later. There are four basic ways to do this: saving the information as part of a query string in a URL, saving it in a hidden field, saving it in a database table, or saving it in a cookie file by using the OWA_COOKIE package, described later in this section. In this section, we'll look at each method and discuss possible problems you may run into.
The first way to maintain state, using a query string of a URL, is the most straightforward: you simply build the string as you go, placing the information you want to pass from screen to screen in name/value pairs. These values are then passed to the procedure specified in the URL's href attribute when the user clicks on the hyperlink. The disadvantages to this approach include the following:
Depending on the system, the maximum length of the URL is limited to 256 characters.
Each value must be encoded to the CGI specification. It can be easy to forget to do this if you're in a rush.
The second way to save state information is to store it in hidden fields. A hidden field is simply an invisible input element that is part of an HTML form. Although the user cannot see the value on the screen, the hidden field is stored as part of the underlying HTML code. To maintain a value across multiple sessions, all we have to do is include the value as a hidden field on the form. You create a hidden field by setting the type attribute of an <input> element to "hidden," as in the following example:
<input type=hidden name=user_id value="10235">
The disadvantages of this approach include the following:
Every procedure must include a corresponding parameter for the hidden value. While this might be okay for a small number of fields, it quickly becomes unworkable for larger numbers (this also applies when embedding the information in a URL).
Each procedure must include the code to reproduce the hidden fields as part of its output.
Hidden fields are insecure since almost all browsers have a "View Source" option that allows users to look at the underlying HTML code, making hidden fields of limited value when security is an issue.
The third method for maintaining state simply saves the information in a table and uses a SELECT statement to retrieve it later. However, there are two problems with this approach:
It requires a hit against a table to both save and recall a piece of information.
We need something to use as a primary key, carried across each page, to associate the state information with a particular session. The simplest way to do this is to store the client's IP address as part of a primary key. We can use this address, which is simple to obtain, when we want to recall the information later.
The fourth method is to save state information using cookies. Each cookie has a name and one or more associated values, and is saved either as a record on the user's machine or as an environment variable on the web server. In either case, it is accessible throughout (and sometimes even after) a user's session. You create a cookie on a user's browser by embedding HTML-like commands into the MIME header of a page. For example, the following set of instructions creates two cookies (notice that the second has multiple values):
Set-Cookie: username=odewahn Set-Cookie: city=BOSTON; city=CHICAGO; city=NEW YORK
Some disadvantages of this approach are:
Cookies were originally introduced by Netscape and are not part of the HTML standard. However, they have become a popular way to overcome statelessness and are now supported by most browsers.
Cookies that are saved on a user's machine (and don't expire) tie the user to that specific machine. For example, many Internet storefronts use cookies to save your user information. When you visit the site, the server reads the cookies and thinks it's you. When someone else borrows or uses your computer, though, this information is still stored on the machine, which can lead to problems.
Cookies can be turned off. Most browsers allow users to reject cookies, usually out of privacy concerns. Consequently, you can't save any state information to those users' machines.
In the next section, we'll look at the toolkit package for manipulating cookies.
The OWA_COOKIE package contains procedures that allow us to create, access, and even update cookies within PL/SQL.
A cookie can have multiple values that can be as large as 4K. These are stored in an array named vc_arr:
TYPE vc_arr IS TABLE OF VARCHAR2(4096) INDEX BY BINARY_INTEGER;
NOTE: The vc_arr used in OWA_COOKIE is not the same as the one used in OWA_TEXT.
The cookie itself is represented with a record that holds its name, its values, and the number of these values:
TYPE cookie IS RECORD ( name VARCHAR2(4096), vals vc_arr, num_vals INTEGER );
The procedures and functions of OWA_COOKIE read, create, and remove cookies. The instructions to read cookies retrieve those cookies from the browser and store their values in a cookie variable. Creating or removing the cookies is slightly trickier. Table 7.13 shows the procedures and functions for OWA_COOKIE, along with their parameters.
| Procedure/Function | Parameters | Description |
|---|---|---|
| name IN VARCHAR2 | Generates instructions to retrieve a specified cookie from the browser and store its value in a cookie variable | |
| names OUT owa_cookie.vc_arr vals OUT owa_cookie.vc_arr num_vals OUT INTEGER | Generates instructions to retrieve the names and values of all unexpired cookies | |
| name IN VARCHAR2 value IN VARCHAR2 expires IN DATE DEFAULT NULL path IN VARCHAR2 DEFAULT NULL domain IN VARCHAR2 DEFAULT NULL SECURE IN VARCHAR2 DEFAULT NULL | Generates instructions to create a cookie | |
| name IN VARCHAR2 value IN VARCHAR2 path IN VARCHAR2 DEFAULT NULL | Generates instructions to delete a cookie |
Like the procedures of the HTP package, the OWA_COOKIE procedures and functions generate instructions that are sent to the browser for processing. Unlike normal HTML tags, however, these instructions must appear outside the normal document in a section called the HTTP header. To place instructions in the header, we must use the MIME_HEADER and HTTP_HEADER_CLOSE procedures from the OWA_UTIL package. Please see Section 7.4.1.4, "HTML and HTTP utilities", later in this chapter, for a detailed discussion of these procedures.
The following snippet shows how the OWA_UTIL procedures are used to create cookies:
/*
|| FALSE value in mime_header keeps the header open
|| so we can insert the cookie into the header section
*/
OWA_UTIL.mime_header ('text/html', FALSE);
OWA_COOKIE.send ('city', 'BOSTON');
OWA_COOKIE.send ('city', 'CHICAGO');
OWA_COOKIE.send ('city', 'NEW YORK');
OWA_UTIL.http_header_close; -- Now close the header
HTP.print ('<html>');
...This procedure generates the instruction to create a cookie. As noted, this instruction must appear inside the HTTP header. The parameters for the procedure are as follows:
Name of the cookie.
Value of the cookie.
Expiration date; the cookie is deleted after the specified date. If omitted, it never expires. Also note that the time zone must match the settings in OWA_INIT.
If a path is specified, the server sends the cookie only when the URL of the request matches the path; this make the cookie available only to those requests that match the specified path.
Like the path, the server sends the cookie only if the domain (i.e., www.oreilly.com) matches the URL of the request, allowing you to prevent a cookie from being sent if the domain (the server section of the URL) matches the specified path.
If non-NULL, the keyword SECURE is added to the cookie; if added, the cookie is sent only if the client and server are connected through a secure protocol like HTTPS.
SEND produces a string based on the following template:
Set-Cookie: name=value expires=expires path=path domain=domain secure
The following procedure illustrates the use of the SEND procedure:
CREATE OR REPLACE PROCEDURE send_cookie (
cookie_name IN VARCHAR2 DEFAULT NULL,
cookie_val IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
-- Cookies must be set within the header
OWA_UTIL.mime_header ('text/html', FALSE);
-- Send a cookie if a name was entered
IF cookie_name IS NOT NULL
THEN
OWA_COOKIE.send (cookie_name, cookie_val);
END IF;
OWA_UTIL.http_header_close;
END;This procedure causes a cookie to immediately expire and, like SEND, must be used inside the HTTP header. The parameters are:
The name of the cookie to remove.
The value of the cookie to remove.
The path of the cookie to remove.
REMOVE produces the following template:
Set-Cookie: name=value expires=01-JAN-1990 path=path
This function retrieves the value for the specified cookie and returns it as a cookie datatype. Unlike SEND or REMOVE, GET is not limited to the header and may appear anywhere within a procedure. It has one parameter:
Name of the cookie to retrieve.
The following example illustrates the GET procedure:
CREATE OR REPLACE PROCEDURE get_cookie (
cookie_name IN VARCHAR2 DEFAULT NULL
)
IS
target_cookie OWA_COOKIE.cookie;
BEGIN
target_cookie := OWA_COOKIE.get (cookie_name);
-- Print message if the cookie was not found
IF target_cookie.num_vals = 0
THEN
HTP.print ('<h1>Cookie not found!</h1>');
ELSE
HTP.print ('<h1>Values for cookie ' ||
cookie_name ||
'</h1><hr>');
FOR i IN 1 .. target_cookie.num_vals
LOOP
HTP.print (target_cookie.vals (i) || '<p>');
END LOOP;
END IF;
END;This procedure retrieves the names and values for all nonexpired cookies. Its parameters are:
Array of cookie names.
Array of cookie values.
Total number of cookies retrieved.
The following procedure illustrates the GET_ALL procedure:
CREATE OR REPLACE PROCEDURE print_cookies
IS
-- Note that vc_arr is in owa_cookie, not owa_text!
current_cookie_names OWA_COOKIE.vc_arr;
current_cookie_vals OWA_COOKIE.vc_arr;
n INTEGER DEFAULT 0;
BEGIN
-- Fetch and print the current cookies
OWA_COOKIE.get_all (
current_cookie_names,
current_cookie_vals,
n
);
FOR i IN 1 .. n
LOOP
HTP.print ('<b>' || current_cookie_names (i) || ':</b>');
HTP.print ('<b>' || current_cookie_vals (i) || '<p>');
END LOOP;
END;Developing data entry forms with HTML is closely related to the problem of maintaining state. As discussed in Chapter 2, Foundations, forms are processed in two steps. In the first, the form is displayed and the user is allowed to make changes. In the second, once the user has made all desired edits, the form is submitted to another program for processing. This program adds, deletes, or updates the original record. As any client/server developer knows, forms must be able to handle situations in which multiple users attempt to update the same record simultaneously.
There are two different approaches to handling the simultaneous update problem: pessimistic locking and optimistic locking. With pessimistic locking, the record is locked as soon as the user attempts to edit it. If the lock succeeds, other users are unable to make changes until the original user releases the lock.
With optimistic locking, no locks are issued, in the hope (hence the term "optimistic") that someone else won't come along in the interim and make changes to the record. A user makes edits on the screen, and only when the user has finished editing does the system attempt to lock the record and apply the changes. Unfortunately, because the record was not initially locked, other users are free to make changes while the first user is still staring at his screen. If this occurs, the first user must be given a choice about how to proceed; he can choose to overwrite the other user's updates with his own, or choose to discard his changes in favor of the other user's.
HTML's inability to maintain state makes it extremely difficult, if not impossible, to implement pessimistic locking. However, it is relatively straightforward, although a little clumsy, to implement optimistic locking. With this approach, a snapshot is taken of a record before the user makes any changes. When the user submits the form, the original record is requeried and compared to the snapshot. If they are identical (i.e., no one has made intervening changes) the user's edits are saved. Otherwise, the user is asked how to proceed.
The OWA_OPT_LOCK package provides two ways to simplify optimistic locking in HTML forms. With the first method, the record's columns are saved in hidden fields within the form. When the form is submitted, these hidden fields are passed to the new procedure in an array, where they are then compared with the original record. The second method computes a checksum of the original record. This value is compared to a recomputed checksum to determine if the record has been updated.[3]
[3] A checksum is a mathematical function that computes a single, unique value for any input. For example, the sum of a record's bytes is probably unique to that particular record. Real checksum functions, however, are complex enough that even tiny changes to the record result in a different value.
The vcArray array holds the hidden fields that are passed from the data entry form:
TYPE vcarray IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
The procedures and functions in OWA_OPT_LOCK implement the two strategies for optimistic locking. Table 7.14 shows the procedures and functions, along with their parameters. Note that the hidden fields and checksum approaches are two different methods, each with its own distinct set of operations.
| Procedure/ Function | Parameters | Description |
|---|---|---|
| p_owner IN VARCHAR2 p_tname IN VARCHAR2 p_rowid IN VARCHAR2 | Generates a checksum (rather than a hidden field) for each sensitive column of the row being updated | |
| p_values IN owa_opt_ lock.vcArray | Returns the ROWID from fields generated by store_values | |
| p_owner IN VARCHAR2 p_tname IN VARCHAR2 p_rowid IN VARCHAR2 | Generates a hidden field for each column of the row being updated | |
| p_old_values IN owa_opt_ lock.vcArray | Compares old and new values |
This procedure generates a hidden field for each column of the row that is to be updated. Its parameters are:
The schema that owns the table that is to be updated; you can use the reserved word USER to default to the current schema.
The table to be updated.
ROWID of the record in the table that is to be updated; the procedure always uses the ROWID of the row that is to be updated, regardless of the primary key of the table.
Like the HTP procedures, STORE_VALUES generates HTML tags that are returned to the browser. These tags must appear as part of the data entry form that is being used to update a record. The hidden fields generated by STORE_VALUES have the same name: "old_" followed by the name of the table passed in the p_tname parameter. This passes the old values in a single array parameter to the procedure that processes the form.
Here is a sample program that creates a simple data entry form based on the EMP table. The <form> tag's action attribute points us to the procedure that performs the update:
CREATE OR REPLACE PROCEDURE opt_lock_fentry (
iempno IN VARCHAR2 DEFAULT NULL
)
IS
emp_rec scott.emp%ROWTYPE;
rec_row_id ROWID;
BEGIN
-- Fetch the record and rowid the employee with the given id
SELECT *
INTO emp_rec
FROM scott.emp
WHERE emp.empno = iempno;
SELECT ROWID
INTO rec_row_id
FROM scott.emp
WHERE emp.empno = iempno;
-- Create a simple data entry form
HTP.print ('<form action=opt_lock_fupdate>');
HTP.formhidden (cname => 'iempno', cvalue => iempno);
HTP.print ('Employee Name:');
HTP.formtext (cname => 'iename', cvalue => emp_rec.ename);
HTP.print ('Job:');
HTP.formtext (cname => 'ijob', cvalue => emp_rec.job);
/*
|| Store the current values for the row that is to be updated
*/
OWA_OPT_LOCK.store_values ('SCOTT', 'emp', rec_row_id);
HTP.print ('<input type=submit>');
HTP.print ('</form>');
END;The following listing shows what happens when the procedure is executed. The first three fields simply reproduce the original parameters: the schema name, the table name, and the ROWID of the record that is being updated. After these fields, all the columns in the target row are listed:
<FORM action=opt_lock_fupdate> <INPUT TYPE="hidden" NAME="iempno" VALUE="7934"> Employee Name: <INPUT TYPE="text" NAME="iename" VALUE="MILLER"> Job: <INPUT TYPE="text" NAME="ijob" VALUE="CLERK"> <INPUT TYPE="hidden" NAME="old_emp" VALUE="SCOTT"> <INPUT TYPE="hidden" NAME="old_emp" VALUE="emp"> <INPUT TYPE="hidden" NAME="old_emp" VALUE="AAAAeFAACAAAAEbAAN"> <INPUT TYPE="hidden" NAME="old_emp" VALUE="7934"> <INPUT TYPE="hidden" NAME="old_emp" VALUE="MILLER"> <INPUT TYPE="hidden" NAME="old_emp" VALUE="CLERK"> <INPUT TYPE="hidden" NAME="old_emp" VALUE="7566"> <INPUT TYPE="hidden" NAME="old_emp" VALUE="23-JAN-82"> <INPUT TYPE="hidden" NAME="old_emp" VALUE="1300"> <INPUT TYPE="hidden" NAME="old_emp" VALUE=""> <INPUT TYPE="hidden" NAME="old_emp" VALUE="10"> <input type=submit> </form>
This function, used when a form is submitted, compares the old values from the row to the current values. If they match, the function returns TRUE; otherwise, it returns FALSE. Its one parameter is:
The array of field values created by the STORE_VALUES procedure.
The VERIFY_VALUES procedure is meant to work in tandem with the STORE_VALUES procedure. The following example performs the record update started in the earlier example. In addition to a parameter for each input element on the form, we must also include a parameter that receives the values from the STORE_VALUES procedure. The parameter, declared as a vcArray, must have the same name as the hidden fields created by STORE_VALUES:
CREATE OR REPLACE PROCEDURE opt_lock_fupdate (
iempno IN VARCHAR2 DEFAULT NULL,
iename IN VARCHAR2 DEFAULT NULL,
ijob IN VARCHAR2 DEFAULT NULL,
old_emp IN OWA_OPT_LOCK.vcarray
)
IS
BEGIN
IF OWA_OPT_LOCK.verify_values (old_emp)
THEN
-- Perform the update
UPDATE scott.emp
SET emp.ename = iename,
emp.job = ijob
WHERE emp.empno = iempno;
COMMIT;
HTP.print ('<h1>Change Successful</h1>');
ELSE
HTP.print ('<h1>The record has been changed!</h1>');
END IF;
END;This function accepts a vcArray, and returns the ROWID (always in the third element in the array) from the fields generated by the STORE_VALUES procedure. Like VERIFY_VALUES, GET_ROWID is used in the procedure that handles form submission. The function is included as a convenience to save us from having to pass the original primary key of the record we are attempting to update. For example, in the previous procedure, we had to include the empno field both in the data entry form and as a parameter to the submission form (again, due to statelessness) to retain the original primary key. We could have saved a step by omitting empno and using GET_ROWID to retrieve the ROWID of the target record, as illustrated in the following code snippet:
-- old_rowid is a local variable declared as a rowid
old_rowid := OWA_OPT_LOCK.get_rowid (old_emp);
UPDATE scott.emp
SET emp.ename = iename,
emp.job = ijob
WHERE ROWID = old_rowid;This function provides an alternative to the hidden field method that is useful when the underlying table contains sensitive information that might be compromised with the "View Source" browser option. Additionally, for tables with a very large number of columns, a checksum results in a more compact HTML form. This can be an important factor in improving download times, particularly when users are connecting with a modem or a WAN.
The CHECKSUM function has the same parameters as the STORE_VALUE procedure discussed earlier. The function returns a unique value for the values in the target row, and this value is stored as a hidden field within the data entry form. When the form is submitted, the checksum for the target row is recomputed and compared to the old value. If they are the same, then the procedure can apply the user's updates.
The following procedure illustrates how to use the CHECKSUM function when creating a data entry form. Unlike STORE_VALUES, which automatically passes the ROWID or the target record, we must manually include it as a hidden field when using the CHECKSUM approach:
CREATE OR REPLACE PROCEDURE opt_lock_centry (
iempno IN VARCHAR2 DEFAULT NULL
)
IS
emp_rec scott.emp%ROWTYPE;
rec_row_id ROWID;
csum NUMBER;
BEGIN
SELECT *
INTO emp_rec
FROM scott.emp
WHERE emp.empno = iempno;
SELECT ROWID
INTO rec_row_id
FROM scott.emp
WHERE emp.empno = iempno;
-- Create a simple data entry form
HTP.print ('<form action=opt_lock_cupdate>');
HTP.formhidden (cname => 'iempno', cvalue => iempno);
HTP.formhidden (cname => 'irowid', cvalue => rec_row_id);
HTP.print ('Employee Name:');
HTP.formtext (cname => 'iename', cvalue => emp_rec.ename);
HTP.print ('Job:');
HTP.formtext (cname => 'ijob', cvalue => emp_rec.job);
/*
|| Save the row's checksum in a hidden field
*/
csum := OWA_OPT_LOCK.checksum ('SCOTT', 'emp', rec_row_id);
HTP.formhidden (cname => 'iold_checksum', cvalue => csum);
HTP.print ('<input type=submit>');
HTP.print ('</form>');
END;Here is the output of the procedure, showing the hidden field for the checksum of the target row:
<form action=opt_lock_cupdate> <INPUT TYPE="hidden" NAME="iempno" VALUE="7934"> <INPUT TYPE="hidden" NAME="irowid" VALUE="AAAAeFAACAAAAEbAAN"> Employee Name: <INPUT TYPE="text" NAME="iename" VALUE="MILLER"> Job: <INPUT TYPE="text" NAME="ijob" VALUE="CLERK"> <INPUT TYPE="hidden" NAME="iold_checksum" VALUE="7925"> <input type=submit> </form>
The next procedure illustrates how the function is used to process the form data; notice that the parameter name for the checksum must match the name used for the hidden field:
CREATE OR REPLACE PROCEDURE opt_lock_cupdate (
iempno IN VARCHAR2 DEFAULT NULL,
iename IN VARCHAR2 DEFAULT NULL,
ijob IN VARCHAR2 DEFAULT NULL,
iold_checksum IN VARCHAR2 DEFAULT NULL,
irowid IN VARCHAR2 DEFAULT NULL
)
IS
new_checksum NUMBER;
BEGIN
new_checksum :=
OWA_OPT_LOCK.checksum ('SCOTT', 'emp', irowid);
IF (iold_checksum = new_checksum)
THEN
-- Perform the update
UPDATE scott.emp
SET emp.ename = iename,
emp.job = ijob
WHERE emp.empno = iempno;
COMMIT;
HTP.print ('<h1>Change Successful</h1>');
ELSE
HTP.print ('<h1>The record has been changed by another user.</h1>');
END IF;
END;There is a second version of the CHECKSUM function that computes a value for an arbitrary VARCHAR2 string. The following example shows its return value on two strings that differ by just one character:
OWA_OPT_LOCK.checksum ('Hello, World'); -- (csum = 21074)
OWA_OPT_LOCK.checksum ('Hello, Wordl'); -- (csum = 23114) |  | |
| 7.2 Text Processing |  | 7.4 Improving Productivity |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.