This article expands on on the Database Design Pattern: The Lookup Table article from last month. You can check that article for the merits of using lookup tables.

The key to using lookup tables that serve multiple purposes is defining part of the natural key as a non-unique super key. That super key allows you to return unique results sets from a single table. Each result set could be in its own table but that causes you to create a lot of small tables. The natural key in a WHERE clause lets you discover the surrogate key, which you should use as the primary key.

This article shows how you can rewrite the examples from that article into HTML fragment. Then, you can use the HTML fragment in a web form.

This article has two parts. They are:

  • How to write an HTML fragment from a lookup table
  • How to use an HTML fragment in a web form

The first part shows you how to create a list of OPTION tags, and the second shows you how to use the OPTION tags in a web form.

How to write an HTML fragment from a lookup table

The lookup table in this article differs slightly from the one in the other article. The lookup table is renamed the common_lookup table, and several additional columns have been added. For example, the common_lookup table includes the four common columns that audit who created and last updated each row in the table.

This is the description of the common_lookup table:

SQL> desc common_lookup
 Name                                  Null?    Type
 ----------------------------------------- -------- ----------------------------
 COMMON_LOOKUP_ID       NOT NULL NUMBER
 COMMON_LOOKUP_TABLE    NOT NULL VARCHAR2(30)
 COMMON_LOOKUP_COLUMN   NOT NULL VARCHAR2(30)
 COMMON_LOOKUP_TYPE     NOT NULL VARCHAR2(30)
 COMMON_LOOKUP_CODE              VARCHAR2(8)
 COMMON_LOOKUP_MEANING  NOT NULL VARCHAR2(255)
 CREATED_BY             NOT NULL NUMBER
 CREATION_DATE          NOT NULL DATE
 LAST_UPDATED_BY        NOT NULL NUMBER
 LAST_UPDATE_DATE       NOT NULL DATE

You have two options when you query a common_lookup table. One is to read the rows one line at a time and the other read is to read all the rows through a BULK COLLECT operation. The article shows you both approaches.

The first approach uses a line-by-line approach. It takes two mandatory parameters, which yields an empty web form. The third parameter is the lookup key, which would be a foreign key column value. The fourth parameter is an HTML class attribute value. The HTML class attribute would let you create floating menus.

The following creates a working function that returns an OPTION tag list:

SQL> CREATE OR REPLACE FUNCTION get_option_list
  2    ( pv_table_name  VARCHAR2
  3    , pv_column_name  VARCHAR2
  4    , pv_lookup_key   NUMBER := 0
  5    , pv_class_name   VARCHAR2 := NULL ) RETURN VARCHAR2 IS
  6
  7    /* Return HTML snippet. */
  8    lv_html  VARCHAR2(4000);
  9
 10    /* Declare a dynamic cursor. */
 11    CURSOR cv_list
 12    ( cv_table_name   VARCHAR2
 13    , cv_column_name  VARCHAR2 ) IS
 14    SELECT   cl.common_lookup_id
 15    ,        cl.common_lookup_type
 16    ,        cl.common_lookup_meaning
 17    FROM     common_lookup cl
 18    WHERE    cl.common_lookup_table = cv_table_name
 19    AND      cl.common_lookup_column = cv_column_name
 20    ORDER BY cl.common_lookup_meaning;
 21
 22  BEGIN
 23
 24    /* Set initial value when no foreign key exists. */
 25    IF pv_lookup_key = 0 THEN
 26      lv_html := '<option class="||pv_class_name||"'
 27      ||' value="" selected>Select Type &nbsp;&nbsp;&nbsp;&nbsp;</option>';
 28    ELSE
 29      lv_html := '<option class="'||pv_class_name||'"'
 30      ||' value="">Select Type &nbsp;&nbsp;&nbsp;&nbsp;</option>';
 31    END IF;
 32

 33    FOR i IN cv_list(pv_table_name, pv_column_name) LOOP
 34      /* Set subsequent value when foreign key is set. */
 35      IF pv_lookup_key = i.common_lookup_id THEN
 36        lv_html := lv_html||'<option class="||pv_class_name||"'
 37        ||' value="'||i.common_lookup_type||'" selected>'
 38        || i.common_lookup_meaning||'</option>';
 39      ELSE
 40        lv_html := lv_html||'<option class="||pv_class_name||"'
 41        ||' value="'||i.common_lookup_type||'">'
 42        || i.common_lookup_meaning||'</option>';
 43      END IF;
 44    END LOOP;

 45    /* Return HTML snippet. */
 46    RETURN lv_html;
 47  END get_option_list;
 48  /

