CREATE OR REPLACE PACKAGE demo_pkg
AS
   -- Array - not pl/sql table
   TYPE array_typ IS TABLE OF VARCHAR2 (4000);

   -- pl/sql table - not array
   TYPE pltab_typ IS TABLE OF VARCHAR2 (4000)
      INDEX BY BINARY_INTEGER;

   emptypltab   pltab_typ;

 ----------------------------------------------------------
-- spreadsheet type update (multiple row updates)
 ----------------------------------------------------------
-- the form
   PROCEDURE show_data (p_rows IN NUMBER DEFAULT 3);

--    -- the submit for the from
--    -- Seems WEB forms don't like vArrays - only PL/SQL tables
--    PROCEDURE do_something (
--       p_old_deptno   IN   array_typ,
--       p_new_deptno   IN   array_typ,
--       p_old_dname    IN   array_typ,
--       p_new_dname    IN   array_typ,
--       p_rows         IN   NUMBER DEFAULT 2
--    );

   -- the submit for the form
   PROCEDURE do_something (
      p_old_deptno   IN   pltab_typ,
      p_new_deptno   IN   pltab_typ,
      p_old_dname    IN   pltab_typ,
      p_new_dname    IN   pltab_typ,
      p_rows         IN   NUMBER DEFAULT 2
   );

 ----------------------------------------------------------
-- show multiple from features(checkbox, radio button, multi select)
 ----------------------------------------------------------
   PROCEDURE my_form;

   -- the submit for the form
   PROCEDURE my_form_submit (
      p_user   IN   VARCHAR2 DEFAULT NULL,
      p_pass   IN   VARCHAR2 DEFAULT NULL,
      p_ck1    IN   VARCHAR2 DEFAULT NULL,
      p_ck2    IN   VARCHAR2 DEFAULT NULL,
      p_ck3    IN   VARCHAR2 DEFAULT NULL,
      p_ck4    IN   VARCHAR2 DEFAULT NULL,
      p_rd1    IN   pltab_typ DEFAULT emptypltab,
      p_rd2    IN   pltab_typ DEFAULT emptypltab,
      p_text   IN   VARCHAR2 DEFAULT NULL,
      loc      IN   pltab_typ DEFAULT emptypltab,
      hid1     IN   VARCHAR2
   );
END;
/


