Oracle Web Applications: PL/SQL Developer's Introduction

Oracle Web Applications: PL/SQL Developer's IntroductionSearch this book
Previous: 7.2 Text ProcessingChapter 7
The PL/SQL Toolkit
Next: 7.4 Improving Productivity
 

7.3 Maintaining State

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:

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:

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:

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:

In the next section, we'll look at the toolkit package for manipulating cookies.

7.3.1 OWA_COOKIE: Storing Cookies

The OWA_COOKIE package contains procedures that allow us to create, access, and even update cookies within PL/SQL.

7.3.1.1 Data structures

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
);

7.3.1.2 Procedures and functions

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.


Table 7.13: Various OWA_COOKIE Procedures and Functions

Procedure/Function

Parameters

Description

GET

name IN VARCHAR2

Generates instructions to retrieve a specified cookie from the browser and store its value in a cookie variable

GET_ALL

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

SEND

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

REMOVE

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>');
...

7.3.1.2.1 The SEND procedure.

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 IN VARCHAR2

Name of the cookie.

value IN VARCHAR2

Value of the cookie.

expires IN DATE DEFAULT NULL

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.

path IN VARCHAR2 DEFAULT NULL

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.

domain IN VARCHAR2 DEFAULT NULL

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.

SECURE IN VARCHAR2 DEFAULT NULL

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;

7.3.1.2.2 The REMOVE procedure.

This procedure causes a cookie to immediately expire and, like SEND, must be used inside the HTTP header. The parameters are:

name IN VARCHAR2

The name of the cookie to remove.

value IN VARCHAR2

The value of the cookie to remove.

path IN VARCHAR2 DEFAULT NULL

The path of the cookie to remove.

REMOVE produces the following template:

Set-Cookie: name=value expires=01-JAN-1990 path=path

7.3.1.2.3 The GET function.

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 IN VARCHAR2

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;

7.3.1.2.4 The GET_ALL procedure.

This procedure retrieves the names and values for all nonexpired cookies. Its parameters are:

names OUT owa_cookie.vc_arr

Array of cookie names.

vals OUT owa_cookie.vc_arr

Array of cookie values.

num_vals OUT INTEGER

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;

7.3.2 OWA_OPT_LOCK: Record Locking

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.

7.3.2.1 Data structures

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;

7.3.2.2 Procedures and functions

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.


Table 7.14: Various OWA_OPT_LOCK Procedures and Functions

Procedure/

Function

Parameters

Description

CHECKSUM

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

GET_ROWID

p_values IN owa_opt_

lock.vcArray

Returns the ROWID from fields generated by store_values

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

VERIFY_VALUES

p_old_values IN owa_opt_

lock.vcArray

Compares old and new values

7.3.2.2.1 The STORE_VALUES procedure.

This procedure generates a hidden field for each column of the row that is to be updated. Its parameters are:

p_owner IN VARCHAR2

The schema that owns the table that is to be updated; you can use the reserved word USER to default to the current schema.

p_tname IN VARCHAR2

The table to be updated.

p_rowid IN VARCHAR2

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>

7.3.2.2.2 The VERIFY_VALUES function.

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:

p_old_values IN owa_opt_lock.vcArray

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;

7.3.2.2.3 The GET_ROWID function.

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;

7.3.2.2.4 The CHECKSUM function.

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)


Previous: 7.2 Text ProcessingOracle Web Applications: PL/SQL Developer's IntroductionNext: 7.4 Improving Productivity
7.2 Text ProcessingBook Index7.4 Improving Productivity

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference
This HTML Help has been published using the chm2web software.