This notes provides a sample package to create a simple HTML query form using
the PL/SQL Toolkit. The form allows you to select multiple departments from
a select list and to display the employees in those departments.

This package also gives usage examples of the following toolkit procedure:

  OWA_UTIL.LISTPRINT       generate HTML selection list (in a HTML form)
                           directly from SQL query
  OWA_UTIL.CELLSPRINT      generate an HTML table from the output of a SQL
                           query. SQL atomic data items are mapped to HTML 
                           cells and SQL rows to HTML rows.
  OWA_UTIL.BIND_VARIABLES  prepare a SQL query by binding variables to it,


The sample script assumes that the PL/SQL toolkit is installed and that
you have a demo schema and cartridge configured as described in Note:73311.1

-- start of sample --
create or replace package emp_data as

  empty    owa_util.ident_arr;
  procedure deptqry( p_my_list in owa_util.ident_arr default empty);
end;
/

create or replace package body emp_data as
  procedure deptqry( p_my_list in owa_util.ident_arr default empty ) is
    i   integer := 0;
  begin
    htp.htmlOpen;
    htp.headOpen;
    htp.title('Dept query');
    htp.headClose;
    htp.bodyOpen;

    htp.formOpen( 'emp_data.deptqry' );
    htp.p( 'Select one or more Departments to display employees for :' );
    htp.para;
    owa_util.listprint( 'select dname, dname,null from dept', 
          'p_my_list', 3, TRUE );
    htp.para;
    htp.formsubmit;
    htp.formclose;
    loop begin
      i := i + 1;
      htp.p( 'Employees in Dept = ' || p_my_list(i) );
      htp.para;
      htp.print('<TABLE>');
      owa_util.cellsprint(owa_util.bind_variables(
        'select ename, empno, job from emp e, dept d
        where d.deptno=e.deptno and dname = :dname', ':dname', p_my_list(i)));
      htp.print('</TABLE>');
      htp.hr;
    exception
      when no_data_found then exit;
    end;
    end loop;
    htp.bodyClose;
    htp.htmlClose;
  end;
end;
/
-- end of sample --

Calling the Procedure
 
1. From your browser go to the following URL:
 
       http://hostname:port/tkdemo/plsql/emp_data.deptqry
 
   This will execute the procedure and display it in the browser window.