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

From: Jeremy <jeremy0505_at_gmail.com>
Date: Fri, 29 Sep 2006 09:18:00 +0100
Message-ID: <MPG.1f86e7084bc352ae98a2e7@news.individual.net>

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 


 ============================================================

Received on Fri Sep 29 2006 - 03:18:00 CDT