9 Developing PL/SQL Web Applications

PL/SQL Gateway

The PL/SQL gateway enables a web browser to invoke a PL/SQL stored subprogram through an HTTP listener. The gateway is a platform on which PL/SQL users develop and deploy PL/SQL web applications.

PL/SQL Web Toolkit

This set of PL/SQL packages is a generic interface that enables you to use stored subprograms invoked by mod_plsql at run time.

In response to a browser request, a PL/SQL subprogram updates or retrieves data from Oracle Database according to the user input. It then generates an HTTP response to the browser, typically in the form of a file download or HTML to be displayed. The PL/SQL Web Toolkit API enables stored subprograms to perform actions such as:

• Obtain information about an HTTP request

• Generate HTTP headers such as content-type and mime-type

• Set browser cookies

• Generate HTML pages

Table 9-1 describes commonly used PL/SQL Web Toolkit packages.

How Embedded PL/SQL Gateway Processes Client Requests

Figure 9-2 illustrates the process by which the embedded gateway handles client HTTP requests.

Figure 9-2 Processing Client Requests with Embedded PL/SQL Gateway

Description of "Figure 9-2 Processing Client Requests with Embedded PL/SQL Gateway"

The explanation of the steps in Figure 9-2 is as follows:

1. The Oracle XML DB HTTP Listener receives a request from a client browser to request to invoke a PL/SQL subprogram. The subprogram can either be written directly in PL/SQL or indirectly generated when a PL/SQL Server Page is uploaded to the database and compiled.

2. The XML DB HTTP Listener routes the request to the embedded PL/SQL gateway as specified in its virtual-path mapping configuration.

3. The embedded gateway uses the HTTP request information and the gateway configuration to determine which database account to use for authentication.

4. The embedded gateway prepares the call parameters and invokes the PL/SQL subprogram in the application.

5. The PL/SQL subprogram generates an HTML page out of relational data and the PL/SQL Web Toolkit accessed from the database.

6. The application sends the page to the embedded gateway.

7. The embedded gateway sends the page to the XML DB HTTP Listener.

8. The XML DB HTTP Listener sends the page to the client browser.

Unlike mod_plsql, the embedded gateway processes HTTP requests with the Oracle XML DB Listener. This listener is the same server-side process as the Oracle Net Listener and supports Oracle Net Services, HTTP, and FTP.

