Re: PL/SQL - Processing arrays where an element may be null

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/cd/B19306_01/appdev.102/b14261/collections.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. Received on Fri Sep 29 2006 - 09:23:35 CDT