Home » SQL & PL/SQL » SQL & PL/SQL » Retain Multiple Select Values When User Returns to Form
Retain Multiple Select Values When User Returns to Form [message #135452] Wed, 31 August 2005 11:56 Go to next message
plumeriawd
Messages: 1
Registered: August 2005
Location: Ewa Beach, HI
Junior Member

I have a pl/sql form that has several multiple select form fields, that are populated from a cursor statement.

What I need is to ensure that the selected values stay selected if the user fails server-side validation.

I have been looking at collections in 9i and have been trying to figure out the best solution for this.

The values in the select boxes are of type table index by binary_integer.

Would it be better to put them in a varray?

I've been looping through my cursor and then have a separate loop inside that loop which loops through the variables selected using p_services.count (p_services is my var name) and then displays to screen. While it loops and selects the correct variables, it's going through too many times so my box is showing up like this:

Apples - Selected
Apples
Bananas - Selected
Bananas
Oranges
Oranges

How do you populate a varray from a select statement?

Thanks!
Re: Retain Multiple Select Values When User Returns to Form [message #135676 is a reply to message #135452] Thu, 01 September 2005 18:54 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I can't test this code right now, but I think it works. To highlight the selected rows when you re-generate the page just requires you to specify "selected" in the html attributes.
Call demo_pkg.my_form

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;
/

[Updated on: Wed, 14 April 2010 15:12]

Report message to a moderator

Re: Retain Multiple Select Values When User Returns to Form [message #196249 is a reply to message #135676] Wed, 04 October 2006 11:47 Go to previous message
anngold
Messages: 9
Registered: July 2006
Junior Member
How do you define pltab_typ and emptypltab? Thanks.
Previous Topic: Returning Recordset from PL/SQL
Next Topic: From my own session how to find outthe last sql that I issued? (merged 2 threads)
Goto Forum:
  


Current Time: Tue Dec 06 13:52:24 CST 2016

Total time taken to generate the page: 0.06625 seconds