Environment as in sig.


Can anyone point to the correct way in handling this? This is data being
POSTed from an HTML form (mod_plsql / plsql web toolkit):

The variables
p_grid_row_id
p_c1_val
p_c2_val

are all defined as tables of varchar2


for i in 1..p_grid_row_id.count
loop
update mytable
set c1_val = p_c1_val(i),
c2_val = p_c2_val(i)
where grid_row_id = p_grid_row_id(i);
end loop;

Nice and simple.

Assume the values:

p_grid_row_id(1) '101'
p_grid_row_id(2) '102'
p_grid_row_id(3) '103'

p_c1_val(1) 'p'
p_c1_val(2) null
p_c1_val(3) 'r'

p_c2_val(1) 'x'
p_c2_val(2) 'y'
p_c2_val(3) 'z'


When processing the above loop, an ORA-01403 NO DATA FOUND will be
generated on the 2nd iteration.

I realise that filling a value in to p_c1_val(2) will "fix" the problem,
but this doesn't seem right. Putting a nvl() around the p_c1_val(i)
doesn't make any difference. Can anyone suggest (if you follow what my
problem is!) a better way of achieving what I am trying to do? I don't
want to implement a kludgy workaround if there is a "purer" way of
structuring it.

Thanks


--
jeremy

================================================== ==========
ENVIRONMENT:
Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8
================================================== ==========

Jeremy wrote:
> Environment as in sig.
>
>
> Can anyone point to the correct way in handling this? This is data being
> POSTed from an HTML form (mod_plsql / plsql web toolkit):
>
> The variables
> p_grid_row_id
> p_c1_val
> p_c2_val
>
> are all defined as tables of varchar2
>
>
> for i in 1..p_grid_row_id.count
> loop
> update mytable
> set c1_val = p_c1_val(i),
> c2_val = p_c2_val(i)
> where grid_row_id = p_grid_row_id(i);
> end loop;
>
> Nice and simple.
>
> Assume the values:
>
> p_grid_row_id(1) '101'
> p_grid_row_id(2) '102'
> p_grid_row_id(3) '103'
>
> p_c1_val(1) 'p'
> p_c1_val(2) null
> p_c1_val(3) 'r'
>
> p_c2_val(1) 'x'
> p_c2_val(2) 'y'
> p_c2_val(3) 'z'
>
>
> When processing the above loop, an ORA-01403 NO DATA FOUND will be
> generated on the 2nd iteration.
>
> I realise that filling a value in to p_c1_val(2) will "fix" the problem,
> but this doesn't seem right. Putting a nvl() around the p_c1_val(i)
> doesn't make any difference. Can anyone suggest (if you follow what my
> problem is!) a better way of achieving what I am trying to do? I don't
> want to implement a kludgy workaround if there is a "purer" way of
> structuring it.
>
> Thanks
>
>
> --
> jeremy
>
> ================================================== ==========
> ENVIRONMENT:
> Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8
> ================================================== ==========

Jeremy
Please read more in:
http://download-east.oracle.com/docs...ons.htm#i26701

p_c1_val(2) is not null, element p_c1_val(2) doesn't exist,
never been assigned or was deleted.

You can use function EXISTS to verify this in your program.
....
for i in 1..p_grid_row_id.count
loop
if p_c1_val.exists(i) and p_c2_val.exists(i) then
update mytable
set c1_val = p_c1_val(i),
c2_val = p_c2_val(i)
where grid_row_id = p_grid_row_id(i);
end if;
end loop;
....

HTH
Thomas Olszewicki
CPAS Systems Inc.

ThomasO@cpas.com wrote:
> Jeremy wrote:
> > Environment as in sig.
> >
> >
> > Can anyone point to the correct way in handling this? This is data being
> > POSTed from an HTML form (mod_plsql / plsql web toolkit):
> >
> > The variables
> > p_grid_row_id
> > p_c1_val
> > p_c2_val
> >
> > are all defined as tables of varchar2
> >
> >
> > for i in 1..p_grid_row_id.count
> > loop
> > update mytable
> > set c1_val = p_c1_val(i),
> > c2_val = p_c2_val(i)
> > where grid_row_id = p_grid_row_id(i);
> > end loop;
> >
> > Nice and simple.
> >
> > Assume the values:
> >
> > p_grid_row_id(1) '101'
> > p_grid_row_id(2) '102'
> > p_grid_row_id(3) '103'
> >
> > p_c1_val(1) 'p'
> > p_c1_val(2) null
> > p_c1_val(3) 'r'
> >
> > p_c2_val(1) 'x'
> > p_c2_val(2) 'y'
> > p_c2_val(3) 'z'
> >
> >
> > When processing the above loop, an ORA-01403 NO DATA FOUND will be
> > generated on the 2nd iteration.
> >
> > I realise that filling a value in to p_c1_val(2) will "fix" the problem,
> > but this doesn't seem right. Putting a nvl() around the p_c1_val(i)
> > doesn't make any difference. Can anyone suggest (if you follow what my
> > problem is!) a better way of achieving what I am trying to do? I don't
> > want to implement a kludgy workaround if there is a "purer" way of
> > structuring it.
> >
> > Thanks
> >
> >
> > --
> > jeremy
> >
> > ================================================== ==========
> > ENVIRONMENT:
> > Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8
> > ================================================== ==========
>
> Jeremy
> Please read more in:
> http://download-east.oracle.com/docs...ons.htm#i26701
>
> p_c1_val(2) is not null, element p_c1_val(2) doesn't exist,
> never been assigned or was deleted.
>
> You can use function EXISTS to verify this in your program.
> ...
> for i in 1..p_grid_row_id.count
> loop
> if p_c1_val.exists(i) and p_c2_val.exists(i) then
> update mytable
> set c1_val = p_c1_val(i),
> c2_val = p_c2_val(i)
> where grid_row_id = p_grid_row_id(i);
> end if;
> end loop;
> ...
>
> HTH
> Thomas Olszewicki
> CPAS Systems Inc.

Another way to handle this would be to wrap update into an exception
handler:

for i in 1..p_grid_row_id.count
loop
BEGIN
update mytable
set c1_val = p_c1_val(i),
c2_val = p_c2_val(i)
where grid_row_id = p_grid_row_id(i);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL; -- to ignore the error, substitute with any
suitable action
END;
end loop;

EXISTS() function of a collection gives you more opportunities to
detect and handle missing collection elements issue (for example,
instead of skipping the row with null exception handler/saved
exceptions you might substitute missing value with NULL and still
update the row, if this is desired behaviour.)

However, if you use FORALL with SAVE EXCEPTIONS you can significantly
improve update performance and then handle all errors encountered
during the course of this bulk operation in a consistent way (for
example, apply the same convert to null logic, but only for failed rows
- you can use EXISTS() here to find out which elements are actually
missing.)


Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com