Re: PL/SQL/HTML how to question

From: Jeremy <jeremy0505_at_gmail.com>
Date: Thu, 15 Dec 2005 10:23:09 -0000
Message-ID: <MPG.1e0b54586dcdc77798a075@news.individual.net>

In article <1134582581.310286.196530_at_g14g2000cwa.googlegroups.com>, stephen O'D says...
> dennishancy_at_eaton.com wrote:
> > I have a PL/SQL package that displays an HTML form like the following:
> >
> >
> > name | approved?
> > ------------------
> > Allen | x
> > -----------------
> > Betty | x
> > -----------------
> > Chuck |
> > ------------------
> >
> > where the "x" represent a checkbox.
> >
> > When the user clicks OK, I need to update the approved field in my
> > table.
> >
> >
> > So many questions about how to make this work. Hoping it's simpler
> > than I am thinking it is.
> >
> >
> > If I try to refer to the form values in the table, it only gives me
> > those that have been checked. I was hoping to loop through all entries
> > on the table so I can update the unapproved records too.
> >
> >
> > Any advice on how I can accomplish this?
> >
> > Thanks.
> >
> >
> > Dennis
> > Eaton Corporation
> > Cleveland, OH
>
> Assuming you are not using HTMLDB, the reason you are only getting the
> ones you checked returned is because the html/cgi specification states
> that non-checked boxes are not returned to the server at all - it is
> frustrating at times!!
>
> To get this to work, you will probably need to add another hidden field
> in the html that contains the same value as the checkbox. Then you can
> loop over each of the records to approve that come from the checkboxes,
> and then for each of the hidden fields (which could be approved or
> non-approved) do something like
>
> for i in 1 .. non-approved.count loop
> approved := false;
> for j in 1 .. approved.count loop
> if non-approved(i) := approved(j) then
> approved := true;
> exit;
> end if;
> end loop;
> if not approved then
> update();
> end if;
> end loop;
>
> This is all very well if there are only 10 or 20 records - if there are
> 100's or 1000's it will not perform well at all.
>
> The check box is useful when you only want to consider records that are
> ticked, and take no action on any other, to access the non-ticked ones
> too a different method could be better.
>
>

The approach I use is very simple: you simple set the "checked" value of each occurrence of the checkbox to the id of the (in this case I guess) person.

e.g. in your code for displaying the HTML table:

for person in (select name,person_id,approved from people) loop
  htp.tablerowopen;
  htp.tabledata(person.name);
  if person.approved = 'Y' then
    htp.tabledata(htf.formcheckbox('p_approved',

                                    cvalue=>person.person_id,
                                    cchecked=>'CHECKED');

  else
    htp.tabledata(htf.formcheckbox('p_approved',

                                    cvalue=>person.person_id);

  end if;
  htp.tablerowclose;
end loop;

Then all checked values that are sent into the next procedure can be picked up like this..(assuming p_approved is defined as some kind of array)...

for i in 1..p_approved.count
loop
  update people
  set approved = 'Y'
  where person_id = p_approved(i);
end loop;

Hopefully you get the idea.

-- 

jeremy

Received on Thu Dec 15 2005 - 04:23:09 CST