The cursor on lines 11 through 20 uses the super key of the table and column name to return the unique set from the common_lookup table. The IF-block on lines 25 through 31 checks whether you sent a lookup key parameter. It sets the unselected value when as the selected value for display when you haven’t sent the lookup key. It alternatively unsets the unselected value when another value should be the selected value. The selected value in a SELECT tag is the one you see displayed without clicking it for options.

You should note that lines 27 and 30 include ampersands (&). You need to set DEFINE to OFF before you try to compile the get_option_list function because that avoids threating these specialized HTML macros as substitution variables.

The get_option_list function displays the following HTML snippet, which is artificially formatted across rows:

<option class="" value="">Select Type &nbsp;&nbsp;&nbsp;&nbsp;</option>
<option class="" value="AMEX_CARD">American Express Card</option>
<option class="" value="DISCOVER_CARD">Discover Card</option>
<option class="" value="MASTER_CARD">Master Card</option>
<option class="" value="VISA_CARD" selected>VISA Card</option>

The VISA Card entry is highlighted, which means it will show as the selected value in a web form. You’ll see that example later in this article.

Shifting gears, the next example replaces the row-by-row logic and uses a BULK COLLECT to populate the OPTION tags. It requires the setup of an SQL-level object type and a collection of the SQL-level object type.

You create the SQL-level object type like this:

SQL> CREATE OR REPLACE
  2    TYPE lookup_object IS OBJECT
  3    ( lookup_id      NUMBER
  4    , lookup_type   VARCHAR2(30)
  5    , lookup_value  VARCHAR2(30));
  6  /

Then, you create a table collection of the base object, like

SQL> CREATE OR REPLACE
  2    TYPE lookup_table IS TABLE OF lookup_object;
  3  /

The two SQL-level object types are needed to perform a BULK COLLECT. The BULK COLLECT reduces the IO reading the data by putting it in a table collection. You read the table collection in memory, which is slightly faster than reading it row-by-row. It’s only a slight difference because these are very small sets.

Here one of the refactored elements of the get_option_list function:

 10    /* Declare and construct an empty collection. */
 11    lv_lookup_table  COMMON_LOOKUP_TABLE := common_lookup_table();
 12 

 13    /* Declare a dynamic cursor. */
 14    CURSOR cv_list
 15    ( cv_table_name   VARCHAR2
 16    , cv_column_name  VARCHAR2 ) IS
 17     SELECT   common_lookup_object( cl.common_lookup_id
 18                                   , cl.common_lookup_type
 19                                   , cl.common_lookup_meaning)
 20      FROM     common_lookup cl

 21      WHERE    cl.common_lookup_table = cv_table_name
 22      AND      cl.common_lookup_column = cv_column_name
 23      ORDER BY cl.common_lookup_meaning;
 24 
 25  BEGIN
 26    /* Bulk open and assign values. */
 27    OPEN  cv_list(pv_table_name, pv_column_name);
 28    FETCH cv_list BULK COLLECT INTO lv_lookup_table;
 29    CLOSE cv_list;

Line 11 shows the creation of a local instance of the SQL-level table collection. You need it to assign a set of rows in a BULK COLLECT operation. Lines 17 through 19 show how you use the columns returned by the query to construct SQL-level object type instances. Lines 27 through 29 show the BULK COLLECT operation with a parameter-driven cursor.

The next change involves how you read and translate the data into a collection. The excerpt from the function that performs that task is:

 40    FOR i IN 1..lv_lookup_table.COUNT LOOP
 41      /* Set subsequent value when foreign key is set. */
 42      IF pv_lookup_key = lv_lookup_table(i).common_lookup_id THEN
 43        lv_html := lv_html||'<option class="||pv_class_name||"'
 44        ||' value="'||lv_lookup_table(i).common_lookup_type
 45        ||'" selected>'||lv_lookup_table(i).common_lookup_meaning||'</option>';
 46      ELSE
 47        lv_html := lv_html||'<option class="||pv_class_name||"'
 48        ||' value="'||lv_lookup_table(i).common_lookup_type
 49        ||'">'||lv_lookup_table(i).common_lookup_meaning||'</option>';
 50      END IF;
 51    END LOOP;