Configure general HTTP listener settings through the XML DB interface (for instructions, see Oracle XML DB Developer's Guide). Configure the HTTP listener either by using Oracle Enterprise Manager or by editing the xdbconfig.xml file. Use the DBMS_EPG package for all embedded PL/SQL gateway configuration, for example, creating or setting attributes for a DAD.

Installing Embedded PL/SQL Gateway

The embedded gateway requires these components:

• XML DB HTTP Listener

• PL/SQL Web Toolkit

The embedded PL/SQL gateway is installed as part of Oracle XML DB. If you are using a preconfigured database created during an installation or by the Database Configuration Assistant (DBCA), then Oracle XML DB is installed and configured. For information about manually adding Oracle XML DB to an existing database, see Oracle XML DB Developer's Guide.

The PL/SQL Web Toolkit is part of the standard installation of the database, so no supplementary installation is necessary.

Configuring Embedded PL/SQL Gateway

You configure mod_plsql by editing the Oracle HTTP Server configuration files. Because the embedded gateway is installed as part of the Oracle XML DB HTTP Listener, you manage the embedded gateway as a servlet through the Oracle XML DB servlet management interface.

The configuration interface to the embedded gateway is the PL/SQL package DBMS_EPG. This package modifies the underlying xdbconfig.xml configuration file that XML DB uses. The default values of the embedded gateway configuration parameters are sufficient for most users.

Topics:

• Configuring Embedded PL/SQL Gateway: Overview

• Configuring User Authentication for Embedded PL/SQL Gateway

------------------------------------------------------------------------
--- DAD with static authentication
------------------------------------------------------------------------

CONNECT SYSTEM AS SYSDBA
PASSWORD: password
EXEC DBMS_EPG.CREATE_DAD('Static_Auth_DAD', '/static/*');
EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('Static_Auth_DAD', 'database-username', 'HR');
GRANT EXECUTE ON DBMS_EPG TO HR;

-- Authorization
CONNECT HR
PASSWORD: password
EXEC DBMS_EPG.AUTHORIZE_DAD('Static_Auth_DAD');

------------------------------------------------------------------------
-- DAD with dynamic authentication
------------------------------------------------------------------------

CONNECT SYSTEM AS SYSDBA
PASSWORD: password
EXEC DBMS_EPG.CREATE_DAD('Dynamic_Auth_DAD', '/dynamic/*');

-------------------------------------------------------------------------
-- DAD with dynamic authentication restricted
-------------------------------------------------------------------------

EXEC DBMS_EPG.CREATE_DAD('Dynamic_Auth_DAD_Restricted', '/dynamic/*');
EXEC DBMS_EPG.SET_DAD_ATTRIBUTE
  ('Dynamic_Auth_DAD_Restricted', 'database-username', 'HR');

REM Create DAD with static authentication for database user HR

CONNECT SYSTEM AS SYSDBA
PASSWORD: password
EXEC DBMS_EPG.CREATE_DAD('Static_Auth_DAD', '/static/*');
EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('Static_Auth_DAD', 'database-username', 'HR');
GRANT EXECUTE ON DBMS_EPG TO HR;

REM Database user HR authorizes DAD that does not exist

CONNECT HR
PASSWORD: password
EXEC DBMS_EPG.AUTHORIZE_DAD('Static_Auth_DAD_Typo');

REM Database user OE authorizes DAD with database-username 'HR'

CONNECT OE
PASSWORD: password
EXEC DBMS_EPG.AUTHORIZE_DAD('Static_Auth_DAD');

CREATE OR REPLACE PROCEDURE show_dad_auth_status (p_dadname VARCHAR2) IS
  v_daduser VARCHAR2(32);
  v_cnt     PLS_INTEGER;
BEGIN
  -- Determine DAD user
  v_daduser := DBMS_EPG.GET_DAD_ATTRIBUTE(p_dadname, 'database-username');

  -- Determine whether DAD authorization exists for DAD user
  SELECT COUNT(*)
    INTO v_cnt
      FROM DBA_EPG_DAD_AUTHORIZATION da
        WHERE da.DAD_NAME = p_dadname
          AND da.USERNAME = v_daduser;

  -- If DAD authorization exists for DAD user, authentication mode is static
  IF (v_cnt > 0) THEN
    DBMS_OUTPUT.PUT_LINE (
      '''' || p_dadname ||
      ''' is set up for static authentication for user ''' ||
      v_daduser || '''.');
    RETURN;
  END IF;

  -- If no DAD authorization exists for DAD user, authentication mode is dynamic

  -- Determine whether dynamic authentication is restricted to particular user
  IF (v_daduser IS NOT NULL) THEN
    DBMS_OUTPUT.PUT_LINE (
      '''' || p_dadname ||
      ''' is set up for dynamic authentication for user ''' ||
      v_daduser || ''' only.');
  ELSE
    DBMS_OUTPUT.PUT_LINE (
      '''' || p_dadname ||
      ''' is set up for dynamic authentication for any user.');
  END IF;
END;
/

SET SERVEROUTPUT ON;
BEGIN
  show_dad_auth_status('Static_Auth_DAD');
END;
/
'Static_Auth_DAD' is set up for static authentication for user 'HR'.

DECLARE
  v_dad_names DBMS_EPG.VARCHAR2_TABLE;
BEGIN
  DBMS_OUTPUT.PUT_LINE
    ('---------- Authorization Status for All DADs ----------');
  DBMS_EPG.GET_DAD_LIST(v_dad_names);

  FOR i IN 1..v_dad_names.count LOOP
    show_dad_auth_status(v_dad_names(i));
  END LOOP;
END;
/

---------- Authorization Status for All DADs ----------
'Static_Auth_DAD' is set up for static auth for user 'HR'.
'Dynamic_Auth_DAD' is set up for dynamic auth for any user.
'Dynamic_Auth_DAD_Restricted' is set up for dynamic auth for user 'HR' only.

DECLARE
  v_dad_names DBMS_EPG.VARCHAR2_TABLE;
  v_dad_user  VARCHAR2(32);
  v_dad_found BOOLEAN;
BEGIN 
  DBMS_OUTPUT.PUT_LINE
    ('---------- DAD Authorizations Not in Effect ----------');
  DBMS_EPG.GET_DAD_LIST(v_dad_names);

  FOR r IN (SELECT * FROM DBA_EPG_DAD_AUTHORIZATION) LOOP  -- Outer loop
    v_dad_found := FALSE;
    FOR i IN 1..v_dad_names.count LOOP  -- Inner loop
      IF (r.DAD_NAME = v_dad_names(i)) THEN
        v_dad_user :=
          DBMS_EPG.GET_DAD_ATTRIBUTE(r.DAD_NAME, 'database-username');

        -- Is database-username the user for whom DAD is authorized?
        IF (r.USERNAME <> v_dad_user) THEN
          DBMS_OUTPUT.PUT_LINE (
            'DAD authorization of ''' || r.dad_name ||
            ''' by user ''' || r.username || '''' ||
            ' is not in effect because DAD user is ' ||
            '''' || v_dad_user || '''.');
        END IF;
        v_dad_found := TRUE;
        EXIT;  -- Inner loop
      END IF;
    END LOOP;  -- Inner loop

    -- Does DAD exist?
    IF (NOT v_dad_found) THEN
      DBMS_OUTPUT.PUT_LINE (
       'DAD authorization of ''' || r.dad_name ||
       ''' by user ''' || r.username ||
       ''' is not in effect because the DAD does not exist.');
    END IF;
  END LOOP;  -- Outer loop
END;
/

---------- DAD Authorizations Not in Effect ----------
DAD authorization of 'Static_Auth_DAD' by user 'OE' is not in effect
  because DAD user is 'HR'.
DAD authorization of 'Static_Auth_DAD_Typo' by user 'HR' is not in effect
  because DAD does not exist.

$ORACLE_HOME/rdbms/admin/epgstat.sql

@$ORACLE_HOME/rdbms/admin/epgstat.sql

+--------------------------------------+
| XDB protocol ports:                  |
|  XDB is listening for the protocol   |
|  when the protocol port is nonzero.  |
+--------------------------------------+

HTTP Port FTP Port
--------- --------
        0        0

1 row selected.

+---------------------------+
| DAD virtual-path mappings |
+---------------------------+

Virtual Path                     DAD Name
-------------------------------- --------------------------------
/dynamic/*                       Dynamic_Auth_DAD_Restricted
/static/*                        Static_Auth_DAD

2 rows selected.

+----------------+
| DAD attributes |
+----------------+

DAD Name        DAD Param             DAD Value
------------    --------------------- ----------------------------------------
Dynamic_Auth    database-username     HR
_DAD_Restric
ted

Static_Auth_    database-username     HR
DAD


2 rows selected.

+---------------------------------------------------+
| DAD authorization:                                |
|  To use static authentication of a user in a DAD, |
|  the DAD must be authorized for the user.         |
+---------------------------------------------------+

DAD Name                         User Name
-------------------------------- --------------------------------
Static_Auth_DAD                  HR
                                 OE
Static_Auth_DAD_Typo             HR

3 rows selected.

+----------------------------+
| DAD authentication schemes |
+----------------------------+

DAD Name             User Name                        Auth Scheme
-------------------- -------------------------------- ------------------
Dynamic_Auth_DAD                                      Dynamic
Dynamic_Auth_DAD_Res HR                               Dynamic Restricted
tricted

Static_Auth_DAD      HR                               Static

3 rows selected.

+--------------------------------------------------------+
| ANONYMOUS user status:                                 |
|  To use static or anonymous authentication in any DAD, |
|  the ANONYMOUS account must be unlocked.               |
+--------------------------------------------------------+

Database User   Status
--------------- --------------------
ANONYMOUS       EXPIRED & LOCKED

1 row selected.

+-------------------------------------------------------------------+
| ANONYMOUS access to XDB repository:                               |
|  To allow public access to XDB repository without authentication, |
|  ANONYMOUS access to the repository must be allowed.              |
+-------------------------------------------------------------------+

Allow repository anonymous access?
----------------------------------
false

1 row selected.

Invoking PL/SQL Stored Subprograms Through Embedded PL/SQL Gateway

The basic steps for invoking PL/SQL subprograms through the embedded PL/SQL gateway are the same as for the mod_plsql gateway. See Oracle HTTP Server mod_plsql User's Guide for instructions. You must adapt the mod_plsql instructions slightly for use with the embedded gateway. For example, invoke the embedded gateway in a browser by entering the URL in this format:

protocol://hostname[:port]/virt-path/[[!][schema.][package.]proc_name[?query_str]]

The placeholder virt-path stands for the virtual path that you configured in DBMS_EPG.CREATE_DAD. The mod_plsql documentation uses DAD_location instead of virt-path.

These topics documented in Oracle HTTP Server mod_plsql User's Guide apply equally to the embedded gateway:

• Transaction mode

• Supported data types

• Parameter-passing scheme

• File upload and download support

• Path-aliasing

• Common Gateway Interface (CGI) environment variables

Securing Application Access with Embedded PL/SQL Gateway

The embedded gateway shares the same protection mechanism with mod_plsql. See Oracle HTTP Server mod_plsql User's Guide for instructions.

Restrictions in Embedded PL/SQL Gateway

The mod_plsql restrictions documented in the first chapter of Oracle HTTP Server mod_plsql User's Guide apply equally to the embedded gateway. In addition, the embedded version of the gateway does not support these features:

• Dynamic HTML caching

• System monitoring

• Authentication modes other than Basic

  For information about authentication modes, see Oracle HTTP Server mod_plsql User's Guide.

Using Embedded PL/SQL Gateway: Scenario

This section illustrates how to write a simple application that queries the hr.employees table and delivers HTML output to a web browser through the PL/SQL gateway. It assumes that you have both XML DB and the sample schemas installed.

To write and run the program follow these steps:

1. Log on to the database as a user with ALTER USER privileges and ensure that the database account ANONYMOUS is unlocked. The ANONYMOUS account, which is locked by default, is required for static authentication. If the account is locked, then use this SQL statement to unlock it:

   ALTER USER anonymous ACCOUNT UNLOCK;
   

2. Log on to the database as an XML DB administrator, that is, a user with the XDBADMIN role.

   To determine which users and roles were granted the XDADMIN role, query the data dictionary:

   SELECT *
   FROM DBA_ROLE_PRIVS
   WHERE GRANTED_ROLE = 'XDBADMIN';
   

3. Create the DAD. For example, this procedure creates a DAD invoked HR_DAD and maps the virtual path to /plsql/:

   EXEC DBMS_EPG.CREATE_DAD('HR_DAD', '/plsql/*');
   

4. Set the DAD attribute database-username to the database user whose privileges must be used by the DAD. For example, this procedure specifies that the DAD HR_DAD accesses database objects with the privileges of user HR:

   EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('HR_DAD', 'database-username', 'HR');
   

   The attribute database-username is case-sensitive.

5. Grant EXECUTE privilege to the database user whose privileges must be used by the DAD (so that he or she can authorize the DAD). For example:

   GRANT EXECUTE ON DBMS_EPG TO HR;
   

6. Log off as the XML DB administrator and log on to the database as the database user whose privileges must be used by the DAD (for example, HR).

7. Authorize the embedded PL/SQL gateway to invoke procedures and access document tables through the DAD. For example:

   EXEC DBMS_EPG.AUTHORIZE_DAD('HR_DAD');
   

8. Create a sample PL/SQL stored procedure invoked print_employees. This program creates an HTML page that includes the result set of a query of hr.employees:

   CREATE OR REPLACE PROCEDURE print_employees IS
     CURSOR emp_cursor IS
       SELECT last_name, first_name
         FROM hr.employees
           ORDER BY last_name;
   BEGIN
     HTP.PRINT('<html>');
     HTP.PRINT('<head>');
     HTP.PRINT('<meta http-equiv="Content-Type" content="text/html">');
     HTP.PRINT('<title>List of Employees</title>');
     HTP.PRINT('</head>'); 
     HTP.PRINT('<body TEXT="#000000" BGCOLOR="#FFFFFF">');
     HTP.PRINT('<h1>List of Employees</h1>');
     HTP.PRINT('<table width="40%" border="1">');
     HTP.PRINT('<tr>');
     HTP.PRINT('<th align="left">Last Name</th>');
     HTP.PRINT('<th align="left">First Name</th>');
     HTP.PRINT('</tr>');
     FOR emp_record IN emp_cursor LOOP
       HTP.PRINT('<tr>');
       HTP.PRINT('<td>' || emp_record.last_name  || '</td>');
       HTP.PRINT('<td>' || emp_record.first_name || '</td>');
     END LOOP;
     HTP.PRINT('</table>');
     HTP.PRINT('</body>');
     HTP.PRINT('</html>');
   END;
   /
   

9. Ensure that the Oracle Net listener can accept HTTP requests. You can determine the status of the listener on Linux and UNIX by running this command at the system prompt:

   lsnrctl status | grep HTTP
   

   Output (reformatted from a single line to multiple lines from page size constraints):

   (DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcp)(HOST=example.com)(PORT=8080))
     (Presentation=HTTP)
     (Session=RAW)
   )
   

   If you do not see the HTTP service started, then you can add these lines to your initialization parameter file (replacing listener_name with the name of your Oracle Net local listener), then restart the database and the listener:

   dispatchers="(PROTOCOL=TCP)"
   local_listener=listener_name
   

10. Run the print_employees program from your web browser. For example, you can use this URL, replacing host with the name of your host computer and port with the value of the PORT parameter in the previous step:

    http://host:port/plsql/print_employees
    

    For example, if your host is test.com and your HTTP port is 8080, then enter:

    http://example.com:8080/plsql/print_employees
    

    The web browser returns an HTML page with a table that includes the first and last name of every employee in the hr.employees table.

Passing List and Dropdown-List Parameters from an HTML Form

List boxes and drop-down lists are implemented with the HTML tag <SELECT>.

Use a list box for a large number of choices or to allow multiple selections. List boxes are good for showing items in alphabetical order so that users can find an item quickly without reading all the choices.

Use a drop-down list in these situations:

• There are a small number of choices

• Screen space is limited.

• Choices are in an unusual order.

The drop-down captures the attention of first-time users and makes them read the items. If you keep the choices and order consistent, then users can memorize the motion of selecting an item from the drop-down list, allowing them to make selections quickly as they gain experience. Example 9-9 shows a simple drop-down list.

<form>
<select name="seasons">
<option value="winter">Winter
<option value="spring">Spring
<option value="summer">Summer
<option value="fall">Fall
</select>

Passing Option and Check Box Parameters from an HTML Form

Options pass either a null value (if none of the options in a group is checked), or the value specified on the option that is checked.

To specify a default value for a set of options, you can include the CHECKED attribute in anINPUT tag, or include a DEFAULT clause on the parameter within the stored subprogram. When setting up a group of options, be sure to include a choice that indicates "no preference", because after selecting a option, the user can select a different one, but cannot clear the selection completely. For example, include a "Don't Care" or "Don't Know" selection along with "Yes" and "No" choices, in case someone makes a selection and then realizes it was wrong.

Check boxes need special handling, because your stored subprogram might receive a null value, a single value, or multiple values:

All the check boxes with the same NAME attribute comprise a check box group. If none of the check boxes in a group is checked, the stored subprogram receives a null value for the corresponding parameter.

If one check box in a group is checked, the stored subprogram receives a single VARCHAR2 parameter.

If multiple check boxes in a group are checked, the stored subprogram receives a parameter with the PL/SQL type TABLE OF VARCHAR2. You must declare a type like TABLE OF VARCHAR2, or use a predefined one like OWA_UTIL.IDENT_ARR. To retrieve the values, use a loop:

CREATE OR REPLACE PROCEDURE handle_checkboxes (
  checkboxes owa_util.ident_arr
) AS
BEGIN
  FOR i IN 1..checkboxes.count
  LOOP
    htp.print('<p>Check Box value: ' || checkboxes(i));
  END LOOP;
END;
/

Passing Entry-Field Parameters from an HTML Form

Entry fields require the most validation, because a user might enter data in the wrong format, out of range, and so on. If possible, validate the data on the client side using a client-side JavaScript function, and format it correctly for the user or prompt them to enter it again.

For example:

• You might prevent the user from entering alphabetic characters in a numeric entry field, or from entering characters after reaching a length limit.

• You might silently remove spaces and dashes from a credit card number if the stored subprogram expects the value in that format.

• You might inform the user immediately when they type a number that is too large, so that they can retype it.

Because you cannot always rely on such validation to succeed, code the stored subprograms to deal with these cases anyway. Rather than forcing the user to use the Back button when they enter wrong data, display a single page with an error message and the original form with all the other values filled in.

For sensitive information such as passwords, a special form of the entry field, <INPUT TYPE=PASSWORD>, hides the text as it is typed in.

The procedure in Example 9-10 accepts two strings as input. The first time the procedure is invoked, the user sees a simple form prompting for the input values. When the user submits the information, the same procedure is invoked again to check if the input is correct. If the input is OK, the procedure processes it. If not, the procedure prompts for input, filling in the original values for the user.

DROP TABLE name_zip_table;
CREATE TABLE name_zip_table (
  name     VARCHAR2(100),
  zipcode  NUMBER
);

-- Store a name and associated zip code in the database.

CREATE OR REPLACE PROCEDURE associate_name_with_zipcode
  (name VARCHAR2 := NULL,
   zip  VARCHAR2 := NULL)
AS
BEGIN
  -- Each entry field must contain a value. Zip code must be 6 characters.
  -- (In a real program you perform more extensive checking.)

  IF name IS NOT NULL AND zip IS NOT NULL AND length(zip) = 6 THEN
    INSERT INTO name_zip_table (name, zipcode) VALUES (name, zip);

    HTP.PRINT('<p>The person ' || HTP.ESCAPE_SC(name) ||
              ' has the zip code ' || HTP.ESCAPE_SC(zip) || '.');

    -- If input was OK, stop here. User does not see form again.
    RETURN;
  END IF;

  -- If user entered incomplete or incorrect data, show error message.

  IF (name IS NULL AND zip IS NOT NULL)
    OR (name IS NOT NULL AND zip IS NULL)
      OR (zip IS NOT NULL AND length(zip) != 6)
  THEN
    HTP.PRINT('<p><b>Please reenter data. Fill all fields,
               and use 6-digit zip code.</b>');
  END IF;

  -- If user entered no data or incorrect data, show error message
  -- & make form invoke same procedure to check input values.

  HTP.FORMOPEN('HR.associate_name_with_zipcode', 'GET');
  HTP.PRINT('<p>Enter your name:</td>');

  HTP.PRINT('<td valign=center><input type=text name=name value="' ||
            HTP.ESCAPE_SC(name) || '">');

  HTP.PRINT('<p>Enter your zip code:</td>');

  HTP.PRINT('<td valign=center><input type=text name=zip value="' ||
            HTP.ESCAPE_SC(zip) || '">');

  HTP.FORMSUBMIT(NULL, 'Submit');
  HTP.FORMCLOSE;
END;
/

Passing Hidden Parameters from an HTML Form

One technique for passing information through a sequence of stored subprograms, without requiring the user to specify the same choices each time, is to include hidden parameters in the form that invokes a stored subprogram. The first stored subprogram places information, such as a user name, into the HTML form that it generates. The value of the hidden parameter is passed to the next stored subprogram, as if the user had entered it through a option or entry field.

Other techniques for passing information from one stored subprogram to another include:

• Sending a "cookie" containing the persistent information to the browser. The browser then sends this same information back to the server when accessing other web pages from the same site. Cookies are set and retrieved through the HTTP headers that are transferred between the browser and the web server before the HTML text of each web page.

• Storing the information in the database itself, where later stored subprograms can retrieve it. This technique involves some extra overhead on the database server, and you must still find a way to keep track of each user as multiple users access the server at the same time.

Uploading a File from an HTML Form

You can use an HTML form to choose a file on a client system, and transfer it to the server. A stored subprogram can insert the file into the database as a CLOB, BLOB, or other type that can hold large amounts of data.

The PL/SQL Web Toolkit and the PL/SQL gateway have the notion of a "document table" that holds uploaded files.

Submitting a Completed HTML Form

By default, an HTML form must have a Submit button, which transmits the data from the form to a stored subprogram or CGI program. You can label this button with text of your choice, such as "Search", "Register", and so on.

You can have multiple forms on the same page, each with its own form elements and Submit button. You can even have forms consisting entirely of hidden parameters, where the user makes no choice other than clicking the button.

Using JavaScript or other scripting languages, you can eliminate the Submit button and have the form submitted in response to some other action, such as selecting from a drop-down list. This technique is best when the user only makes a single selection, and the confirmation step of the Submit button is not essential.

Handling Missing Input from an HTML Form

When an HTML form is submitted, your stored subprogram receives null parameters for any form elements that are not filled in. For example, null parameters can result from an empty entry field, a set of check boxes, options, or list items with none checked, or a VALUE parameter of "" (empty quotation marks).

Regardless of any validation you do on the client side, always code stored subprograms to handle the possibility that some parameters are null:

• Specify an initial value in all parameter declarations, to prevent an exception when the stored subprogram is invoked with a missing form parameter. You can set the initial value to zero for numeric values (when that makes sense), and to NULL when you want to check whether the user actually specifies a value.

• Before using an input parameter value that has the initial value NULL, check if it is null.

• Make the subprogram generate sensible results even when not all input parameters are specified. You might leave some sections out of a report, or display a text string or image in a report to indicate where parameters were not specified.

• Provide a way to fill in the missing values and run the stored subprogram again, directly from the results page. For example, include a link that invokes the same stored subprogram with an additional parameter, or display the original form with its values filled in as part of the output.

Maintaining State Information Between Web Pages

Web applications are particularly concerned with the idea of state, the set of data that is current at a particular moment in time. It is easy to lose state information when switching from one web page to another, which might result in asking the user to make the same choices over and over.

You can pass state information between dynamic web pages using HTML forms. The information is passed as a set of name-value pairs, which are turned into stored subprogram parameters for you.

If the user has to make multiple selections, or one selection from many choices, or it is important to avoid an accidental selection, use an HTML form. After the user makes and reviews all the choices, they confirm the choices with the Submit button. Subsequent pages can use forms with hidden parameters (<INPUT TYPE=HIDDEN> tags) to pass these choices from one page to the next.

If the user is only considering one or two choices, or the decision points are scattered throughout the web page, you can save the user from hunting around for the Submit button by representing actions as hyperlinks and including any necessary name-value pairs in the query string (the part after the ? within a URL).

An alternative way to main state information is to use Oracle Application Server and its mod_ose module. This approach lets you store state information in package variables that remain available as a user moves around a web site.

http://www.oracle.com/technetwork/indexes/documentation/index.html

Sending Email from PL/SQL

Using the UTL_SMTP package, a PL/SQL subprogram can send email, as in Example 9-11.

CREATE OR REPLACE PROCEDURE send_test_message
IS
  mailhost   VARCHAR2(64) := 'mailhost.example.com';
  sender     VARCHAR2(64) := 'me@example.com';
  recipient  VARCHAR2(64) := 'you@example.com';
  mail_conn  UTL_SMTP.CONNECTION;
BEGIN
  mail_conn := UTL_SMTP.OPEN_CONNECTION(mailhost, 25); -- 25 is the port
  UTL_SMTP.HELO(mail_conn, mailhost);
  UTL_SMTP.MAIL(mail_conn, sender);
  UTL_SMTP.RCPT(mail_conn, recipient);
 
  UTL_SMTP.OPEN_DATA(mail_conn);
  UTL_SMTP.WRITE_DATA(mail_conn, 'This is a test message.' || chr(13));
  UTL_SMTP.WRITE_DATA(mail_conn, 'This is line 2.' || chr(13));
  UTL_SMTP.CLOSE_DATA(mail_conn);
 
  /* If message were in single string, open_data(), write_data(),
     and close_data() could be in a single call to data(). */
 
  UTL_SMTP.QUIT(mail_conn);
EXCEPTION
  WHEN OTHERS THEN
   -- Insert error-handling code here
   RAISE;
END;
/

Getting a Host Name or Address from PL/SQL

Using the UTL_INADDR package, a PL/SQL subprogram can determine the host name of the local system or the IP address of a given host name.

Using TCP/IP Connections from PL/SQL

Using the UTL_TCP package, a PL/SQL subprogram can open TCP/IP connections to systems on the network, and read or write to the corresponding sockets.

Retrieving HTTP URL Contents from PL/SQL

Using the UTL_HTTP package, a PL/SQL subprogram can:

• Retrieve the contents of an HTTP URL

  The contents are usually in the form of HTML-tagged text, but might be any kind of file that can be downloaded from a web server (for example, plain text or a JPEG image).

• Control HTTP session details (such as headers, cookies, redirects, proxy servers, IDs and passwords for protected sites, and CGI parameters)

• Speed up multiple accesses to the same web site, using HTTP 1.1 persistent connections

A PL/SQL subprogram can construct and interpret URLs for use with the UTL_HTTP package by using the functions UTL_URL.ESCAPE and UTL_URL.UNESCAPE.

The PL/SQL procedure in Example 9-12 uses the UTL_HTTP package to retrieve the contents of an HTTP URL.

CREATE OR REPLACE PROCEDURE show_url
  (url      IN VARCHAR2,
  username IN VARCHAR2 := NULL,
  password IN VARCHAR2 := NULL)
AS
  req        UTL_HTTP.REQ;
  resp       UTL_HTTP.RESP;
  name_      VARCHAR2(256);
  value_     VARCHAR2(1024);
  data_      VARCHAR2(255);
  my_scheme  VARCHAR2(256);
  my_realm   VARCHAR2(256);
  my_proxy   BOOLEAN;
BEGIN
  -- When going through a firewall, pass requests through this host.
  -- Specify sites inside the firewall that do not need the proxy host.
 
  UTL_HTTP.SET_PROXY('proxy.example.com', 'corp.example.com');
 
  -- Ask UTL_HTTP not to raise an exception for 4xx and 5xx status codes,
  -- rather than just returning the text of the error page.
 
  UTL_HTTP.SET_RESPONSE_ERROR_CHECK(FALSE);
 
  -- Begin retrieving this web page.
 req := UTL_HTTP.BEGIN_REQUEST(url);
 
  -- Identify yourself.
  --  Some sites serve special pages for particular browsers.
  UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
 
  -- Specify user ID and password for pages that require them.
  IF (username IS NOT NULL) THEN
    UTL_HTTP.SET_AUTHENTICATION(req, username, password);
  END IF;
 
  -- Start receiving the HTML text.
  resp := UTL_HTTP.GET_RESPONSE(req);
 
  -- Show status codes and reason phrase of response.
  DBMS_OUTPUT.PUT_LINE('HTTP response status code: ' || resp.status_code);
  DBMS_OUTPUT.PUT_LINE
    ('HTTP response reason phrase: ' || resp.reason_phrase);
 
  -- Look for client-side error and report it.
  IF (resp.status_code >= 400) AND (resp.status_code <= 499) THEN
 
  -- Detect whether page is password protected
  -- and you didn't supply the right authorization.
 
  IF (resp.status_code = UTL_HTTP.HTTP_UNAUTHORIZED) THEN
  UTL_HTTP.GET_AUTHENTICATION(resp, my_scheme, my_realm, my_proxy);
  IF (my_proxy) THEN
    DBMS_OUTPUT.PUT_LINE('Web proxy server is protected.');
    DBMS_OUTPUT.PUT('Please supply the required ' || my_scheme ||
      ' authentication username for realm ' || my_realm ||
      ' for the proxy server.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Web page ' || url || ' is protected.');
    DBMS_OUTPUT.PUT('Please supplied the required ' || my_scheme ||
      ' authentication username for realm ' || my_realm ||
      ' for the web page.');
  END IF;
  ELSE
    DBMS_OUTPUT.PUT_LINE('Check the URL.');
  END IF;
 
  UTL_HTTP.END_RESPONSE(resp);
    RETURN;
 
  -- Look for server-side error and report it.
  ELSIF (resp.status_code >= 500) AND (resp.status_code <= 599) THEN
    DBMS_OUTPUT.PUT_LINE('Check if the web site is up.');
    UTL_HTTP.END_RESPONSE(resp);
    RETURN;
  END IF;
 
  -- HTTP header lines contain information about cookies, character sets,
  -- and other data that client and server can use to customize each
  -- session.
 
  FOR i IN 1..UTL_HTTP.GET_HEADER_COUNT(resp) LOOP
    UTL_HTTP.GET_HEADER(resp, i, name_, value_);
    DBMS_OUTPUT.PUT_LINE(name_ || ': ' || value_);
  END LOOP;
 
  -- Read lines until none are left and an exception is raised.
  LOOP
    UTL_HTTP.READ_LINE(resp, value_);
    DBMS_OUTPUT.PUT_LINE(value_);
  END LOOP;
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
  UTL_HTTP.END_RESPONSE(resp);
END;
/

This block shows examples of calls to the procedure in Example 9-12, but the URLs are for nonexistent pages. Substitute URLs from your own web server.

BEGIN
  show_url('http://www.oracle.com/no-such-page.html');
  show_url('http://www.oracle.com/protected-page.html');
  show_url
    ('http://www.oracle.com/protected-page.html','username','password');
END;
/

Using Tables, Image Maps, Cookies, and CGI Variables from PL/SQL

Using packages supplied by Oracle, and the mod_plsql plug-in of Oracle HTTP Server (OHS), a PL/SQL subprogram can format the results of a query in an HTML table, produce an image map, set and get HTTP cookies, check the values of CGI variables, and perform other typical web operations.

Documentation for these packages is not part of the database documentation library. The location of the documentation depends on your application server. To get started with these packages, look at their subprogram names and parameters using the SQL*Plus DESCRIBE statement:

DESCRIBE HTP;
DESCRIBE HTF;
DESCRIBE OWA_UTIL;