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