Line 40 shows you how to loop through a collection. Lines 42, 44, 45, 48, and 49 show you how to reference elements of the SQL-level collection. This function returns the same data set as before.

This section has shown you how to translate relational data into an HTML OPTION list. The next section will show you how it makes leveraging foreign keys against a common_lookup table easy and effective. 

How to use an HTML fragment in a web form

This part of the article shows you how to use the OPTION list function in a web form. It’s fair to say that both PL/SQL and HTML are verbose development languages. Many in the development community acknowledge PL/SQL as a proprietary programming language but they see HTML as a markup system to display text.

You create an event driven programming language when you combine HTML and JavaScript. This web page only displays the form.

The following is the code to display the form:

SQL> CREATE OR REPLACE
  2    PROCEDURE basic_form
  3    ( first_name         VARCHAR2 := NULL
  4    , middle_name        VARCHAR2 := NULL
  5    , last_name          VARCHAR2 := NULL
  6    , account_number     VARCHAR2 := NULL
  7    , credit_card_number VARCHAR2 := NULL ) IS
  8
  9    /* Declare local variables. */
 10    lv_member_id       NUMBER;
 11    lv_member_table    VARCHAR2(30) := 'MEMBER';
 12    lv_member_column1  VARCHAR2(30) := 'MEMBER_TYPE';
 13    lv_member_column2  VARCHAR2(30) := 'CREDIT_CARD_TYPE';
 14    lv_member_type     NUMBER;
 15    lv_contact_id      NUMBER;
 16    lv_contact_table   VARCHAR2(30) := 'CONTACT';
 17    lv_contact_column  VARCHAR2(30) := 'CONTACT_TYPE';
 18    lv_contact_type    NUMBER;
 19
 20    /* Declare a local Cascading Style Sheet. */
 21    lv_css  VARCHAR2(1400);
 22    lv_out  VARCHAR2(4000);
 23
 24    /* Declare a cursor. */
 25    CURSOR get_member
 26    ( cv_first_name      VARCHAR2
 27    , cv_middle_name     VARCHAR2
 28    , cv_last_name       VARCHAR2
 29    , cv_account_number  VARCHAR2
 30    , cv_credit_card_number      VARCHAR2 ) IS
 31      SELECT   m.account_number
 32      ,        m.member_type
 33      ,        m.credit_card_number
 34      ,        m.credit_card_type
 35      ,        c.first_name
 36      ,        c.middle_name
 37      ,        c.last_name
 38      ,        c.contact_type
 39      FROM     member m INNER JOIN contact c
 40      ON       m.member_id = c.member_id
 41      WHERE   (NVL(c.first_name,'x') = NVL(cv_first_name,'x')
 42      AND      NVL(c.last_name,'x') = NVL(cv_last_name,'x'))
 43      OR      (NVL(c.first_name,'x') = NVL(cv_first_name,'x')
 44      AND      NVL(c.middle_name,'x') = NVL(cv_middle_name,'x')
 45      AND      NVL(c.last_name,'x') = NVL(cv_last_name,'x'))
 46      OR       m.account_number = cv_account_number
 47      OR       m.credit_card_number = cv_credit_card_number;
 48 

 49  BEGIN
 50    /* Assign the css to a local variable. */
 51    FOR i IN (SELECT css_text
 52              FROM   css
 53              WHERE  css_name = 'form-standard') LOOP
 54      lv_css := i.css_text;
 55    END LOOP;
 56

 57    /* Print header for form */
 58    htp.print('<!DOCTYPE html>');
 59    htp.print(lv_css);
 60    htp.htmlopen;
 61    htp.headopen;
 62    htp.headclose;
 63    htp.bodyopen;
 64 
 65    /* Get dynamic data. */
 66    FOR i IN get_member( first_name
 67                       , middle_name
 68                       , last_name
 69                       , account_number
 70                       , credit_card_number) LOOP
 71 

 72      /* Collect HTML snippet for basic form. */
 73      lv_out :=         '<form method="post" name="myForm" action="submitItemType.php">';
 74      lv_out := lv_out || '<label class="title1">Membership Account Information</label><br
