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

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 29 Sep 2006 10:58:05 -0700
Message-ID: <1159552685.007203.272630@i42g2000cwa.googlegroups.com>

ThomasO_at_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/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.

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 Received on Fri Sep 29 2006 - 12:58:05 CDT