CREATE OR REPLACE PACKAGE BODY demo_pkg
AS
/*
|| AHM 06/20/03 a few web from features from other exaples.
*/
 ----------------------------------------------------------
 -- spreadsheet type update (multiple row updates)
 ----------------------------------------------------------
 -- the form
   PROCEDURE show_data (p_rows IN NUMBER DEFAULT 2)
   IS
   BEGIN
      HTP.formOpen ('demo_pkg.do_something');
         HTP.formhidden ('p_rows', p_rows);
         HTP.tableOpen;
      
            FOR x IN (SELECT HTF.escape_sc (deptno) deptno,
                             HTF.escape_sc (dname) dname
                        FROM dept
                       WHERE ROWNUM <= p_rows)
            LOOP
               HTP.p ('<tr><td>');
               HTP.formhidden ('p_old_deptno', x.deptno);
               HTP.formtext ('p_new_deptno', cvalue => x.deptno);
               HTP.p ('</td><td>');
               HTP.formhidden ('p_old_dname', x.dname);
               HTP.formtext ('p_new_dname', cvalue => x.dname);
               HTP.p ('</td></tr>');
            END LOOP;
   
         HTP.tableClose;
         HTP.formsubmit;
      HTP.formClose;
   END;

   -- multi row update
   PROCEDURE do_something (
      p_old_deptno   IN   pltab_typ,
      p_new_deptno   IN   pltab_typ,
      p_old_dname    IN   pltab_typ,
      p_new_dname    IN   pltab_typ,
      p_rows         IN   NUMBER DEFAULT 2
   )
   IS
   BEGIN
      FOR i IN 1 .. p_old_deptno.COUNT
      LOOP
         UPDATE dept
            SET deptno = p_new_deptno (i),
                dname = p_new_dname (i)
          WHERE deptno = p_old_deptno (i) AND dname = p_old_dname (i);

         HTP.bold ('update to dept ' || p_old_deptno (i));

         IF (SQL%ROWCOUNT = 0)
         THEN
            HTP.bold (' failed, someone else changed it');
         ELSE
            HTP.bold (' succeeded...');
         END IF;

         HTP.br;
      END LOOP;

      show_data (p_rows);
   END;

 ----------------------------------------------------------
 -- show multiple form features(checkbox, radio button, multi select)
 ----------------------------------------------------------
   PROCEDURE my_form
   IS
   BEGIN
      HTP.htmlOpen;
         HTP.headOpen;
            HTP.title ('Form Demo Page');
         HTP.headClose;
         HTP.bodyOpen;
            HTP.header (1, 'Demo Form Page', 'center');
            HTP.br;
            HTP.formOpen ('demo_pkg.my_form_submit', 'post');
               HTP.p ('Username:');
               HTP.formtext ('p_user', '8', NULL, 'User');
               HTP.br;
               HTP.p ('Password:');
               HTP.formpassword ('p_pass', '8', '8');
               HTP.hr;
               HTP.p (' Multiple checkboxes can be done independently');
               HTP.br;
               HTP.formcheckbox ('p_ck1', 'Fruit', 1);
               HTP.p ('Fruit');
               HTP.br;
               HTP.formcheckbox ('p_ck2', 'Vegetable');
               HTP.p ('Vegetable');
               HTP.br;
               HTP.formcheckbox ('p_ck3', 'Meat');
               HTP.p ('Meat');
               HTP.br;
               HTP.formcheckbox ('p_ck4', 'Grain');
               HTP.p ('Grain');
               HTP.br;
               HTP.hr;
               HTP.p ('Radio buttons are connected by name, not order');
               HTP.br;
               HTP.formradio ('p_rd1', '1', 'CHECKED');
               HTP.p ('red');
               HTP.br;
               HTP.formradio ('p_rd2', '1');
               HTP.fontOpen ('RED');
                  HTP.p ('Large');
               HTP.fontClose;
               HTP.br;
               HTP.formradio ('p_rd1', '2');
               HTP.p ('BLUE');
               HTP.br;
               HTP.formradio ('p_rd2', '2');
               HTP.fontOpen ('red');
                  HTP.p ('Medium');
               HTP.fontClose;
               HTP.br;
               HTP.formradio ('p_rd1', '3');
               HTP.p ('Green');
               HTP.br;
               HTP.formradio ('p_rd2', '3');
               HTP.fontOpen ('red');
                  HTP.p ('Small');
               HTP.fontClose;
               HTP.br;
               HTP.hr;
               HTP.p ('Text Area Field');
               HTP.br;
               HTP.formtextareaopen2 ('p_text', '4', '20', NULL, 'virtual');
               HTP.p
                  ('Even if you don''t include default text you have to have a closing tag'
                  );
            HTP.formTextareaClose;
            HTP.br;
            HTP.hr;
            HTP.p
               ('Select lists can either scroll through the choices or have a drop down:'
               );
            HTP.br;
            HTP.br;
            HTP.formSelectOpen ('loc', 'Select 1', '4', cattributes => 'MULTIPLE');
               HTP.formselectoption ('San Francisco',
                                     cattributes      => 'VALUE="1-San Francisco"'
                                    );
               HTP.formselectoption ('San Jose', 1,
                                     cattributes      => 'VALUE="2-San Jose"');
               HTP.formselectoption ('San Mateo', cattributes => 'VALUE="3-San Mateo"');
               HTP.formselectoption ('Santa Cruz',
                                     cattributes      => 'VALUE="4-Santa Cruz"'
                                    );
               HTP.formselectoption ('Santa Clara',
                                     cattributes      => 'VALUE="5-Santa Clara"'
                                    );
               HTP.formselectoption ('Santa Maria',
                                     cattributes      => 'VALUE="6-Santa Maria"'
                                    );
            HTP.formSelectClose;
            HTP.hr;
            HTP.formSelectOpen ('loc', 'Select2', 1);
               HTP.formselectoption ('Downtown', cattributes => 'VALUE="1-->Downtown"');
               HTP.formselectoption ('Mid-City',
                                     1,
                                     cattributes      => 'VALUE="2-->Mid-City"'
                                    );
               HTP.formselectoption ('Coast', cattributes => 'VALUE="3-->Coast"');
               HTP.formselectoption ('Burbs', cattributes => 'VALUE="4-->Burbs"');
            HTP.formSelectClose;
            HTP.hr;
            HTP.formhidden ('hid1', 'Hidden but not unviewable');
            HTP.formsubmit (NULL, 'Send it in!');
            HTP.br;
            HTP.formreset ('TRY AGAIN!');
         HTP.formClose;
      HTP.bodyClose;
      HTP.htmlClose;
   END;

   PROCEDURE my_form_submit (
      p_user   IN   VARCHAR2 DEFAULT NULL,
      p_pass   IN   VARCHAR2 DEFAULT NULL,
      p_ck1    IN   VARCHAR2 DEFAULT NULL,
      p_ck2    IN   VARCHAR2 DEFAULT NULL,
      p_ck3    IN   VARCHAR2 DEFAULT NULL,
      p_ck4    IN   VARCHAR2 DEFAULT NULL,
      p_rd1    IN   pltab_typ DEFAULT emptypltab,
      p_rd2    IN   pltab_typ DEFAULT emptypltab,
      p_text   IN   VARCHAR2 DEFAULT NULL,
      loc      IN   pltab_typ DEFAULT emptypltab,
      hid1     IN   VARCHAR2
   )
   IS
   BEGIN
      HTP.p ('<pre>');
      HTP.p ('p_user=' || p_user);
      HTP.p ('p_pass=' || p_pass);
      HTP.p ('p_ck1=' || p_ck1);
      HTP.p ('p_ck2=' || p_ck2);
      HTP.p ('p_ck3=' || p_ck3);
      HTP.p ('p_ck4=' || p_ck4);

      FOR i IN 1 .. p_rd1.COUNT
      LOOP
         HTP.p ('p_rd1=' || p_rd1 (i));
      END LOOP;

      FOR i IN 1 .. p_rd2.COUNT
      LOOP
         HTP.p ('p_rd2=' || p_rd2 (i));
      END LOOP;

      HTP.p ('p_text=' || p_text);

      FOR i IN 1 .. loc.COUNT
      LOOP
         HTP.PRINT (loc (i));
      END LOOP;

      HTP.p ('hid1=' || hid1);
      HTP.p ('</pre>');
   END;
END;
/