/>';
 75      lv_out := lv_out || '<div class="box">';
 76      lv_out := lv_out || '<div class="topBox">';
 77      lv_out := lv_out || '<label class="formDownLabel">Account #</label>';
 78      lv_out := lv_out || '<input class="formInput" type="text"
value="'||i.account_number||'" />';
 79      lv_out := lv_out || '<select name="member_type" size="1" onChange="change(this.form.member_type)">';
 80      lv_out := lv_out ||  get_option_list(lv_member_table, lv_member_column1, i.member_type  );
 81     lv_out := lv_out || '</select><br />';
 82     lv_out := lv_out || '<label class="formDownLabel">Credit Card #</label>';
 83     lv_out := lv_out || '<input class="formInput" type="text" value="'||i.credit_card_number||'" />';
 84     lv_out := lv_out || '<select name="credit_card_type" size="1" onChange="change(this.form.credit_card_type)">';
 85     lv_out := lv_out ||  get_option_list(lv_member_table, lv_member_column2, i.credit_card_type);
 86     lv_out := lv_out || '</select><br />';
 87     lv_out := lv_out || '</div>';
 88     lv_out := lv_out || '<div class="middleBox">';
 89     lv_out := lv_out || '<label class="formDownLabel">&nbsp;</label>';
 90     lv_out := lv_out || '<label class="formAcrossLabel">First Name</label>';
 91     lv_out := lv_out || '<label class="formAcrossLabel">Middle Name</label>';
 92     lv_out := lv_out || '<label class="formAcrossLabel">Last Name</label><br class="clear" />';
 93     lv_out := lv_out || '<label class="formDownLabel">Customer</label>';
 94     lv_out := lv_out || '<input class="formShortInput" type="text" value="'||i.first_name||'" />';
 95     lv_out := lv_out || '<input class="formShortInput" type="text" value="'||i.middle_name||'" />';
 96     lv_out := lv_out || '<input class="formShortInput" type="text" value="'||i.last_name||'" /><br />';
 97     lv_out := lv_out || '<label class="formDownLabel">Type</label>';
 98     lv_out := lv_out || '<select style="margin:5px" name="contact_type" size="1" onChange="change(this.form.contact_type)">';
 99     lv_out := lv_out || get_option_list(lv_contact_table, lv_contact_column, i.contact_type);
100     lv_out := lv_out || '</select><br />';
101     lv_out := lv_out || '</div>';
102     lv_out := lv_out || '<div class="bottomBox">';
103     lv_out := lv_out || '<input class="button" name="submit" type="submit" value="Submit">';
104     lv_out := lv_out || '<div class="clear" />';
105     lv_out := lv_out || '</div>';
106     lv_out := lv_out || '</form>';
107   END LOOP;
108
109   /* Print footer for form. */
110   htp.print(lv_out);
111   htp.bodyclose;

112   htp.htmlclose;
113 
114  END basic_form;
115  /

Lines 41 through 47 uses a WHERE clause to enable the entry of first and last names, first, middle, and last names, account number, or credit card type to retrieve a valid row. A blank entry screen results when there are no matching criteria.

Line 51 through 55 reads a CSS sheet into memory and assigns it to a local variable. Lines 66 through 70 send parameters into the cursor. Lines 80, 85, and 99 calls the get_option_list function and returns a specific OPTION list for any given foreign key value.

Lines 73 through 106 assign raw HTML to a local variable. Line 110 effectively echoes the content to the web page.

The basic_form web page displays the following dialog:

You should note that the lookup values highlight the values associated with the foreign key values in the member and contact tables. If you click on any of the dropdown fields, the lookup shows the list of possible choices.

The get_option_list function is a reusable library that lets you display any lookup in your web form. In the example above, the get_option_list returns three lookup sets.

This article has shown you how to use lookup tables to create OPTION lists and how to use this type of function in a web form.