Reference Code

Mod PL/SQL

Performance

Expensive SQL

Render and Process an HTML Form with Multi-Select Option Lists

Peter Goldthorp August 2002

Some applications require multiple selections as an input. The application allows one or more items to be selected.

The values in the form are populated by a sql cursor (cur_object) and presented in an html form option list item htp.p('<td><SELECT NAME="c_name" SIZE="20" multiple>'); The code for this appears below:

procedure show_capture_objects(c_name   in dba_objects.object_name%type := '%'
                            , c_owner  in dba_objects.owner%type := '%'
                            , c_type   in dba_objects.object_type%type := '%'
                            , c_status in dba_objects.status%type := '%') is

  cursor cur_object(c_name  in dba_objects.object_name%type
                 , c_owner in dba_objects.owner%type
                 , c_type  in dba_objects.object_type%type
                 , c_status in dba_objects.status%type := '%') is
  select owner, object_name
  from dba_objects
  where object_name like c_name ESCAPE '\'
  and   object_type like c_type
  and   owner       like c_owner
  and   status      like c_status
  order by owner, object_name;

  v_name                    varchar2(80);
  v_owner                   dba_objects.owner%type;
  v_type                    dba_objects.object_type%type;

begin

    htp.p('<body bgcolor="#ffffff" onload="javascript:window.focus();">
           <a name="top-of-page"></a>
           <p><img src="/images/dba_header.gif" alt="DBA Data">
           <hr size="2" width="100%" noshade align="left"></p>

           <h1>Design Capture</h1>');

    htp.p('<p>Select the objects you want to capture then press the ''XML Capture'' button</p>');


    htp.p('<FORM ACTION="xmlutil.xml_gen" METHOD="POST">');
    htp.p('<input type="hidden" name="c_type" value="'||c_type||'">');
    htp.p('<input type="hidden" name="c_owner" value="'||c_owner||'">');

    htp.p('<TABLE cellpadding="0" cellspacing="0" >');
    htp.p('<tr><td><b>Schema:</b></td><td>'||c_owner||'</td></tr>');  
    htp.p('<tr><td><b>Object Type:</b></td><td>'||c_type||'</td></tr>');  

    htp.p('<tr><td><b>Object Name:</b></td> ');  
    htp.p('<td><SELECT NAME="c_name" SIZE="20" multiple>');  

    for a_rec in cur_object(nvl(v_name, '%')
                          , nvl(replace(c_owner, '*', '%'), '%')
                          , c_type
                          , nvl(replace(c_status, '*', '%'),'%')) loop
      htp.p('    <OPTION selected>'||a_rec.object_name);
    end loop;
    htp.p('</select></td></tr>');
    htp.p('<tr><td><br></td><td><input type="submit" value="XML Capture"></td></tr>');
    htp.p('</table>');

    htp.p('</form>');

  end show_capture_objects;

The html form calls a procedure in the xmlutil package called xml_gen htp.p('<FORM ACTION="xmlutil.xml_gen" METHOD="POST">');. xmlutil has a package package type definition called f_table_type:

type f_table_type       is table of varchar2(512) index by binary_integer;

xml_gen accepts the values as a table of varchar2 and then processes them in a loop:

procedure xml_gen(c_name   in f_table_type
                  , c_owner  in dba_objects.owner%type := '%'
                  , c_type   in dba_objects.object_type%type := '%') is
  begin
    htp.p('<h1>XML XDB Repository Extract</h1>');
    for i in 1 .. c_name.count loop
      xml_object_gen(c_name=>c_name(i), c_type=>c_type, c_owner=>c_owner);
    end loop;
  end xml_gen;